Execute SQL Task

Question

Execute SQL Task

aMSDeveloper

Right there with Babe

Points: 718
More actions
May 4, 2005 at 12:51 pm

#61612

Hi,
I want to capture an error in Execute SQL Task and raise an error in the job which runs the DTS Package.
I am copying files between servers and if the copy fails I want the Execute SQL Task to raise an error.
Here is the code.
Declare
@i int
BEGIN
exec master..xp_cmdshell @i = 'copy files from server a to server b'
If @i > 0
Raiserror('copy failed', 16, 1)
This code doesnt execute a raise error because the DTS Task successfully finished without errors, even though the code in the Execute SQL Task might fail (and in the above code a raiserror is raised).
So, how do we raise error to the job level? Any ideas?

Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

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

THNQdigital SSC Eights! Points: 849 More actions · Answer 1

Hi Balaji,

Have another step after your copy step is over ,You could use a activex script ( use file system object) to check if the file you are trying to copy is actually copied to the destination server/fodler.. based on this check you can decide what to do next like... you can send an email with an appropriate message.. or you could set an global variable with desired value to be used in some other step if you want to handle,, like "file not copied " exception etc.. or take any other action conditionally based on the global varibale value any time inside the package

HTH

THNQdigital

philcart SSC-Forever Points: 47794 More actions · Answer 2

Better option is to wrap your call to xp_cmdshell in your own stored procedure that returns a success/failure via an output parameter. Then you can pick it up using the global variable assignment in the ExecuteSQL task.

--------------------
Colt 45 - the original point and click interface

Tobi White Mr or Mrs. 500 Points: 551 More actions · Answer 3

Place your code in an activeX script if it isn't already. Check the return value of the proc call either by using recordset, or parameter. If the error is returned then set your execution status to failure for the step instead of success.

mike gercevich SSCrazy Points: 2961 More actions · Answer 4

I've used an @ResultStatus BIT OUTPUT parameter in many procs that I call from DTS packages. Default the param to 0. Place an error handler in the script that gets called if @@ERROR <> 0. If the entire transaction commits, you set the param to a value of 1.

I believe there is more control using an ActiveX script over ExecuteSQL Task... but that is up to your preference. SQL 2000 also gives a system generated output parameter defined as an integer value of @RETURN_VALUE. On successful execution, the value is 0. the value of @@ERROR is returned if an error is raised.

-Mike Gercevich