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

avatar image

Katie 1
1.4k 132 164 205

(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

avatar image

JohnM
12.4k 3 7 14

(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:

x2077
x1067
x455

asked: Apr 24, 2012 at 08:02 PM

Seen: 1233 times

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

Copyright 2016 Redgate Software. Privacy Policy