SQL Server stored procedure executes in query analyzer, but does not function properly from C#

Question

I have a stored procedure that updates my database.

It runs great in query analyzer, but when I try to run it from my C# web app, the table is not updated.

I am receiving the following error, so I set ARITHABORT to "ON", but I still receive the error.

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.

C# Code:

p = new SqlParameter("@userAnswers", SqlDbType.VarChar, 1000);
p.Value = "";
p.Value = exam.ExamQuestions.Rows[0].ItemArray[0] + ":" + exam.UserAnswerChoices[0];

for (int x = 1; x < exam.NumQuestions; x++)
{
    p.Value+= ", " + exam.ExamQuestions.Rows[x].ItemArray[0] + ":" + exam.UserAnswerChoices[x];
}

conn.query("insertAnswers", p);
return p.Value.ToString();

Stored procedure code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO
ALTER PROCEDURE [dbo].[insertAnswers] 
@userAnswers VarChar(1000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @input XML
Set @input = '<Questions><Question id="' + Replace(Replace(@userAnswers, ':', '">'), ', ', '</Question><Question id="') + '</Question>' + '</Questions>'

;WITH ParsedXML AS
(
SELECT
    ID = C.value('(@id)[1]', 'int'),
    ColumnName = C.value('(.)[1]', 'varchar(10)')
FROM @input.nodes('/Questions/Question') AS T(C)
)
UPDATE CourseQuestions 
SET a = CASE WHEN p.ColumnName = 'a' THEN t.a + 1 ELSE t.a END,
    b = CASE WHEN p.ColumnName = 'b' THEN t.b + 1 ELSE t.b END,
    c = CASE WHEN p.ColumnName = 'c' THEN t.c + 1 ELSE t.c END,
    d = CASE WHEN p.ColumnName = 'd' THEN t.d + 1 ELSE t.d END,
    e = CASE WHEN p.ColumnName = 'e' THEN t.e + 1 ELSE t.e END,
    f = CASE WHEN p.ColumnName = 'f' THEN t.f + 1 ELSE t.f END
FROM CourseQuestions t
INNER JOIN ParsedXml p ON t.QuestionID = p.ID
END

@userAnswers is basically a comma-separated string (since I can't send an array to SQL Server). It looks like: '1:d, 2:a, 3:b'

can you post your code you are using in your app and the stored procedure code? — Taryn, Jul 18 '12 at 14:25
What is the SP and how do you call it? Edit; @bluefeet beat me to it +1 — Gerald Versluis, Jul 18 '12 at 14:26
I was returning a value before executing the query. I modified my question. I now am getting the Update Failed error. — jpsnow72, Jul 18 '12 at 14:41
If you're going through all the trouble to build a string in C# (without using stringbuilder) and then reparsing it into XML, why not just pass it as XML in the first place? I wrote a blog post about this earlier in 2012 (in VB.NET but you should be able to figure out): http://nycdotnet.blogspot.com/2012/05/one-way-to-insert-many-rows-very-fast.html — NYCdotNet, Dec 15 '12 at 16:14
If you use `SQL Server` please have a look at my answer on [Failed because incorrect arithabort setting](http://stackoverflow.com/questions/35140159/failed-because-incorrect-arithabort-setting/35750269#35750269) page. Hope this helps... — Murat Yıldız, Mar 02 '16 at 14:42

score 0 · Answer 1 · answered Jul 18 '12 at 14:29

0

A few things to check;

Execution/General + Advanced options in Management Studio, ensure that you don't have some strange things set in there. The usual suspects which are on by default are:

QUOTED_IDENTIFIER

ANSI_NULL_DFLT_ON

ANSI_PADDING

ANSI_WARNINGS

ANSI_NULLS

CONCAT_NULL_YIELDS_NULL

ARITHABORT (which you already checked)

Is there a chance you could actually post the Query being executed, otherwise we're flying blind a bit.

answered Jul 18 '12 at 14:29

Mister Bee

69
2
4

+1 @Mister Bee. Any time that a query executes from SSMS but fails from the agent or external code, advanced options are typically the culprit. Compare the database options with the advanced options in SSMS and you'll probably spot the issue. – brian Jul 18 '12 at 14:46
@Mister Bee. - Thanks. This was very useful. I wasn't setting ARITHABORT in the right place and therefore it wasn't changed for this procedure – jpsnow72 Jul 18 '12 at 14:53

score -1 · Answer 2 · answered Jul 18 '12 at 14:40

-1

Wild stab here, but could the C# code be handling text that already has : or ' within it? Or is the concatenated string that you are passing greater than 1000 characters?

On another note you should be using StringBuilder for the concatenations as it provides better performance in most instances, especially when you are doing many concatenations.

answered Jul 18 '12 at 14:40

James Culshaw

1,047
8
19

No. I printed out the string to verify that it looked OK. I will take your advice and use StringBuilder though. – jpsnow72 Jul 18 '12 at 14:49

score -1 · Accepted Answer · answered Jul 18 '12 at 14:45

SET ARITHABORT ON is not within Stored Procedure

ALTER PROCEDURE [dbo].[insertAnswers]  
@userAnswers VarChar(1000) 
AS 
BEGIN 
SET NOCOUNT ON
SET ARITHABORT ON
.....

Haven't tested. If that doesn't work try

strSQL = "SET ARITHABORT ON" & chr(13) & chr(10) & "EXEC MySPRoc ..."
conn.Execute strSQL

SQL Server stored procedure executes in query analyzer, but does not function properly from C#

3 Answers3