BEGIN TRY/CATCH in a MERGE Statment with OUTPUT to capture Record Counts

Question

BEGIN TRY/CATCH in a MERGE Statment with OUTPUT to capture Record Counts

Viewing 5 posts - 31 through 35 (of 35 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

I modified the code to return the Error Information as a Parameter and I perform the Inserts in the CATCH Block of the calling Stored Procedure.

The calling SP Inserts records into a table.

If I do not truncate a record is inserted into the log table that a Foreign Key Violation occurred but of course the calling SP completes successfully.

I'm not sure what to do differently. Some of the Loads are performed via an SP and I use OLE DB for many others.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gazareth One Orange Chip Points: 27737 More actions · Answer 2

If I understand it right, you want to see the original error, rather than 'SP completed successfully'?

This article contains code to rethrow an error from a catch block (usp_RethrowError):

http://msdn.microsoft.com/en-us/library/ms179296(v=sql.105).aspx

I use a modified version of this in some of my databases. In your case, it's the equivalent of usp_GetErrorInfo I think.

Be sure to note the points on ROLLBACK, XACT_STATE, etc.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Welsh Corgi (7/27/2012)
I do not see an option to redirect errors from an Execute SQL Task?

That was kind of the point of my previous comment. If the insert or merge functionality is buried in the T-SQL of an an EXECUTE SQL task - the error handling is restricted to what the DB engine can provide. If on the other hand you perform the insert via mapping an "source object" (ADO/OLEDB/ODBC/XML/ETC) to any of the transform items to a destination (basically same list as before), then you'll get access to SSIS' ON ERROR redirect options. Siimply go into the tools and configure error output to give yourself access to teh data rows that failed.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

I appreciate all of the feedback.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

Matt Miller (#4) (7/27/2012)
Welsh Corgi (7/27/2012)
I do not see an option to redirect errors from an Execute SQL Task?
That was kind of the point of my previous comment. If the insert or merge functionality is buried in the T-SQL of an an EXECUTE SQL task - the error handling is restricted to what the DB engine can provide. If on the other hand you perform the insert via mapping an "source object" (ADO/OLEDB/ODBC/XML/ETC) to any of the transform items to a destination (basically same list as before), then you'll get access to SSIS' ON ERROR redirect options. Siimply go into the tools and configure error output to give yourself access to teh data rows that failed.

I'm loading Staging and the Data Warehouse Tables.

I prefer to use OLEDB but on the large tables it takes too long and have to resort to an SP with a Merge so that creates a problem as far as redirecting row that failed.

Thanks again.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/