Post reply

SQL Agent halts on divide by zero

  • January 20, 2011 at 3:15 am

    #245564

    Hi Guys,

    I have a stored procedure that retrieves record counts from scalar functions and works out a percentage. It then writes them into a database table.

    DECLARE @fQuotes AS FLOAT

    DECLARE @fSales AS FLOAT

    DECLARE @fConversion AS FLOAT

    SELECT @fQuotes = dbo.funQuotes(GETDATE())

    SELECT @fSales = dbo.funSales(GETDATE())

    SELECT @fConversion = (100 / @fQuotes) * @fSales

    INSERT INTO tblThatTable

    SELECT COALESCE(@fConversion,0), @fSales, @fQuotes

    If I run this in SSMS it sometimes warns that a divide by zero has occurred and assigns NULL to @fConversion.

    I thought to myself during development - oh that's handy I dont have to faff about with loads of "if it's zero" logic. This has resulted in a ~ 8000 line long stored proc with no code in it to handle divide by zero as I thought it would just produce a warning and carry on.

    unfortunately for me the same divide by zero warning results in a failure when run through SQL Agent.

    Is there anything I can do to reduce the severity of divide by zero so that SQL Agent doesn't halt on it? I really really dont want to have to rewrite the 8000 line long sproc. It will take me days...

    Cheers

    Ben

    ^ Thats me!

    ----------------------------------------
    01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
    ----------------------------------------

  • January 20, 2011 at 3:44 am

    #1275764

    Try setting the ARITHABORT / ARITHIGNORE settings

    http://msdn.microsoft.com/en-us/library/aa259212%28v=sql.80%29.aspx


    Clear Sky SQL
    My Blog[/url]

  • January 20, 2011 at 3:53 am

    #1275768

    Ah wonderful thank you Dave.

    Ben

    ^ Thats me!

    ----------------------------------------
    01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
    ----------------------------------------

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply