Output errors from SQL Server stored procedure

35 pts.
Tags:
SQL
SQL error handling
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

Answer Wiki

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

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Spionred
    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
    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
    In Oracle you could also use autonomous transactions, so you can commit the error log separately.
    69,175 pointsBadges:
    report
  • Spionred
    Thanks for the advice. I will report back when I have cracked it. Cheers, kevin.
    35 pointsBadges:
    report
  • Kccrosser
    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
    Interesting, Kccrosser. It seems that you have many years of experience in Oracle. That's valuable.
    69,175 pointsBadges:
    report
  • Denny Cherry
    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.
    66,075 pointsBadges:
    report
  • Spionred
    Thanks, I will give that a try. Kevin.
    35 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following