x

sql server logging in try catch

Hi,

How to insert the print statements from a try catch block into a physical table in sql server.

 ALTER procedure usp_masterproc
 as

  DECLARE @ErrMsg nvarchar(4000),
  @ErrSeverity int  ,
   @errorNum int

   Begin  try
begin transaction


   exec sp1;
   exec sp2;
   exec sp3;
   exec sp4;
   exec sp5;
   exec sp6

  commit;
  end try

 begin catch 
 if @@trancount >0 
 rollback

               RAISERROR(@ErrMsg, @ErrSeverity, 16)  
               SELECT @ErrMsg = ERROR_MESSAGE(),    
             @ErrSeverity = ERROR_SEVERITY(),
             @errorNum = ERROR_NUMBER()

    PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR); 
    PRINT 'Error Message: ' + ERROR_MESSAGE(); 
    PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR); 
    PRINT 'Error State: ' + CAST(ERROR_STATE() AS VARCHAR); 
    PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR); 
    PRINT 'Error Proc: ' + ERROR_PROCEDURE(); 

    END CATCH 

For example i will have a table called dailyloadlog (date datetime,errormessage, varchar)

how can i get the print statment into the table dailyloadlog

Thanks
more ▼

asked Apr 24, 2012 at 08:02 PM in Default

Katie 1 gravatar image

Katie 1
1.4k 132 163 202

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You would alter the CATCH portion to just insert the values from the error.

IE:

BEGIN CATCH
IF @@trancount >0 
ROLLBACK

    RAISERROR(@ErrMsg, @ErrSeverity, 16)  
    SELECT @ErrMsg = ERROR_MESSAGE(),    
         @ErrSeverity = ERROR_SEVERITY(),
         @errorNum = ERROR_NUMBER()

INSERT dailyloadlog([date],errormessage)
    SELECT getdate(), @ErrMsg

END CATCH 

You could also just select the message out directly without having to assign it to a variable like this,

INSERT dailyloadlog([date],errormessage)
        SELECT getdate(), Error_Message()
Hope this helps!
more ▼

answered Apr 24, 2012 at 08:10 PM

JohnM gravatar image

JohnM
6.9k 1 3 7

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1853
x991
x415

asked: Apr 24, 2012 at 08:02 PM

Seen: 1068 times

Last Updated: Apr 24, 2012 at 08:13 PM