Microsoft KB Archive/122033

{|
 * width="100%"|

INF: New Server Message 8113 Introduced in SQL 4.21a

 * }

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 ' ', '' 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