asked
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
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!
4 People are following this question.
