Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error Handling Expand / Collapse
Author
Message
Posted Friday, May 31, 2013 12:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 6:28 AM
Points: 23, Visits: 409
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)
Post #1458828
Posted Thursday, June 6, 2013 3:22 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 28, 2014 3:27 AM
Points: 337, Visits: 456
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



Joe
Post #1460595
Posted Thursday, June 6, 2013 5:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 6:28 AM
Points: 23, Visits: 409
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.
Post #1460633
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse