FIX: Correlated Subquery Can Fail With Trace Flag 204 Enabled

ID: Q158235

The information in this article applies to:
BUG #: 15927 (Windows NT: 6.5)

SYMPTOMS

Microsoft SQL Server version 6.5 does not allow a correlated subquery in the column list of a SELECT statement when trace flag 204 is enabled. When this type of query is executed, the following error is encountered:

Msg 107, Level 15, State 1

The column prefix 'o' does not match with a table name or alias name used in the query.

This type of query is used by SQL Enterprise Manager (actually SQL Distributed Management Objects, or SQL-DMO) to obtain details about databases. Therefore, certain operations in SQL Enterprise Manager, such as viewing a list of databases in the database tree, will not appear to work, and no error message is provided.


WORKAROUND

To work around this problem, disable trace flag 204, and create a stored procedure that contains your query. This stored procedure can be run with trace flag 204 enabled, and it will produce the correct results. For SQL Enterprise Manager, you must disable this trace flag to use all features of the product.


STATUS

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


MORE INFORMATION

The following is an example of a query that fails with trace flag 204 enabled: 


   select LogOnSepDev = (select count(*) from sysusages us where us.dbid =
   o.dbid and us.segmap = 4)
   from sysdatabases o



Please note that that Microsoft SQL Server version 6.5 Service Pack 1 upgrades may also be affected by this problem. If trace flag 204 is enabled before running Setup.exe for the service pack, the upgrade will fail running Proc65_1.sql when trying to create the procedure Sp_helpconstraint. Setup will fail with the following message:
Setup initialization could not be successfully completed. Isql.exe could not be executed. Please check the relevant .out file.

The following error will be recorded in the Proc65_1.out file:
Creating 'sp_helpconstraint'.
Msg 107, Level 15, State 1. Procedure sp_helpconstraint, Line 513

The column prefix 'r' does not match with a table name or alias name used in the query.

If you successfully upgrade SQL Server 6.5 to Service Pack 1 without trace flag 204 enabled, then you will be able to run Sp_helpconstraint with no errors, and you will receive the proper results.

The SQL Server "Administrator's Companion 6.0" contains more details about trace flags. Please note the following statement from the documentation concerning the usage of trace flags:
SQL Server trace flags provide additional information about SQL Server operations or change certain behaviors, usually for backward compatibility. In general, trace flags should be used to temporarily work around a problem until a permanent solution is put in place. Although the information provided by trace flags can help you diagnose problems, keep in mind that trace flags are not part of the supported feature set. This means that future compatibility or continued use is not assured. In addition, your primary support provider, including Microsoft, will usually not have further information and will not answer questions regarding the trace flags or their output. In other words, the information provided in this section is to be used "as is."

Additional query words: SP1 


Keywords          : kbnetwork SSrvTran_SQL kbbug6.50 kbfix6.50.sp2 
Version           : 6.5
Platform          : WINDOWS 
Issue type        :

Last Reviewed: April 3, 1999