June 18, 2012 at 5:53 am
Guys,
If an SP causes an error - e.g. you've written an SP that uses a table that doesn't exist, is this logged somewhere in SQL, and if so, can this information be readily queried using T-SQL?
I've found this article http://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/ and I can get all the messages out just fine, however, I don't see any related to stored procedures causing errors. If an error is printed in the gridview is this by default stored somewhere or does one need to have specifically configured the SP to write to an error log?
I've done loads of searching on this and I've seen people suggesting error capturing should be built into procedures, however, I just want to see the errors that have occured, it seems like this might not be possible?!
I had it in my mind I'd be able to see a list of the errors generated and then somehow join this to a catalog to see the object that had cause the error, and then filter on severity level... perhaps I'm dreaming here though!
Any feedback much appreciated.
June 18, 2012 at 7:18 am
If that functionality exists out of the box I'm not familiar with it. I've always logged errors to a table in my catch blocks.
June 18, 2012 at 10:39 am
written an SP that uses a table that doesn't exist
I think your best chance is to try the following DMVs:
sys.dm_sql_referenced_entities
sys.dm_sql_referencing_entities
Other than that, I don't of anything else within SQL that could provide that info.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 18, 2012 at 11:10 am
I have a few highly critical procedures that I raise a event error on if they fail and have SCOM pick up the event and alert me of the failure.
You can use master..xp_logevent to do it.
June 18, 2012 at 11:12 am
Rob-350472 (6/18/2012)
Guys,If an SP causes an error - e.g. you've written an SP that uses a table that doesn't exist, is this logged somewhere in SQL, and if so, can this information be readily queried using T-SQL?
No and no, unless the error is severe enough to be logged in the SQL error log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2012 at 1:46 am
Boo! That's what I thought after some playing around for a while.
Is there any third party app which would achieve this or do all the SPs need to be edited so they're logging somewhere first?
June 19, 2012 at 2:58 am
Extended events (seeing as you're on SQL 2008) would probably be the optimal if you just want to record the errors that have happened. But if you want error handling, the procedures need to be changed to include error handling
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2012 at 8:42 am
I think this was a two stage process really. In the first instance we were trying to ascertain how many errors and at what severity were occurring, based on that we were going to either edit the SPs, either with say try-catch blocks or something else, or just create a report that would email when an SP caused an error above a certain severity.
I've looked at Extended Events (as a result of your post) very briefly, it does seem this could offer us a host of benefits - something I need to dedicate some time to reading and understanding.
Thanks 🙂
June 19, 2012 at 8:44 am
All errors of severity 19 or above go into the error log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply