Output errors from SQL Server stored procedure
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
Software/Hardware used:
Software/Hardware used:
ASKED:
April 23, 2009 1:28 AM
UPDATED:
April 26, 2009 8:00 PM
Answer Wiki:
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>
To see all answers submitted to the Answer Wiki: View Answer History.
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.
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.
In Oracle you could also use autonomous transactions, so you can commit the error log separately.
Thanks for the advice.
I will report back when I have cracked it.
Cheers,
kevin.
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.
Interesting, Kccrosser.
It seems that you have many years of experience in Oracle.
That’s valuable.
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.
Thanks,
I will give that a try.
Kevin.