Christine_Chavez_6412

How to capture MSSQL Stored procedure errors in the Automic report or error log

Discussion created by Christine_Chavez_6412 on May 11, 2016

You can add a 'print' inside the stored procedure, or in the Automic SQL job itself. However, 'print' will not be processed if this is after a statement that failed.

example:
print 'start'
select * From invalid table ---- select statement that fails
print 'failed'
OR
print 'start'
exec sp_test ----- stored proc that fails
print 'failed'

In both cases, it will print out 'start', but 'print' after the failing statement will not be processed. Job Report will only show 'start'.


You can try something like this on the SQL Job to capture the error, and have the error message display in the Job Report. Once you have the error in the Job Report, you can handle the error in 'Post Process'.

declare @sql varchar(100) = 'sp_test' 
declare @err int 
Begin Try 
exec(@sql) 
End Try 
Begin Catch
set @err = @@ERROR 
if @err <> 0 
print N'ERROR' + str(@err)
print ERROR_MESSAGE()
return
End Catch


Outcomes