Hello -
Certain jobs on one of my SQL 2000 (SP3 with security patch) servers started failing last week. Nothing has changed on this server as far as I am aware. I have narrowed it down to jobs that call the SQL utilities sqlmaint.exe and isql.exe.
The error message from sqlmaint.exe (which is run in a job step with "EXECUTE master.dbo.xp_sqlmaint N'-PlanID D919160A-5406-4D16-BF27-00A7DF3337BB -Rpt "F:\mssql\LOG\Backup\Transaction Logs6.txt" -DelTxtRpt 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog "F:\mssql\Data" -DelBkUps 2DAYS -CrBkSubDir -BkExt "TRN"'"") is
Executed as user: CBNWEB\sqlsrv_domain. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
isql.exe and osql.exe also fail, but without error messages and without actually running the specified query.
When I run the same commands from within Query Analyzer, they also fail. When I run similar commands from a DOS prompt, they run successfully. (Like the successful "sqlmaint.exe -PlanID D919160A-5406-4D16-BF27-00A7DF3337BB -Rpt "F:\mssql\LOG\Backup\Transaction Logs6.txt" -DelTxtRpt 4WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog "F:\mssql\Data" -DelBkUps 2DAYS -CrBkSubDir -BkExt "TRN"'"")
I thought it might be a difference in the users, so I ran the command as the same account as the SQLAgent, and it worked from the DOS prompt. Just not as a scheduled job or from Query Analyzer.
Here's an example with isql.exe (or osql.exe) called from Query Analyzer:
set nocount on declare @rc int exec @rc = xp_cmdshell 'isql -E -Q "select @@version"' select @rc as ReturnCode
The output when it runs successfully on another server is
----------------------------------------------------------------------- Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1) NULL (1 row affected) NULL ReturnCode ----------- 0
The output on the server where the utilities are failing is
output ------------------------------------------------------------- NULL ReturnCode ----------- 128
What could cause this difference in behavior where the commands only error within jobs or from Query Analyzer Rebooting would probably correct the problem, but this server is supposed to be 24x7. Also, the cause of the problem would not be found.
Thank you in advance for any help!
Tammy
P.S. I forgot to mention that I can run other .exe files from xp_cmdshell successfully. Just not the SQL ones.

SQL utilities failing when called within SQL