Microsoft KB Archive/152546

{|
 * width="100%"|

BUG: No List of AutoStart Stored Procedures in SP_HELPSTARTUP

 * }

Q152546

-

The information in this article applies to:


 * 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

where category & 16 = 16

STATUS
Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0.

Additional query words: master autostart sp_helpstartup 6.0 stored procedure database

Keywords :

Issue type : kbbug

Technology : kbSQLServSearch kbAudDeveloper kbSQLServ600