Inserting error data into a table from CATCH statement within a TRY CATCH

Question

Inserting error data into a table from CATCH statement within a TRY CATCH

//Ravi

SSCrazy

Points: 2612
More actions
November 8, 2013 at 3:04 pm

#300122

I've been researching for past two days and hitting my head against wall, but I can't figure this out.
I am trying to insert error message into a table along with the data that goes with it. So here how my statement looks:
BEGIN CATCH
--INSERT INTO InsertStatementErrors
SELECT f.* , 0 AS [ErrorResolvedYesNo],
ERROR_MESSAGE() [error_msg],
ERROR_NUMBER() ErrorNBR ,
ERROR_SEVERITY() Severity ,
ERROR_LINE() ErrorLine ,
GETDATE() AS [TimeAndDateAdded]
FROM froi_import f
WHERE Insured_ID = @Insured_ID and record_id = @record_id
ROLLBACK TRANSACTION
END CATCH;
When I comment out the INSERT INTO, it displayed the error on the screen. But when I enable the INSERT INTO, it doesn't insert the data into the table neither it gives me any error message. I can't figure out what I am doing wrong.
------------
🙂

Viewing 12 posts - 1 through 11 (of 11 total)

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

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

You are rolling back the transaction after you insert the errors so that is why they are displayed, but not inserted. You need to insert the errors after you rollback. Of course you will probably lose the data in your table.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

lwe@xtra.co.nz Mr or Mrs. 500 Points: 574 More actions · Answer 2

http://www.sqlservercentral.com/blogs/james-sql-footprint/2013/06/20/use-table-variables-in-transaction/

//Ravi SSCrazy Points: 2612 More actions · Answer 3

Keith, Thanks. That did the Trick. Here is the correct Code:

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION

INSERT INTO rcmd.FROI_InsertStatementErrors

SELECT f.* , 0 AS [ErrorResolvedYesNo],

ERROR_MESSAGE() [error_msg],

ERROR_NUMBER() ErrorNBR ,

ERROR_SEVERITY() Severity ,

ERROR_LINE() ErrorLine ,

GETDATE() AS [TimeAndDateAdded]

FROM rcmd.froi_import f

WHERE Insured_ID = @Insured_ID and record_id = @record_id

END CATCH;

The Variable Idea sound good. I'll try it next time.

------------
🙂

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 4

//Ravi (11/12/2013)
Keith, Thanks. That did the Trick. Here is the correct Code:
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
INSERT INTO rcmd.FROI_InsertStatementErrors
SELECT f.* , 0 AS [ErrorResolvedYesNo],
ERROR_MESSAGE() [error_msg],
ERROR_NUMBER() ErrorNBR ,
ERROR_SEVERITY() Severity ,
ERROR_LINE() ErrorLine ,
GETDATE() AS [TimeAndDateAdded]
FROM rcmd.froi_import f
WHERE Insured_ID = @Insured_ID and record_id = @record_id
END CATCH;
The Variable Idea sound good. I'll try it next time.

I'm not a big fan of the way you've constructed your INSERT/SELECT statements on your CATCH logic. I prefer to specify the columns in both the INSERT statement and the SELECT statement. I say this only because if for some reason rcmd.froi_import were ever to change your catch logic would fail as well. Just saying.

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Kurt W. Zimmerman (11/12/2013)
I'm not a big fan of the way you've constructed your INSERT/SELECT statements on your CATCH logic. I prefer to specify the columns in both the INSERT statement and the SELECT statement. I say this only because if for some reason rcmd.froi_import were ever to change your catch logic would fail as well. Just saying.

+1000 to that.

I also have to say that in general the code makes this look a looping/cursor type of insert. :w00t:

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

//Ravi SSCrazy Points: 2612 More actions · Answer 6

Kurt, I agree with you and I don't like the way it is. But it's one of those things that I users need it "Immediately" and I didn't have time to put 70+ columns in the code. I'll take care of it in version 2 :-).

Sean, yes it is Looping/cursor type of insert. I hate using cursors, but the application, into which the data is being inserted in, can't handle set based inserts. It has to be one by one.

------------
🙂

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

//Ravi (11/14/2013)
Sean, yes it is Looping/cursor type of insert. I hate using cursors, but the application, into which the data is being inserted in, can't handle set based inserts. It has to be one by one.

I will take you word for it but...we don't insert data into applications, we insert data into a database (which can handle multiple row inserts).

At any rate, glad you were able to sort your issue.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

poonamsinha Newbie Points: 1 More actions · Answer 8

Hey Ravi,

Can you please share your full code , I am trying to get the error data in catch block but some how using while loop its getting into infinite loop.

theDarkPrince Newbie Points: 5 More actions · Answer 9

Hi guys. I realize that I am a bit late to the party but I am in the middle of something similar that Ravi is facing. In addition to what Ravi wants to achieve with his code, I need to wrap the INSERT statement inside another transaction. I tried using named transactions but so far I seem to be stuck this error : Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1. Any ideas about how I can achieve this ? Thanks in advance. 🙂

Regards,

Nachiket

lwe@xtra.co.nz Mr or Mrs. 500 Points: 574 More actions · Answer 10

Use a table variable. See https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d7bd8e3f-d08b-4b35-94c0-a81777985edb/logging-messages-even-if-transaction-is-rolled-back#:~:text=SQL%20will%20rollback%20all%20nested%20transactions%20including%20your,from%20the%20table%20variable%20into%20your%20error%20table.

lwe@xtra.co.nz Mr or Mrs. 500 Points: 574 More actions · Answer 11

Use a table variable. See https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d7bd8e3f-d08b-4b35-94c0-a81777985edb/logging-messages-even-if-transaction-is-rolled-back#:~:text=SQL%20will%20rollback%20all%20nested%20transactions%20including%20your,from%20the%20table%20variable%20into%20your%20error%20table.