July 17, 2009 at 1:02 pm
Has anyone ever heard of a way to define a trigger that fires when the return code of an SQL command executed by a specific login is not 0? We have a "critical error report" we produce for clients when the application fails executing a query (there are several thousand queries and several hundred tables in the app). It would be very cool to write some event trigger that would fire when a non zero return code is received by the execution of an SQL command (obviously there are several other codes that would need to be ignored - like "you just switched your connection successfully").
Hopefully somebody has done this before... 🙂
July 17, 2009 at 1:29 pm
I don't think a trigger can do this since a trigger can only handle inserts, updates, and deletes, and if one of those fails then the trigger will likely not fire anyway. This does however exclude DDL triggers, but I don't think they are in play.
Instead there is usually a default trace that SQL 2005/2008 have that you might be able to build a process to read in and get what you are looking for. I think that is the best place to start.
CEWII
July 17, 2009 at 2:23 pm
Isn't this something that should be handled in the application, not in SQL Server? The application receives an error message or time out message from SQL Server when there is a problem and then the application handles that error. The handling of the error could log the query and error message and then re-submit it or return an error message to the user.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 19, 2009 at 3:57 pm
Yup the app could do it and should however there are thousands of places in the application where we would have to add code to write out the contents of the error to a log file. Rather than do this, I though I might be able to do this more efficientlyby trpping the error, writing out the log record and then return a non-zero code letting the application continue.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply