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.
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.
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").
Microsoft is researching this problem and will post more information in this article when the information becomes available.
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:
Troubleshooting Database Maintenance plan failures
BUG: Expired transaction log backups may not be deleted by Maintenance Plan