From: esquel@sommarskog.se   
      
   Peng Liu (liupengwyy@gmail.com) writes:   
   > Now I did a minor change to my C++ code. Now, on the SQL Server   
   > Management Studio, right click the database node, select   
   > Reports->Standard Reports->All transactions, the report said that there   
   > is no active transaction.   
   >   
   > However, through the step 1 and 2, there is still 1 transaction.   
   >   
   > What do you think about this scenario?   
      
   So your step 1 and 2 was to look in sys.dm_tran_session_transactions and   
   sys.dm_tran_active_transactions, and if I understand you correctly   
   they display a transaction.   
      
   Using Profiler, I found that the report All Transactions in SSMS runs a   
   query of which the pertinent parts are:   
      
      from    sys.dm_tran_database_transactions dt   
      ...   
      inner join sys.dm_tran_active_transactions at   
          on (at.transaction_id = dt.transaction_id)   
      inner join sys.dm_tran_session_transactions st   
         on (st.transaction_id = dt.transaction_id)   
      ...   
      where (dt.database_id = DB_ID()) and (st.is_user_transaction=1)   
      
   That is, if this query lists no transactions, this means that your   
   transaction is not in sys.dm_tran_database_transactions, at least for   
   the database you look in. (And nor in tempdb, as the report also runs   
   the query i tempdb.)   
      
   As I don't know your scenario, I cannot comment further, but I would   
   be intrigued if you can present a repro.   
      
   Maybe I be so curious to ask for what purpose you want to know whether   
   you have a transaction or not?   
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)