INF: New Server Message 8113 Introduced in SQL 4.21a

ID: Q122033

The information in this article applies to:

Microsoft SQL Server version 4.2x

SUMMARY

As stated on page 190 of the "Transact-SQL Reference Guide":


   You cannot use the following statements inside a user-defined
   transaction: CREATE DATABASE, CREATE TABLE, ......

A new server error message, 8113, has been introduced in SQL Server 4.21a to warn the user that an illegal data definition language statement was found within the context of a user-defined transaction while recompiling a stored procedure.

MORE INFORMATION

Recompilation of a stored procedure that contains a data definition language statement, such as CREATE TABLE, when a transaction is open will generate the following error message:

Msg 8113, Level 16, State 1
Unable to recompile '<sp name>', '<DDL statement>' is illegal in an
open transaction.

Since the rules of Transact-SQL do not allow a data definition language statement inside a user-defined transaction (See page 190 of the SQL Server "Transact-SQL Reference Guide"), the transaction must be ended or the procedure modified before the procedure can be successfully recompiled.

One technique is to execute a COMMIT TRANSACTION or ROLLBACK TRANSACTION before executing the stored procedure. Another technique is to end the transaction inside the stored procedure before the data definition language statement is executed.

Keywords : kbother SSrvStProc SSrvWinNT
Version : 4.21a 4.21.006 4.21.007
Platform : WINDOWS
Issue type :