SQL Server Agent cannot start after it attaches a SQL Server 7.0 MSDB database to SQL Server 2000 (321824)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
  • Microsoft SQL Server 7.0
This article was previously published under Q321824

SYMPTOMS

The SQL Server Agent service will not start after you attach the msdb database from a server that is running SQL Server 7.0 to a server that is running SQL Server 2000. The following error is recorded in the SQLAgent log:

SQLServerAgent cannot start because the instance of the server () is not the expected instance (instanceName)
For the default instance, the instanceName is MSSQLServer. For the named instance, the instanceName is the actual instance name.

You might also receive the error when you upgrade SQL Server 7.0 to SQL Server 2000, if the program cannot upgrade the msdb database.

NOTE: The SQL Server 7.0 system databases are not compatible with SQL Server 2000. Do not try to back up or restore, or to attach and detach procedures on the SQL Server 7.0 master, model, msdb or distribution databases to SQL Server 2000.

This article is useful, if you have already mistakenly attached a SQL Server 7.0 msdb database to a SQL 2000 server, and you cannot start SQL Server 2000.

CAUSE

When the SQL Server Agent service starts, it calls the sp_sqlagent_get_startup_info stored procedure to get the instance name to connect to the correct instance of SQL Server.

The problem occurs after you attach the msdb database from a SQL Server 7.0 server to the SQL Server 2000 server.

During the attach process, the sp_sqlagent_get_startup_info stored procedure is not upgraded. The SQL Server 7.0 version of sp_sqlagent_get_startup_info cannot pull out the correct instance name; therefore, the failure occurs.

RESOLUTION

To resolve this problem, re-create the msdb database by running the Instmsdb.sql script from the SQL Server (or the latest service pack if the server has a service pack applied) Install folder in SQL Query Analyzer.

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

224071 INF: Moving SQL Server Databases to a New Location with Detach/Attach

Modification Type:MajorLast Reviewed:12/17/2004
Keywords:kbprb KB321824
©2004 Microsoft Corporation. All rights reserved.