Arithmetic Overflow Not Causing Job to Fail

Posted on 2009-12-28
Last Modified: 2012-05-08
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.
Question by:davidcahan
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    >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

    Author Comment

    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.

    LVL 142

    Accepted Solution

    you will need to
    1) catch the error:
    2) raise the error at the end:  with a severity higher than 20


    Author Comment

    great...thanks for the quick and precise feedback!!!

    Author Closing Comment

    as usual with AngelIII, the feedback was quick and right to the point with links  to documentation for each item.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
    Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now