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

• Microsoft SQL Server, version 6.0

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

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

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