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

Document ID : KB000088428
Last Modified Date : 14/04/2018
Show Technical Document Details
Issue:


When an MSSQL stored procedure fails, the error is not shown in the Automic job report/log.
Resolution:


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 -- this fails
print 'failed'

OR
print 'start'
exec sp_test
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