@@TRANCOUNT and a current connection

Question

Assume I connect to SQL server 2008 via SQL Server Management Studio ( SSMS ) and open new window W1 by clicking on New Query tab and write the following inside W1:

BEGIN TRANSACTION;

If I execute this statement 5 times, and then write (inside W1)

SELECT @@TRANCOUNT;

, then the value returned will be 5. But if I open another window W2 ( inside the same SSMS instance and thus on the same connection ) and write inside W2

SELECT @@TRANCOUNT; 

then value returned will be 0.

@@TRANCOUNT variable returns the number of active transactions for the current connection.

Both W1 and W2 windows were opened on the same connection, so shouldn’t ( according to the above quote ) in both W1 and W2 variable @@TRANCOUNT hold the same value?

thanx

Answer 1

Every query window in SSMS is a separate connection, running on a separate spid. The number in parenthesis in the query window tab is your connection number for the current window.

With a couple windows open, and open transactions in each if you like, you should be able to see each one of them show up in the results of a call to sp_who2. Using the spid number from the window tab you'll be able to find each row in the results of that proc. You can also find detailed information about your connection in the status bar at the bottom of each query window, and also in the properties window for each open query window.