FIX: SET NOEXEC OFF May Not Execute If Included in Same Batch as SET NOEXEC ON (308745)

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

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.