BUG: No List of AutoStart Stored Procedures in SP_HELPSTARTUP

• Microsoft SQL Server version 6.0

SYMPTOMS

If the stored procedure SP_HELPSTARTUP is executed from a database other than master, SP_HELPSTARTUP will not show the list of autostart stored procedures.

CAUSE

This is because the stored procedure is selecting a list of autostart procedures by checking the appropriate value of the category field in sysobjects, but the sysobjects table is not qualified by the master database in the procedure text. So the sysobjects table of the current database will be searched, resulting in no output because all the autostart stored procedures are created only in the master database.

The stored procedure should return correct results because by definition a stored procedure whose name is preceded by 'sp_' can be executed in any database. If the stored procedure is not existent in the database, the stored procedure is assumed to be in the master database and the server tries to find the stored procedure in the master database. The issue here is that though the stored procedure exists, the sysobjects table referenced will be that of the database currently in use, because the text of the procedure does not qualify the sysobjects table with the master database.

WORKAROUND

Execute the procedure SP_HELPSTARTUP from the master database. An alternative is to drop and re-create the stored procedure with the following change to its text::

    select 'Startup stored procedures:' = name from master..sysobjects 

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

Additional query words: master autostart sp_helpstartup 6.0 stored procedure database

Keywords          : SSrvDRI kbbug6.00 
Version           : 6.00 6.5
Platform          : WINDOWS 
Issue type        : 

Last Reviewed: March 27, 1999