0

I have a database that I am working on with over 900 SP's. None of the SP's have any error handling. Is there a utility within SQL Server 2005 or 2008 that would automatically log the SP and the error into a table?

mattgcon
  • 4,768
  • 19
  • 69
  • 117
  • 1
    Maybe the code that calls the sp's would like to log errors? – John Saunders Aug 28 '11 at 00:33
  • LOL no the code would not like to log those errors. Imagine if there are 900+ SP's, how big the code that handles all those SP's – mattgcon Aug 28 '11 at 00:59
  • How big is the code that _calls_ those 900+ SPs? That's the code that should log it, and if you were handling exceptions properly, it wouldn't require much code to do logging. – John Saunders Aug 28 '11 at 01:08
  • You can do it [through tracing in 2005/2008](http://stackoverflow.com/questions/5199812/logging-erroneous-queries-only-on-sql-server/5199933#5199933). Though it will be quite heavy weight even though you are filtering out a lot. Extended Events in 2008 can fire only on error. – Martin Smith Aug 28 '11 at 01:12

1 Answers1

0

If the SPs are being called from code in a separate data layer, you could possibly add a global exception handler for that class. There is no 'global' error handling, per-se in sql server as far as stored procedures go - think about code - in code, if you had a gazillion classes, and there was no ineritance of any sort, you would have to implement error handling on each class separately. Sql server SPs have their own error handling, such as try-catch and @@error - look on books online, or http://www.codeproject.com/KB/database/ErrorHandling.aspx

M.R.
  • 4,737
  • 3
  • 37
  • 81