Error Handling->xp_cmdshell

Last Post 24 Oct 2011 07:18 AM by phsatech. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
phsatech
New Member
New Member

--
20 Oct 2011 02:33 PM
Hi there:

I have a script that uses xp_cmdshell to copy over a database backup file.  I'd like to be able to have a stored procedure (email notification of error) called if either "The network path was not found" or "the system cannot find the file specified'.  These were both error messages returned by sql when testing around with bogus locations.

Here's the sql:
 

xp_cmdshell 'COPY \\server1\backup$\PA880PRD_Full_LiteSpeed.BAK\\server2\backup$\PA880PRD_Full_LiteSpeed.BAK'


Appreciate insight
gunneyk
New Member
New Member

--
20 Oct 2011 05:16 PM
You can create a table and dump the results from xp_cmdshell into that and then p*** the messages. Essentially you would create a table like this:

DECLARE @Return INT ;
CREATE TABLE #Errors ([Results] NVARCHAR(1000)) ;

And then do an Insert Into EXEC similar to this:

INSERT INTO #Errors ([Results])
EXEC @Return = master..xp_cmdshell 'Your cmd script here' ;

And also check the return (@Return) value as well.
phsatech
New Member
New Member

--
24 Oct 2011 07:18 AM
Great, thanks for this.  It did the trick!
You are not authorized to post a reply.

Acceptable Use Policy