SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error Handling


Error Handling

Author
Message
David.Lester
David.Lester
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 492
I am fairly sure I already know the answer to this, but any input others may have, or experience would be handy, in case I have overlooked some idea.

Suppose you have a large warehouse system, a combination of SSIS and stored procs, and this system was not built with any form of error handling. This is the start point.

Management wants there to be some "switch to throw" which would cause the entire process to ignore and continue to process on any errors, such as a duplicate key insert.
(We have been over and over all the serious problems with such an idea)

Is there actually such a thing? Or some method which one could copy/paste to every SSIS or stored proc which would suddenly handle the various errors that could possibly happen?

As a note this idea got started because there was an issue with the cube, and the cube was set (originally for a single run) to ignore all errors. So it had a "switch", from the management perspective.

To the best of my knowledge, to handle the errors, we need to change the code, all of the places where it could be a problem to properly handle the errors. That there is no sort of configuration switch at the database or SSIS server level which can be implemented to drop all error records, but continue on and process all non error records.

Any thoughts? (Besides the disaster which looms in the future when ignoring errors entirely)
dfine
dfine
SSChasing Mays
SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)

Group: General Forum Members
Points: 652 Visits: 597
I could only think you need to add error handling in your code

Example: you can think of following code to handle the duplicate key issue

BEGIN TRY
CREATE UNIQUE CLUSTERED INDEX ix_tblname ON tblname (col1,col2)
END TRY
BEGIN CATCH
;WITH dupes AS
(SELECT * , ROW_NUMBER() OVER ( PARTITION BY col1,col2 ORDER BY col1, col2) AS Rownum FROM tblname )
DELETE FROM dupes
OUTPUT DELETED.* INTO #Log
WHERE Rownum > 1
CREATE UNIQUE CLUSTERED INDEX ix_tblname ON tblname (col1,col2)
END CATCH



Raj
David.Lester
David.Lester
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 492
That is what I was thinking as well. Essentially the management wants the existing code based fixed to handle errors without spending any time coding. None of us believe that is possible. I thought to myself, if there was some clever or unique way to do it, someone on this board would know.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search