none
Trying to Run Stored Procedure, But get ARITHABORT Error RRS feed

  • Question

  • Hi,

       I have stored procedure in MS SQL 2005, when I execute stored procedure from SQL 2005 it successfully enters record, but when I run the following code from C# it give me this error

     

    Could anyone help?

    Error is:-
    INSERT 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.

    Code is:-

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Data;
    using System.Data.SqlClient;
    using System.Xml;
    
    
    namespace Run_StoredProcdure
    {
        class Program
        {
            static void Main(string[] args)
            {
                
                String sConnectionstring;
                sConnectionstring = "Initial Catalog=Pubs;Integrated Security=true;Data Source=DCC2516";
             
                SqlConnection Conn = new SqlConnection(sConnectionstring);
                SqlCommand cmd = new SqlCommand();
               // Int32 rowsAffected;
    
                cmd.CommandText = "dbo.xmltext_import";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = Conn;
                Conn.Open();
               //Console.Write ("Connection is open");
                //rowsAffected = 
                cmd.ExecuteNonQuery();
                Conn.Close();
            }
        }
    }
    


    Bedroom Furniture
    • Moved by nobugz Tuesday, October 13, 2009 11:32 AM (From:Visual C# General)
    Tuesday, October 13, 2009 11:25 AM

Answers

  • I'm not a c# expert by any means, but try the following:

               String sConnectionstring;
                sConnectionstring = "Initial Catalog=Pubs;Integrated Security=true;Data Source=DCC2516";
             
                SqlConnection Conn = new SqlConnection(sConnectionstring);
    
    		SqlCommand blah = new SqlCommand("SET ARITHABORT ON", Conn);
    		blah.ExecuteNonQuery();
    
    
                SqlCommand cmd = new SqlCommand();
               // Int32 rowsAffected;
    
                cmd.CommandText = "dbo.xmltext_import";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = Conn;
                Conn.Open();
               //Console.Write ("Connection is open");
                //rowsAffected = 
                cmd.ExecuteNonQuery();
                Conn.Close();

    every day is a school day
    • Marked as answer by Learn4Develop Thursday, October 15, 2009 10:19 AM
    Tuesday, October 13, 2009 1:31 PM
    Moderator

All replies

  • When you run the query from SSMS, then your connection properties must have ARITHABORT ON but your connection from c# has it OFF. You'll need to execute SET ARITHABORT ON before you call your stored procedure.
    every day is a school day
    Tuesday, October 13, 2009 12:15 PM
    Moderator
  • I have tried the following and also many other but still getting the same error.
    i have done this, but no success

    SqlConnection Conn = new SqlConnection(sConnectionstring);
                SqlCommand cmd = new SqlCommand("SET ARITHABORT ON", Conn);
                //Int32 rowsAffected;
    
                cmd.CommandText = "[dbo].[xmltext_import]";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = Conn;
                Conn.Open();
               //Console.Write ("Connection is open");
               //rowsAffected = 
               
                cmd.ExecuteNonQuery();
                Conn.Close();

    Bedroom Furniture
    Tuesday, October 13, 2009 1:19 PM
  • I'm not a c# expert by any means, but try the following:

               String sConnectionstring;
                sConnectionstring = "Initial Catalog=Pubs;Integrated Security=true;Data Source=DCC2516";
             
                SqlConnection Conn = new SqlConnection(sConnectionstring);
    
    		SqlCommand blah = new SqlCommand("SET ARITHABORT ON", Conn);
    		blah.ExecuteNonQuery();
    
    
                SqlCommand cmd = new SqlCommand();
               // Int32 rowsAffected;
    
                cmd.CommandText = "dbo.xmltext_import";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = Conn;
                Conn.Open();
               //Console.Write ("Connection is open");
                //rowsAffected = 
                cmd.ExecuteNonQuery();
                Conn.Close();

    every day is a school day
    • Marked as answer by Learn4Develop Thursday, October 15, 2009 10:19 AM
    Tuesday, October 13, 2009 1:31 PM
    Moderator
  •  You'll need to execute SET ARITHABORT ON before you call your stored procedure.
    You can also set it in the stored procedure:

    -- T-SQL CREATE Stored Procedure 
    CREATE PROC uspListProducts 
    AS 
      SET ARITHABORT  ON 
      SET NOCOUNT ON 
       
      SELECT ProductName=Name, ProductNumber, ListPrice, Color, ProductID
      FROM   AdventureWorks2008.Production.Product 
      WHERE ListPrice > 0.0
      ORDER BY ProductName ASC
    
    GO 
    
    EXEC uspListProducts  
    
    GO 

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Tuesday, October 13, 2009 1:36 PM
    Moderator
  • Hi,

       Thanks for the above solution.

    I have another problem. I have multiple files and I need to run this stored procedure every hour to pick the data from file and insert into table.

     

    I want to define the file name dynamically, like when I run the above code, it should ask the file name (including path) and pass that file name to stored procedure and run the stored procedure.

     

    I have very limited knowledge and experience, could you or anyone me help how I can achieve this task.

     

    Thanks.


    Bedroom Furniture
    Thursday, October 15, 2009 10:34 AM
  • Can you open a new topic for the new question for everybody's benefit?

    Thanks.
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Thursday, October 15, 2009 10:46 AM
    Moderator
  • Can you open a new topic for the new question for everybody's benefit?

    Thanks.
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

    See this please.

    http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/7d4162ad-338a-49e1-ab3f-41184f9dbafe
    Bedroom Furniture
    Thursday, October 15, 2009 1:56 PM