SQL utilities failing when called within SQL

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.



Answer this question

SQL utilities failing when called within SQL

  • SQL utilities failing when called within SQL