How to expose more information about the failure of a stored proc in SQL agent

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: 开发者_JAVA百科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?

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:

CREATE PROCEDURE error_handler_sp AS

DECLARE @errmsg   nvarchar(2048),
        @severity tinyint,
        @state    tinyint,
        @errno    int,
        @proc     sysname,
        @lineno   int

SELECT @errmsg = error_message(), @severity = error_severity(),   -- 10
       @state  = error_state(), @errno = error_number(),
       @proc   = error_procedure(), @lineno = error_line()

IF @errmsg NOT LIKE '***%'                                        -- 11  
BEGIN 
   SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') + 
                    ', ' + ltrim(str(@lineno)) + '. Errno ' + 
                    ltrim(str(@errno)) + ': ' + @errmsg
   RAISERROR(@errmsg, @severity, @state)
END
ELSE
   RAISERROR(@errmsg, @severity, @state)
go

Wrap the top level stored proc in a try catch as follows

BEGIN TRY
   SET NOCOUNT ON
   SET XACT_ABORT ON

EXEC InnerStoredProc1
EXEC InnerStoredProc2

END TRY
BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC error_handler_sp
   RETURN 55555
END CATCH

One way to do this would be add some error handling to the stored procedure. Here is a simple method we use here is something like this

declare
    @Error                 int
   ,@ErrorMsg              varchar(1000)
   ,@StepName              varchar(500)
   ,@ProcedureName         sysname
   ,@dtDateTime            datetime

select @ProcedureName = object_name(@@procid)

begin try
select @StepName = 'Step 01: Select from table
PRINT 'Start'
SELECT * FROM NonExistentTable
PRINT 'End'

end try

begin catch
   select @Error = @@ERROR
   set @ErrorMsg = @ProcedureName + ' Error: ' + @StepName
                                  + ', dbErrorNbr:' + IsNull(convert(varchar(10),@Error),'Null')
   raiserror (@ErrorMsg, 16, 1) with nowait
end catch