May 27, 2005 at 9:10 am
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
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