FIX: Message 2 After Issuing ROLLBACK TRANSACTION Command (159744)

SYMPTOMS

After you issue a ROLLBACK TRANSACTION command, you receive the following error:

Msg 2, Level 16, State 16

However, there is no such thing as msg 2. In addition, as with any server message causing a DB-Library call to fail, it is likely that you will receive DB-Library error 10007 "General error, check for other server messages," as a signal to check for server messages.

This problem occurs if you have done the following in sequence:

Created a local temporary table in a stored procedure.
Entered a user-defined transaction.
Cancelled the query.
Issued the ROLLBACK TRANSACTION command.

WORKAROUND

Other than the unexpected DB-Library error 10007 and server message 2 being returned to the client, there are no other adverse effects.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.

MORE INFORMATION

The sequence that causes this problem is normal for many applications. It is common to get blocked on a lock while in a user-defined transaction, then timeout and have the DB-Lib message handler return INT_CANCEL or otherwise issue dbcancel(). If the other criteria are met as well, you will receive message 2.

However, message 2 does not usually cause any problems. Applications should always be prepared to receive any server message. They should not be hard coded to expect specific numbers or types of server messages.