INF: New Server Message 8113 Introduced in SQL 4.21a

Q122033

The information in this article applies to:

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
Issue type :
Technology : kbSQLServSearch kbAudDeveloper kbSQLServ420OS2


Last Reviewed: March 21, 1999
© 2001 Microsoft Corporation. All rights reserved. Terms of Use.