Arithmetic Overflow Not Causing Job to Fail
I have a job. Step 1 of the job looks like this:
declare @sql varchar(8000)
select @sql = 'bcp "exec FOOBAR.dbo.ExportDNCToSpec
exec master..xp_cmdshell @sql
the SP "ExportDNCToSpectrum" is suddenly raising an arithmetic overflow error, however this isn't causing the job to fail. So basically I had no idea for weeks that anything was wrong. I've tried adding Set ANSI_WARNINGS ON in both the SP and the job step but to no avail. What gives. What am i doing wrong.
declare @sql varchar(8000)
select @sql = 'bcp "exec FOOBAR.dbo.ExportDNCToSpec
exec master..xp_cmdshell @sql
the SP "ExportDNCToSpectrum" is suddenly raising an arithmetic overflow error, however this isn't causing the job to fail. So basically I had no idea for weeks that anything was wrong. I've tried adding Set ANSI_WARNINGS ON in both the SP and the job step but to no avail. What gives. What am i doing wrong.
ASKER
i know what you are saying but not clear on how to implement and have the error bubble up and cause the job to fail. could you provide me a simple example of how I would handle the error in the SP given the circumstances I outlined.
thanks
thanks
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
great...thanks for the quick and precise feedback!!!
ASKER
as usual with AngelIII, the feedback was quick and right to the point with links to documentation for each item.
that is a stored procedure, right?
your @sql will only return the LAST error that occured in the procedure, not "any" or the "first".
so, if there are some statement after that failing in the procedure, your procedure returns no error(s) ...
you need to hande errors in your stored procedure