locked
SET ARITHABORT ON for the SqlConnection RRS feed

  • Question

  • Hello,

     

    I have the following method that attempts to run a stored procedure from an ASP.NET web page:

     

    private String UpdateAllUserAgreements(Int32 userCurrent)

    {

    String resultStr = "";

    SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["MyConnection"]);

    SqlCommand cmd = new SqlCommand("UpdateAllAgreements", conn);

    cmd.Parameters.Add("@inUserID", SqlDbType.Int).Value = userCurrent;

    SqlParameter p = cmd.Parameters.Add("@outRetMsg", SqlDbType.NVarChar, 4000);

    p.Direction = ParameterDirection.Output;

    cmd.CommandType = CommandType.StoredProcedure;

    try

    {

    conn.Open();

    cmd.ExecuteNonQuery();

    resultStr = p.Value.ToString();

    }

    catch (Exception ex)

    {

    resultStr = ex.Message;

    }

    finally

    {

    if (conn != null)

    conn.Close();

    }

    return resultStr;

    }

     

    I receive the following error when running this from the page, even though I can execute the stored proc from Management Studio with the same parameter values with no errors:

     

    UPDATE failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

     

    My question is what can I add to my .NET code to set arithabort on for the above SqlConnection (without reconfiguring the database, which I am not allowed to do).  It turns out that in my case I am able to solve this by putting the stored proc in a different database that is configured correctly, but I'm curious as to how I would do this at the connection level if moving the stored proc was not an option.

     

    Thanks,

     

    -Dave

    Monday, July 30, 2007 10:05 PM

Answers

  • You can avoid this by setting your database options.
    1. Right click your database
    2. Click Properties
    3. Click Options on the Left pane
    4. Turn "Arithmetic Abort Enabled" to "True"

    Execute your application and it should work properly

    Vamshi
    Tuesday, April 21, 2009 7:58 PM

All replies

  •  

    Hello,

     

    Sorry to bother you.

    I have the same problem and wanted to know if you found a solutions ?

     

    Tuesday, April 22, 2008 10:23 AM
  • Hi Albert,

     

    The work-around I used was to put the stored procedure in another database on the same server (this database was correctly configured to avoid the error).

     

    Hope that helps,

     

    -Dave

     

    • Proposed as answer by VamshiBharath Tuesday, April 21, 2009 7:56 PM
    Tuesday, April 22, 2008 4:46 PM
  • You can avoid this by setting your database options.
    1. Right click your database
    2. Click Properties
    3. Click Options on the Left pane
    4. Turn "Arithmetic Abort Enabled" to "True"

    Execute your application and it should work properly

    Vamshi
    Tuesday, April 21, 2009 7:58 PM
  • Or Set your database compatibility mode to something other than 2000
    Thursday, July 28, 2011 2:20 AM