Q141365 - FIX: Example for EXEC to Change DB Context in TSQL Ref Invalid

FIX: Example for EXEC to Change DB Context in TSQL Ref Invalid

Q141365

The information in this article applies to:

Microsoft SQL Server version 6.0

BUG#: 12488 (6.00 DocErr)

SYMPTOMS

The SQL Server 6.0 "Transact-SQL Reference" manual under the section "Using EXECUTE with a Character String" (page177) has an invalid example for using EXEC() to change the database context. This use of EXEC() should not be allowed and is considered a bug for SQL 6.0. Future versions of SQL Server 6.0 will revert the database context to its original state after completion of the EXEC statement. Therefore, any scripts or applications that rely on the current behavior will not work for future versions of SQL Server.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.0. For more information, contact your primary support provider.

MORE INFORMATION

The example as listed in the documentation:


   EXEC('USE pubs')
   EXEC('SELECT * FROM authors')

will fail with the following error:

Msg 208, Level 16, State 1 Invalid object name 'authors'

in future versions of SQL Server (assuming a table called authors does not exist in the current database).

Any change in database context for EXEC is only valid within that EXEC statement. Therefore, the following example will work properly on SQL 6.0 and future versions:


   EXEC ('USE PUBS SELECT * FROM AUTHORS')

Additional query words: sql6 docs

Keywords :
Issue type : kbbug
Technology : kbSQLServSearch kbAudDeveloper kbSQLServ600