From: liupengwyy@gmail.com   
      
   On Saturday, April 27, 2013 3:11:56 PM UTC+8, Peng Liu wrote:   
   > My C++ code accesses SQL Server by ODBC driver 2.x, and calls   
   >    
   > SQLSetConnectAttr( pMSSQLHandles->hDbc, SQL_ATTR_AUTOCOMMIT, S   
   L_AUTOCOMMIT_OFF, 0 );   
   >    
   > to set the  transaction to implicit transaction. I also call SQLEndTran() to   
   commit the transaction.   
   >    
   >    
   >    
   > After my application starts， in SQL Server 2008 Managment Studio, run   
   "DBCC OPENTRAN", no active open transaction is reported. And "select   
   @@TRANCOUNT" return 0.    
   >    
   >    
   >    
   > However, if run   
   >    
   > select * from sys.dm_tran_session_transactions   
   >    
   > we can see there is one record， and we can also find the transaction   
   corresponding to this record in sys.dm_tran_active_transactions.   
   >    
   >    
   >    
   > Below SQL script will return 1 records.   
   >    
   > select s.dbid, s.spid,s.loginame, s.status,d.name,s.last_batch   
   datediff(minute,s.last_batch,GETDATE()) as IdleTimeInMin,    
   >    
   >     s.open_tran,t.text   
   >    
   > from sys.sysprocesses s   
   >    
   > join sys.databases d on d.database_id = s.dbid   
   >    
   > cross apply sys.dm_exec_sql_text (s.sql_handle) t   
   >    
   > where d.name = 'XXX' and s.loginame <> 'sa'   
   >    
   > order by s.last_batch   
   >    
   >    
   >    
   > I am curious that which method is the exact one to get all the not-committed   
   transactions.   
   >    
   > What is the meaning of the records in dm_tran_active_transactions,   
   dm_tran_session_transactions and dm_tran_database_transactions?   
   >    
   > What is difference between Open transaction and active transaction?   
   >    
   >    
   >    
   > Thanks in advance.   
      
   Hi, Erland Sommarskog,   
      
   I understand what you replied. Please look what I try:   
      
   After my application starts, from SQL Server Management Sutdio,   
      
   1. I run "select * from sys.dm_tran_session_transactions", then get one record   
   below which the session id is 53 and the transaction id is 8233.   
      
   session_id	transaction_id	transaction_descriptor	enlist_count	is   
   user_transaction	is_local	is_enlisted	is_bound   
   53	        8233	        0x3C00000035000000	    0	            1	                  
   1	        0	        0   
      
   2. I run "select * from sys.dm_tran_active_transactions where transaction_id =   
   8233;", then get the rusult below (I skip last fields):   
      
   transaction_id	name	            transaction_begin_time	transacti   
   n_type	transaction_uow	transaction_state	transaction_status	transaction_status2   
   8233	        user_transaction	2013-04-28 10:33:15.270	1	                  
   NULL	        2	                0	                258	   
      
   3. I run "DBCC inputbuffer(53)", get the result below:   
   EventType	Parameters	EventInfo   
   Language Event	0	(@P1 varchar(2),@P2 varchar(9),@P3 varchar(13))SELECT * FROM   
   SCMESSAGEM1 READCOMMITTED WHERE "SYSLANGUAGE"=@P1 AND "CLASS"=@P2 AND   
   "MESSAGE_ID"=@P3   
      
   4. I run the script "select spid, lastwaittype, last_batch, status, open_tran,   
   cmd, sql_handle from sys.sysprocesses where spid = 53;", get the rusult below:   
   spid	lastwaittype	last_batch	            status	 open_tran	 cmd	    sql_handle   
   53	    MISCELLANEOUS   2013-04-28 10:33:15.307	sleeping 1	AWAITING   
   COMMAND	0x01000D008F8E1D07C06AB8850000000000000000   
      
   5. I run "SELECT text FROM sys.dm_exec_sql_text(0x01000D008F8E1D   
   7C06AB8850000000000000000)", get below:   
   (@P1 varchar(2),@P2 varchar(9),@P3 varchar(13))SELECT * FROM SCMESSAGEM1   
   READCOMMITTED WHERE "SYSLANGUAGE"=@P1 AND "CLASS"=@P2 AND "MESSAGE_ID"=@P3   
      
   6. Finally, I run "DBCC OPENTRAN;", and get below:   
   No active open transactions.   
   DBCC execution completed. If DBCC printed error messages, contact your system   
   administrator.   
      
   So, according to what I test above, before step 6, it seems that there is one   
   transaction which is not committed, the session 53 has one transaction (the   
   "open_tran" value is 1), the status is "sleeping";  However, step 6 also show   
   that no transaction is    
   not committed. They seems conflict.   
      
   Besides, for the query text from step 5, is it possible for me to know the   
   exact value of the parameters P1, P2 and P3?   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)