PRB: System Stored Procedures Return Underlying SQL Code or Unexpected Results (262951)

The information in this article applies to:

Microsoft SQL Server 7.0
Microsoft SQL Server 2000 (all editions)

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

Modification Type:	Major	Last Reviewed:	10/17/2003
Keywords:	kbprb KB262951