BUG: Current Activity Blank in Enterprise Manager 6.5

• Microsoft SQL Server, version 6.5

SYMPTOMS

After ensuring the tempdb 'Select into/Bulk copy' option is set to TRUE (as noted in article Q149695, "PRB: Current Activity in SEM May Not Be Displayed" in the Microsoft Knowledge Base), the SQL Server Enterprise Manager 6.5 Current Activity will still be blank. If you open an ISQL/W window, connect to the SQL Server's master database, and run the sp_processinfo stored procedure, the following error is returned:

   Msg 2812, Level 16, State 4
   Stored procedure 'sp_processinfo' not found

CAUSE

During the upgrade or installation of Service Pack 1 for SQL Server 6.5, the Procsyst.sql script is run against the master database. This script builds certain temporary tables in the tempdb database. If 'Select into/Bulk copy' is set to FALSE for the tempdb database when this script is run, the following stored procedures may fail to be created within the master database:

   sp_processinfo
   sp_dropsegment
   sp_lockinfo
   sp_helpsort
   sp_who2

WORKAROUND

To work around this problem, do either of the following:

• Enable 'Select into/Bulk copy' through Enterprise Manager.

  -or-

• Run the following statement from a query window:

        sp_dboption tempdb, 'Select into/Bulk copy', TRUE
  

  Then rerun the Procsyst.sql script against the master database.

  NOTE: If you have installed SQL Server Service Pack 1, run the Procsyst.sql script that is shipped with SQL Server 6.5 Service Pack 1.

STATUS

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