Microsoft KB Archive/308745

= FIX: SET NOEXEC OFF May Not Execute If Included in Same Batch as SET NOEXEC ON =

Article ID: 308745

Article Last Modified on 10/30/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q308745



BUG #: 352858 (SHILOH_BUGS)



SYMPTOMS
In some situations, you may notice that some SQL statements do not execute if you have SET NOEXEC ON and SET NOEXEC OFF statements in the same batch of Transact-SQL Statements. SET NOEXEC OFF does not execute in such situations, and therefore any SQL statements that follow SET NOEXEC OFF do not execute.



CAUSE
The problem is that a deferrable compile-time error occurs in the batch that creates tables and executes some complex query with them in the same batch. This causes statements to recompile at run-time for the statements to execute properly. The SET NOEXEC OFF statement recompiles and does not actually execute, which causes SET NOEXEC to be left ON. Therefore, any statements that follow, do not execute.



RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack



WORKAROUND
To work around this problem, execute SET NOEXEC OFF in a separate batch.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.



MORE INFORMATION
To reproduce the problem, execute the following script against any version of SQL Server mentioned in the preceding section: use tempdb go SET NOEXEC ON select name into t1 from sysobjects select name into t2 from sysobjects select name from t1, t2 where t1.name = t2.name SET NOEXEC OFF go select name from sysobjects go The only results that return when you use this code are:

The command(s) completed successfully. Notice that the script did not return the output from the sysobjects system table.

Additional query words: SET Noexec off on

Keywords: kbbug kbfix KB308745

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.