Problem : Maintenance job fails on named instances of SQL Server 2000 with Error 22029
Problem Description :
A database job created with the Database Maintenance Plan Wizard fails for a named SQL Server 2000 instance. If you view the job history in SQL Server Agent and you enable the Show step details check box, you will see the following error:
sqlmaint.exe failed. [SQLSTATE 42000][Error 22029]. The step failed
Resolution :
This problem only occurs on named instances of SQL Server 2000.
The Database Maintenance Plan Wizard creates a job that includes a step to run the xp_sqlmaint extended stored procedure. The xp_sqlmaint extended stored procedure calls the sqlmaint utility with a string that contains various switches to Sqlmaint.exe. One of those switches is the -S parameter, which is used for the server name. The -S parameter is misinterpreted; therefore, the error occurs. As a consequence, you may experience this problem if you use the xp_sqlmaint extended stored procedure in conjunction with named instances of SQL Server 2000.
This problem can also occur if the path of the location of the SQL binaries contains a “-S” string. For example, if the SQL binaries are located in a folder that is named, C:\Program Files\Microsoft SQL Server\Mssql-S01\Binn, the xp_sqlmaint jobs will fail. This behavior also occurs with named instances. In this scenario, you receive the following error message:
Microsoft SQLMaint Utility (Unicode), Version [Microsoft SQL-DMO (ODBC SQLState: 08001)] Error 0: [Microsoft][ODBC SQL Server Driver][Shared Memory]Invalid connection. [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Invalid Instance()).
The following registry key value is used to obtain information about the location of SQL Server binaries:
HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Name>\SETUP\SQLPATH
Related Posts
- Error while installing the SQL Server 2000 Virtual Instance
- SQL Server Cluster Resource Failed When Bringing Online
- Troubleshooting SSRS Error “The report server is not responding”
- Consistency Errors in MS SQL Server 2000
- Troubleshooting SQL Server 2005 Jobs Failure
Tags: Maintenance Job failure, sql server 2000, SQL Server Error Code 22029
