I have a SQL agent job setup and in that job there is a step to execute a stored proc. If that stored proc fails then the SQL agent job will display an error message but there is no other information. Something like a stacktrace or at least the stored proc that was running and the line number would be highly useful.
e.g.
If the following stored proc is executed then an error message like "Executed as user: NT AUTHORITY\NETWORK SERVICE. Start [SQLSTATE 01000] (Message 0) Invalid object name 'NonExistentTable'. [SQLSTATE 42S02] (Error 208). The step failed." with no indication where exactly the failure occured.
CREATE PROCEDURE TestSpLogging AS
BEGIN
PRINT 'Start'
SELECT * FROM NonExistentTable
PRINT 'End'
END
What's the best way to expose this information?
Best Answer
Using the approach detailed at http://www.sommarskog.se/error_handling_2005.html seems to be working sufficiently so far. It has only required an update to the top level stored procedure and will output the name of the stored procedure that failed and the line number to SQL agent.
The output error will look like this:
Executed as user: NT AUTHORITY\NETWORK SERVICE. *** [InnerInnerStoredProc2], 5. Errno 208: Invalid object name 'NonExistentTable'. [SQLSTATE 42000] (Error 50000) Start [SQLSTATE 01000] (Error 0). The step failed.
Summary of steps:
Create the following error handler stored procedure:
Wrap the top level stored proc in a try catch as follows