Microsoft KB Archive/262951

= PRB: System Stored Procedures Return Underlying SQL Code or Unexpected Results =

Article ID: 262951

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q262951



SYMPTOMS
System stored procedures return errors similar to the following example or they produce lengthy blocks of code instead of the expected results.

For example, if you run the sp_helpdb stored procedure, the following error occurs:

Server: Msg 208, Level 16, State 1, Procedure sp_helpdb, Line 68 Invalid object name '#spdbdesc'.



CAUSE
The sp_helpdb stored procedure involves operations that cannot be displayed as part of the result of SHOWPLAN_TEXT or SHOWPLAN_ALL statements. Other system stored procedures that contain similar code will also produce errors. If none of the operations used by the system stored procedure are off limits, the underlying code displays.



RESOLUTION
If either the SHOWPLAN_TEXT or SHOWPLAN_ALL statement has been left on, you must turn them off. Run the following lines of code from within the Query Analyzer:

set showplan_text off go set showplan_all off go



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



MORE INFORMATION
This behavior occurs when SHOWPLAN_TEXT or SHOWPLAN_ALL is set to ON. The problem is that some operations executed by stored procedures cannot be displayed as part of a query plan. Other system stored procedures like sp_tables displays many lines of code without producing error messages.

To reproduce this behavior, run the following script from the SQL Server Query Analyzer:

set showplan_text on go exec sp_helpdb

Keywords: kbprb KB262951

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.