SQL Server Performance Forum – Threads Archive
Trapping SQL erros inside DTS
I have a sp where a cursor get a record from table1<br />and insert into table2.<br />I handle the error 2627 (Violation of PRIMARY KEY constraint .. )<br />so that the cursor move to the next valid record<br />in order to do the correct insert.<br /><br />The sp works fine<br /><br />when I call the same sp from a DTS (in Execute SQL Task ) with exec<br />usp_DialogoSmartStream_Remedy<br />the DTS fails as soon as sp raises the 2627 error <img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ /><br /><br />How do I do to avoid DTS failed ?<br /><br />Thanks a lot.<br /><br /><br /><br /><br />best regard,<br /><br />Leandro.
Couple of methods we use: – Error logging via the Logging tab of the DTS Package Properties. This logging captures all DTS activities at a physical level, reporting runtime errors, # of rows updated, and tasks started and completed. – Making use of the "On Failure" workflow within your package. This technique is best for capturing "Logical" errors. For example, you could use ActiveX to force an "On Failure" event if a table’s data didn’t meet a certain business condition. I think there’s a function in the DTS object model for the Step object called GetExecutionStatus. Take a look at that. There is a KBAhttp://support.microsoft.com/default.aspx?scid=kb;EN-US;Q221193 on this and also a good section in SQL2000 Books Online. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>