Microsoft KB Archive/321824

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

Article ID: 321824

Article Last Modified on 12/17/2004

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



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.

