How to catch the exit code of a CMDEXEC SQL Server

Question

The following SQL Server job always exits with return code 0 indicating success, when in fact it does not do its job, i.e. it does not delete "test.txt". How can I catch the actual exit code (something like %ERRORLEVEL%, or a 'permission denied'-like message, or any meaningful response indicating success or failure of @command at msdb.dbo.sp_add_jobstep)?

Remarks:

• {DBname} is the name of the database I am the owner of
• {proxyName} is the name of the SQL Server Agent Proxy (that is active to the 'Operating system (CmdExec)' subsystem) belonging to a credential that maps to a Windows domain login in SQL Server that has full control to {folderUNC}
• {folderUNC} is the full UNC path to the folder where "test.txt" is

A few details that might be useful:

• Server: Microsoft SQL Server Enterprise Edition (64-bit) version 9.00.4060.00
• OS: Microsoft Windows NT 5.2 (3790)
• I am not a sysadmin, but only the owner of database {DBname}

CODE:

USE {DBname}
GO

DECLARE @returnCode AS INT
DECLARE @jobName NVARCHAR(128)
DECLARE @jobStep1Name SYSNAME

SET @jobName = 'CMDEXEC Test Job' 
SET @jobStep1Name = 'CMDEXEC Test Job Step 1'

EXEC @returnCode = msdb.dbo.sp_add_job 
    @job_name = @jobName,
    @enabled = 1,
    @start_step_id = 1,
    @notify_level_eventlog = 2,
    @delete_level = 0;

 IF (@@ERROR <> 0 OR @returnCode <> 0)
     PRINT N'Error @ sp_add_job. @@ERROR = ' + CONVERT(VARCHAR(10), @@ERROR) + 
          N' @returnCode = ' + CONVERT(VARCHAR(10), @returnCode

EXEC @returnCode = msdb.dbo.sp_add_jobstep
    @job_name = @jobName, 
    @step_id = 1, 
    @step_name = @jobStep1Name, 
    @subsystem = 'CMDEXEC', 
    @command = 'DEL {folderUNC}\test.txt',
    @cmdexec_success_code = 0,
    @on_success_action = 1,
    @on_fail_action = 2,
    @proxy_name = '{proxyName}';

 IF (@@ERROR <> 0 OR @returnCode <> 0)
     PRINT N'Error @ sp_add_job. @@ERROR = ' + CONVERT(VARCHAR(10), @@ERROR) + 
          N' @returnCode = ' + CONVERT(VARCHAR(10), @returnCode

EXEC @returnCode = msdb.dbo.sp_add_jobserver 
    @job_name = @jobName;

 IF (@@ERROR <> 0 OR @returnCode <> 0)
     PRINT N'Error @ sp_add_job. @@ERROR = ' + CONVERT(VARCHAR(10), @@ERROR) + 
          N' @returnCode = ' + CONVERT(VARCHAR(10), @returnCode

EXEC @returnCode = msdb.dbo.sp_start_job 
    @job_name = @jobName,
    @step_name = @jobStep1Name;

 IF (@@ERROR <> 0 OR @returnCode <> 0)
     PRINT N'Error @ sp_add_job. @@ERROR = ' + CONVERT(VARCHAR(10), @@ERROR) + 
          N' @returnCode = ' + CONVERT(VARCHAR(10), @returnCode

EXEC @returnCode = msdb.dbo.sp_delete_job 
    @job_name = @jobName;

 IF (@@ERROR <> 0 OR @returnCode <> 0)
     PRINT N'Error @ sp_add_job. @@ERROR = ' + CONVERT(VARCHAR(10), @@ERROR) + 
          N' @returnCode = ' + CONVERT(VARCHAR(10), @returnCode

Answer 1

The trouble seems to be that del does not set the errorlevel when it fails. There are several ways to 'work around' this, but personally I'd go for either writing my own delete.exe, or via a .bat file.

Example : (delete.bat)

@echo off

del "%1"
if exist "%1" GOTO FAIL
EXIT /B 0

:FAIL
ECHO "File still there ! (%1)"
EXIT /B 1

And you can call it like this (@command):

delete.bat c:\test.txt

it will return as failed when the file is still there after supposedly being deleted.