SQL Server Performance Forum – Threads Archive

DTS Execute SQL Task + stored proc error handling

Hi All,
I am using SQL Server 2000 (SP2) and am designing a DTS package.
I have an Execute SQL Task that calls a stored procedure.
The trick here is that I want to return a result when a particular error occurs in the stored procedure. Namely, if I try to insert a row that violates a null insert constraint (or similar). The stored procdure has a statement like :

INSERT INTO mytable (not_null_col) VALUES (null)
IF (@@ERROR > 0)
SELECT ‘My error statement’ as mymsg

When I run this in the Query Analyzer, this works beautifully – the error message from SQL Server appears in the Messages window, and my error message is returned in Grids.
The problem is that the Execute SQL Task seems to just chuck everything in once it finds a statement that returns an error. My error SELECT statement is never run – even if I remove the IF statement!
Is this just a limitation of the Execute SQL Task? Is there another way around this? In reality, the error I want to return will need to run a query to get a bit more information about the error so I really do need to have the task continue.
Any help is greatly appreciated,
Steele.

---

See
http://www.sommarskog.se/error-handling-II.html Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

---

Thanks for the info Roji, but it’s not quite what I am looking for.
As I mentioned, the actual stored procedure works well when run from anywhere but the DTS package. The failure on the null insert into a non-nullable column seems to halt the Execute SQL Task no matter what I do with the error afterwards.
Any other suggestions?
Steele.

---

]]>