DTS Execute SQL Task Terminates without failing when error occurs (SP3)

[Gerald Dunn]

I'm running SQL Server 7.0 SP3 and having trouble with DTS.

I have an Execute SQL Task that runs several stored procedures. When one of the stored procedures fails, the Execute SQL Task just terminates without failing.

I found a knowledge base article (Q238523) dealing with this situation but it was supposedly fixed in SP2 - I have SP3! The other work around suggested, issuing a SET NOCOUNT ON, does not always work.

Has anyone else run into this or have any other suggestions.

I hate the thought of spending days doing another work around in order get basic DTS functionalty to work as it should!

Thanks!

Jerry Dunn
gdunnjr@yahoo.com

[JC Moore]

I ran into this problem yesterday and found the same KB article. I'm running a single stored procedure and am experiencing the same problem. Unfortunately, I just inherited this server yesterday and it crashed last night (before I found the KB article) which has prevented me from verifying the service pack. It's a hard problem to identify. I will post again later after a fix is in place.


------------
Gerald Dunn at 3/21/01 11:33:34 AM

I'm running SQL Server 7.0 SP3 and having trouble with DTS.

I have an Execute SQL Task that runs several stored procedures. When one of the stored procedures fails, the Execute SQL Task just terminates without failing.

I found a knowledge base article (Q238523) dealing with this situation but it was supposedly fixed in SP2 - I have SP3! The other work around suggested, issuing a SET NOCOUNT ON, does not always work.

Has anyone else run into this or have any other suggestions.

I hate the thought of spending days doing another work around in order get basic DTS functionalty to work as it should!

Thanks!

Jerry Dunn
gdunnjr@yahoo.com

[Gerald Dunn]

I found a work around to this problem and am posting it to help out anyone else who may run into this issue.

It turned out that the package was not terminating after the error, but was continuing and just ignoring my RAISERROR statement and ignoring the errors raised in my stored procedures.

The fix is to put a GO statement after each and every SQL statement in the Execute SQL Task. Then errors from within the stored procedures will cause the task to fail and the RAISERROR statement within the execute sql task will also work.

--Jerry


------------
Gerald Dunn at 3/21/01 11:33:34 AM

I'm running SQL Server 7.0 SP3 and having trouble with DTS.

I have an Execute SQL Task that runs several stored procedures. When one of the stored procedures fails, the Execute SQL Task just terminates without failing.

I found a knowledge base article (Q238523) dealing with this situation but it was supposedly fixed in SP2 - I have SP3! The other work around suggested, issuing a SET NOCOUNT ON, does not always work.

Has anyone else run into this or have any other suggestions.

I hate the thought of spending days doing another work around in order get basic DTS functionalty to work as it should!

Thanks!

Jerry Dunn
gdunnjr@yahoo.com