Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

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.
0
davidcahan
Asked:
davidcahan
  • 3
  • 2
1 Solution
 
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
0
 
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.

thanks
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will need to
1) catch the error: http://msdn.microsoft.com/en-us/library/ms175976.aspx
2) raise the error at the end: http://msdn.microsoft.com/en-us/library/ms178592.aspx  with a severity higher than 20

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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now