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.ExportDNCToSpectrum" queryout c:\inetpub\ftproot\DNC\CoDNCForRiseFS02.csv -c -t, -T -S'+ @@servername

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.
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you will need to
1) catch the error:
2) raise the error at the end:  with a severity higher than 20

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>exec FOOBAR.dbo.ExportDNCToSpectrum
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
davidcahanAuthor Commented:
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.

davidcahanAuthor Commented:
great...thanks for the quick and precise feedback!!!
davidcahanAuthor Commented:
as usual with AngelIII, the feedback was quick and right to the point with links  to documentation for each item.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.