Maintenance job fails on named instances of SQL Server 2000 with Error 22029

Article translations Article translations
Article ID: 326485 - View products that this article applies to.
This article was previously published under Q326485
Expand all | Collapse all

SYMPTOMS

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.

CAUSE

The string for the target folder (the folder specified to store the database backup, or the one used for the transaction log backup or the one used for reports) in a maintenance plan contains "-S". For example, the directory path "C:\Db-Save" contains an "-S" string.

WORKAROUND

To work around this issue, do not use strings that contain "-S" for a directory path. Also note that the error only occurs when the "S" that follows the dash "-" is an uppercase letter ("S").

STATUS

Microsoft is researching this problem and will post more information in this article when the information becomes available.

MORE INFORMATION

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
SQL Server 2000 Books Online; topics: "xp_sqlmaint"; "sqlmaint utility"

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
288577 Troubleshooting Database Maintenance plan failures
303292 BUG: Expired transaction log backups may not be deleted by Maintenance Plan

Properties

Article ID: 326485 - Last Review: December 10, 2005 - Revision: 4.2
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kberrmsg kbtshoot kbpending kbprb KB326485

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com