INF: New Server Message 8113 Introduced in SQL 4.21a

Last reviewed: April 30, 1997
Article ID: Q122033

The information in this article applies to:

  - Microsoft SQL Server version 4.21a

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.

Additional query words:
Keywords : kbother SSrvStProc SSrvWinNT
Version : 4.21a 4.21.006 4.21.007
Platform : WINDOWS
Issue type : kberrmsg

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 30, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.