Post reply

SQL Server Agent jobs don''t fail on xp_cmdshell error

  • May 27, 2005 at 9:10 am

    #109847

    Part of our nightly SQL Server backup jobs are steps to copy the backup file(s) to another server.  We use xp_cmdshell to accomplish this.  Here is an example:

    exec xp_cmdshell "net use s: \\servername\sharename"

    exec xp_cmdshell "del s:\servername\*.bak"

    exec xp_cmdshell "copy f:\mssql\backup\master.bak s:\folder\master.bak"

    exec xp_cmdshell "net use s: /d"

    Sometimes, the copy process fails.  The problem is, the SQL Server Agent job does NOT fail.  I need a way to get the job to fail when the xp_cmdshell commands fail.  I have tried the following, but the job still does not fail:

    DECLARE @rc int

    EXEC @rc = master.dbo.xp_cmdshell "copy f:\mssql\backup\msdb.bak s:\monf07652db\msdb.bak"

    Any ideas?

    “If you're not outraged at the media, you haven't been paying attention.”

  • May 27, 2005 at 3:54 pm

    #561600

    If SQL Agent successfully calls the command shell, well, it considers it a success regardless of what happens in that shell.

    To accomplish what you are trying to do, create an additional step in the job, and use xp_fileexist to determine if the transfer was successful.  xp_fileexist is an undocumented extended stored procedure, so be aware that it could change unannounced in a future version/service pack.  It uses two parameters, the first is the filename (with path), the second is a result.  1 if the file exists, 0 if it doesn't.

    declare @result int

    exec master..xp_fileexist 'c:\my_folder\my_file.txt', @result output

    print @result

    If the result is 0, you can try again, or throw an error and fail the job.

    Steve

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply