Transaction Notification Is Not Displayed When VIEW SERVER STATE Is Missing

Transaction Notification Is Not Displayed When VIEW SERVER STATE Is Missing

The Notification if execution contains open transactions option works as expected on some SQL Server instances, but does not display any notifications on others.

This behavior is typically caused by the absence of the VIEW SERVER STATE privilege for the current user on the affected server.

After a query is executed, SQL Complete determines the number of open transactions by querying the sys.dm_exec_sessions dynamic management view from a separate session.

Without the VIEW SERVER STATE privilege, SQL Server does not allow access to information about other sessions. As a result, the query returns NULL, and the notification is not displayed.

Diagnostics

The issue can be reproduced with the following steps:

Step 1. Open the First Query Document

Open a new query document in SSMS or dbForge Studio for SQL Server and execute:

BEGIN TRAN 

     SELECT 

           @@SPID AS current_session_id, 

                     (SELECT open_transaction_count    

                        FROM sys.dm_exec_sessions    

                        WHERE session_id = @@SPID) AS tr_count_in_session

Step 2. Open a Second Query Document

Open another query document and execute:

SELECT @@SPID AS current_session_id,   

         (SELECT open_transaction_count    

            FROM sys.dm_exec_sessions   

            WHERE session_id = <doc1_session_id>) AS tr_count_in_session

where <doc1_session_id> is the value of current_session_id returned in Step 1.

​

If the VIEW SERVER STATE privilege is missing, the query returns: tr_count_in_session = NULL

This confirms the root cause of the issue.

Resolution

Request the VIEW SERVER STATE privilege from the database administrator for the SQL Server instance where the notification is not displayed.

Product Limitation

The current implementation requires the VIEW SERVER STATE privilege because the number of open transactions is obtained from a separate session.

Using @@TRANCOUNT within the same session is technically possible, but it does not always produce reliable results. For example, the value may be inaccurate during paged data retrieval or other operations that use additional internal queries.