FIX: Correlated Subquery Can Fail With Trace Flag 204 Enabled

Last reviewed: April 9, 1997
Article ID: Q158235

The information in this article applies to:

Microsoft SQL Server, version 6.5

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 : kbbug6.50 kbfix6.50.sp2 kbnetwork SSrvTran_SQL
Version : 6.5
Platform : WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 9, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.