Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Using SQL Server 2008 R2:

How can I write to the SQL Server Error Log? I have a ROLLBACK statement that I'd like to couple with a statement written to the error log for external monitoring.

Example:

BEGIN TRAN

INSERT INTO table1
SELECT * 
FROM table2

IF @@ERROR <> 0
BEGIN
    ROLLBACK TRAN
    --Write to log
    RETURN
END

COMMIT TRAN

EDIT:

I'd like to clarify - I want to write to the "SQL Server Logs", "Current" log, under the "Management" folder in the object explorer.

share|improve this question
add comment

3 Answers

up vote 6 down vote accepted

AFAIK, the only way to do this is to use the WITH LOG option of the RAISERROR function. Note from that MSDN page that there are certain security requirements that must be met for you to do this.

That said, the SQL Server error log really isn't meant for application-based logging. If you need to add monitoring/logging to your application, this should probably be implemented as a table in your database (for example).

share|improve this answer
    
Agree with Jon, RAISERROR should be use to raise error back to client application -- which in turn can log the error by performing an inset into a separate table. –  SQL Learner Jul 5 '12 at 19:25
    
Agree with both of you. However, there's no client application to throw an exception back to, and the error tracking is handled by a different department, so my hands are tied... Thanks for the info. –  Nick Vaccaro Jul 5 '12 at 20:21
add comment

While RAISERROR ... WITH LOG is possible, don't forget that

Only a member of the sysadmin fixed server role or a user with ALTER TRACE permissions can specify WITH LOG.

In production you would have to wrap the WITH log generating code in a stored procedure properly signed.

Depending on what you're trying to achieve, there are far better options for monitoring. for example you can trace a user message using sp_trace_generateevent.

share|improve this answer
add comment

you need to use RAISERROR with LOG

BEGIN TRY
  -- Error here
  SELECT 1/0
END TRY
BEGIN CATCH
  RAISERROR('Ouch... divie by zero', 16,1) WITH LOG
END CATCH
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.