Trying to Run Stored Procedure, But get ARITHABORT Error

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

 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

Answer 5

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

Answer 6

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

Answer 7

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