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

This article was previously published under Q321824
This article has been archived. It is offered "as is" and will no longer be updated.
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.
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.
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.
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

Article ID: 321824 - Last Review: 01/17/2015 05:32:46 - Revision: 4.2

Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 7.0 Standard Edition

  • kbnosurvey kbarchive kbprb KB321824