Microsoft KB Archive/326485

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

Article ID: 326485

Article Last Modified on 12/10/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q326485





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 &quot;-S&quot;. For example, the directory path &quot;C:\Db-Save&quot; contains an &quot;-S&quot; string.



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



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 &quot;-S&quot; 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:



SQL Server 2000 Books Online; topics: &quot;xp_sqlmaint&quot;; &quot;sqlmaint utility&quot;

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

Keywords: kberrmsg kbtshoot kbprb kbpending KB326485

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.