All,

We have written a stored procedure that is called by an Execute SQL
task. In the stored procedure, we are trying to use Raiserror whenever
we encounter a business error (number of rows returned = 0 , sum of
two fields not being equal, etc.).

I read a couple of postings that mentioned this problem and it seems
that Raiserror had to be the very first line in the SQL block - till
SP3 supposedly fixed the problem. However, I am still running into the
problem. I have tried the following:

1. Returned a code of -1 from the stored procedure and check it within
the Execute SQL Task:

Declare @output
exec pr_chk_something @output
IF @output = -1 RAISERROR (msg, 16,1)

Strangely, this worked a couple of times before refusing to throw
errors again.

2. Deleted the Execute SQL task and added a new task with the exact
same code. Again, this solution worked a couple of times before it
stopped throwing errors again.

Has anyone else seen this behavior in DTS ? Any ideas on how to
resolve it ?

thanks in advance,

Kumar,

You can use a signal code to return from the stored procedure and deal with
it in a subsequent ActiveX task. Just be aware that if you hit a true sql
error the return code will be overwritten with one of reserved negative
codes from -14 to -1.

Ilya

"Kumar" <kumarbs (AT) lycos (DOT) com> wrote

Thanks Ilya, that is a good workaround. That would mean adding an
additional ActiveX task after the Exec SQL task - given the way our
workflow is right now, we are trying to cut down the number of tasks
we are using.

I am trying to find a resolution to why Raiserror does not work within
an Execute SQL task when it is not the first SQL statement to be run.
It should either be an acknowledged bug by MS or it should have some
sort of fix me thinks.

"Ilya Margolin" <ilya_no_spam_ (AT) unapen (DOT) com> wrote

Kumar,

Try raising severity level to 21 which ends up session breaking connection:
RAISERROR (msg, 21,1)

Ilya

"Kumar" <kumarbs (AT) lycos (DOT) com> wrote