Output errors from SQL Server stored procedure

Spionred

35 pts.

Tags:

SQL

SQL error messages

SQL Server Express

SQL Server stored procedures

Stored Procedures

I have a stored procedure using SQL Express that update a table using data from another table. All is well except that from time to time the source table might have data that causes an error. I would like to add some code to the stored procedure that will catch the error, output the details to a text file then continue with the procedure. How do I do this? Cheers, Kevin

Asked: April 23, 2009 1:28 AM Last updated: April 26, 2009 8:00 PM

Answer Wiki

Last updated: April 23, 2009 2:10 AM GMT

carlosdl84,495 pts.
History
Contributors
- Ordered by most recent
- carlosdl84,495 pts.

Thanks. We'll let you know when a new response is added.

How about logging the error to a table ?

If you are on 2005+ you could use TRY and CATCH blocks.

For example:

<pre>
BEGIN TRY
— Your code
END TRY
BEGIN CATCH
insert dbo.Application_Error_Log (UserName, tableName,
errorNumber, errorSeverity, errorState, errorMessage)
values (suser_sname(), @tableName, ERROR_NUMBER(),
ERROR_SEVERITY(), ERROR_STATE(), ERROR_MESSAGE())
END CATCH;</pre>

Discuss This Question: 8 Replies

Spionred Apr 23, 2009 4:51 AM GMT

Yeah, I was hoping to just write to a txt file but I suppose once the process has complete I could query the table from code and output to file from there.

35 pointsBadges:

report
Kccrosser Apr 23, 2009 4:06 PM GMT

Keep in mind one common "gotcha" when dealing with error logging. If the procedure is called from within a transaction, or if the failing statement is occurring inside a transaction, and you need to do a rollback to maintain the overall transaction integrity, there is no way to write the log record to a table, as that write will also be rolled back. I solved this in an Oracle system using the dbms_pipe package. With dbms_pipe, you can create a "listener" process to receive and log messages, and pipe messages are not rolled back in transactions, so when an error occurs in a transaction, simply "pipe" the log information over to the listener to record it, and then execute the rollback to ensure transaction integrity.

3,830 pointsBadges:

report
carlosdl Apr 23, 2009 7:08 PM GMT

In Oracle you could also use autonomous transactions, so you can commit the error log separately.

84,495 pointsBadges:

report
Spionred Apr 23, 2009 9:09 PM GMT

Thanks for the advice. I will report back when I have cracked it. Cheers, kevin.

35 pointsBadges:

report
Kccrosser Apr 24, 2009 3:39 PM GMT

Ref the autonomous transactions - this was in a system we built in 1997-2000 on Oracle 7, so those weren't an option. We wound up with a little over 1 million lines of PL/SQL code in lots of layers, and needed a way to log errors (and debug and stats, etc.) without worrying about whether there was a transaction in progress. With the DBMS_PIPE package, it was trivial to create a simple log procedure that anything could call and pass the data to the logging table.

3,830 pointsBadges:

report
carlosdl Apr 24, 2009 6:49 PM GMT

Interesting, Kccrosser. It seems that you have many years of experience in Oracle. That's valuable.

84,495 pointsBadges:

report
Denny Cherry Apr 26, 2009 4:28 AM GMT

In SQL Server you can create a table variable and put the error information into that table variable. Then after committing or rolling back your transaction you can take the data from the table variable and put it into an actual table. There isn't any way to output from a stored procedure to a text file directly.

68,390 pointsBadges:

report
Spionred Apr 26, 2009 8:00 PM GMT

Thanks, I will give that a try. Kevin.

35 pointsBadges:

report