Capturing success/failure of a stored proc called from an Execute SQL task

Question

Capturing success/failure of a stored proc called from an Execute SQL task

Frank The Tank

SSC Eights!

Points: 870
More actions
March 18, 2004 at 7:27 am

#359304

I have a stored procedure that returns no output that I want to call from within a DTS package, using the Execute SQL task. How can I simply do error trapping for this scenario? I'd like to capture the return value from the stored proc, and fail the task if the return value is not zero. I figure I need to set a global variable equal to the return code and pass this value to some simple Active X script, which then handles the failure or success actions. (It's the last task in the package, so basically if it succeeds, nothing needs to happen. If it fails, I want to call another SQL task that sends an email using an extended stored proc). Anyone know the simplest way to accomplish this? I am not a "VB guy" so I'm not sure what the script should look like.
TIA...

Viewing 8 posts - 1 through 7 (of 7 total)

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

Bill Nye Hall of Fame Points: 3248 More actions · Answer 1

Unless you really need the email part in a separate task, just use something like this in the original Execute SQL task:

declare @err int

EXEC my_proc

set @err=@@error

--NOTE:@@ERROR returns the number of the error message ONLY until

-- another Transact-SQL statement is executed so there can be

-- nothing between EXEC and set lines

if @err <>0

Begin

EXEC xp_sendmail ...

End

Frank The Tank SSC Eights! Points: 870 More actions · Answer 2

Thanks Bill. That will indeed work from a functional perspective. The only problem with it is that from DTS' perspective, the task will complete "successfully" even if the stored proc failed. The subsequent call to the send mail code will succeed and so it will seem to DTS that the task itself completed without error. I need to somehow capture the fact that the initial proc call failed, hence the idea of using Active X.

I could probably take the code from the stored proc and run it directly within the sql task, but there are already performance issues involved and I think that will make things even slower...

Bill Nye Hall of Fame Points: 3248 More actions · Answer 3

I must have missed something . I thought you simply wanted to send the email if your proc failed.

If you want the DTS task to fail, try this:

In my above example: after xp_sendmail, put in junk sql such as select "Fail package here". That will cause the task to fail after sending the email.

Or, if you want it in a separate task:

Task1: EXEC myproc -->Workflow: On Failure-->Task2:sendmail (I should have mentioned this way the other day).

If you're trying to capture the error information, you could also use package logging or create your own errorlog table and use something like "Insert into ErrorLog(Error) values(@err)" to track the error causes.

Let me know if I'm still misunderstanding.

RE: "I could probably take the code from the stored proc and run it directly within the sql task" - I don't see how that would help.

I suppose you could do this with ActiveX, but since you're "not a VB guy" and it can be done without it ...

Bill

Frank The Tank SSC Eights! Points: 870 More actions · Answer 4

Hi Bill

Thanks for the replies. Sorry if I am confusing the issue. What it comes down to is this: If the stored proc fails due to a hard database error (@@error <>0) then the task should fail, that's the easy part. I need to be able to capture business logic "soft" failures that are coded within the proc as well (stored proc return value <> 0). An execute SQL task itself does not fail just because a stored proc returns a value other than zero. That's why using the standard On Failure workflow won't work for me here.

I like your idea about inserting some "junk sql" after the email is generated. That should do the trick nicely.

Thanks again.

Peter Evans Ten Centuries Points: 1187 More actions · Answer 5

What about using the RAISEERROR statement in the wrapper proc bill suggested?

Peter Evans (__PETER Peter_)

Bill Nye Hall of Fame Points: 3248 More actions · Answer 6

You're right, Peter .

Raiserror would be a nicer way of handling it.

Bill

Michael Nicol SSC Enthusiast Points: 117 More actions · Answer 7

I've been struggling with something similar.

DTS Package with "Execute SQL Task". SQL to execute = "EXEC SP_someSP". No inputs, no outputs. All I wanted to do was get DTS to acknowledge that the step failed and use the workflow to control the er..., well, workflow.

After numerous hours of searching, I have it working. Make sure you're patched to latest version, i.e. SQL 2000 Patch 3a and security patches as well. After this, "SELECT @@Version" should be at the very least "Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3) "

In the Stored Procedure that you are trying to "RAISERROR" from, make sure that the severity level is at least 11 (see Books Online), i.e. "RAISERROR ('msg', 11, 1)". Severity levels 1-10 are informational only and don't cause actual errors, despite the name of the command being RAISERROR! Go figure.

Hope this helps somebody.

Mike