SET options have incorrect settings: 'ARITHABORT'

Iqbal

Hi,

I am getting the following error when I run a stored procedure in
which I am inserting/deleting data from a view that selects from a
remote table.

INSERT failed because the following SET options have incorrect
settings: 'ARITHABORT'

The first statement in the stored procedure is 'set arithabort on'. I
also ran 'set arithabort on' before creating the view.

I read many articles ragarding the setting of arithabort. I set it for
the connection, for the database (alter database) and above all for
the server (sp_configure) but could not get around the above error.

However, when I had made the setting using "sp_configure 'user
options', 64" I was able to run the stored procedure successfully a
couple of times. But, later when I had resest 'user options' to 0 and
then back to 64 just to reproduce the earlier error and confirm that
the error does not come with the 'user options' to 64 setting I was
getting the same error.

Is there any way by which I can overcome the problem?

Thanks,
Iqbal

Jul 20 '05 #1

Subscribe Reply

23478

Erland Sommarskog

[posted and mailed, please reply in news]

Iqbal (iq*********@hotmail.com) writes:

I am getting the following error when I run a stored procedure in
which I am inserting/deleting data from a view that selects from a
remote table.

INSERT failed because the following SET options have incorrect
settings: 'ARITHABORT'

The first statement in the stored procedure is 'set arithabort on'.
That might be too late. The error happens when you invoke the procedure,
and SQL Server tries to create a plan for it. At this point ARITHABORT is
OFF, and thus the plan-building fails.

There are a couple of workarounds. One is:
However, when I had made the setting using "sp_configure 'user
options', 64"

Others:

o ALTER DATABASE db SET ARITHABORT ON

o Issue SET ARITHABORT ON from the client when you connect.

o Move the body of the procedure to an inner procedure, and keep the
current procedure as a wrapper that says SET ARITHABORT ON; EXEC
inner_sp.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #2

Iqbal

Hi,

I was able to run the stored procedure successfully. I had mentioned
that I am inserting/deleting data from a view that selects from a
remote table. It was only when I changed the server settings for both
the local and remote servers to set arithabort on, I was able to run
the stored procedure successfully. I ran the following command on both
the servers:

sp_configure 'user options', 64
reconfigure

I first tried to change the database settings on both the servers to
have arithabort on using the 'alter database' command, but that too
did not remove the error. Only after changing the server settings I
was able to overcome the error.

Thanks for the help.
Iqbal

Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...

[posted and mailed, please reply in news]

Iqbal (iq*********@hotmail.com) writes:
I am getting the following error when I run a stored procedure in
which I am inserting/deleting data from a view that selects from a
remote table.

INSERT failed because the following SET options have incorrect
settings: 'ARITHABORT'

The first statement in the stored procedure is 'set arithabort on'.

That might be too late. The error happens when you invoke the procedure,
and SQL Server tries to create a plan for it. At this point ARITHABORT is
OFF, and thus the plan-building fails.

There are a couple of workarounds. One is:
However, when I had made the setting using "sp_configure 'user
options', 64"

Others:

o ALTER DATABASE db SET ARITHABORT ON

o Issue SET ARITHABORT ON from the client when you connect.

o Move the body of the procedure to an inner procedure, and keep the
current procedure as a wrapper that says SET ARITHABORT ON; EXEC
inner_sp.

Jul 20 '05 #3

Erland Sommarskog

Iqbal (iq*********@hotmail.com) writes:

I was able to run the stored procedure successfully. I had mentioned
that I am inserting/deleting data from a view that selects from a
remote table. It was only when I changed the server settings for both
the local and remote servers to set arithabort on, I was able to run
the stored procedure successfully. I ran the following command on both
the servers:

sp_configure 'user options', 64
reconfigure

I first tried to change the database settings on both the servers to
have arithabort on using the 'alter database' command, but that too
did not remove the error. Only after changing the server settings I
was able to overcome the error.

I would guess that the remote table is in fact an indexed view, or
you are accessing an indexed computed column in that table. To access
indexed views and indexed computed columns, there are a couple of settings
that must be on: ANSI_NULLS, ANSI_WARNINGS, ANSI_PADDING,
CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER and ARITHABORT. All these
settings are also on by default - except for ARITHABORT. For the local
process, there are a couple of ways to set it, but for the remote
connection, it's likely that 'user options' is the only way to go.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #4

Similar topics

10729

incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER'.

by: Matt Rink | last post by:

Getting an "incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER'." error after creating a view. We wanted a composite unique constraint that ignored nulls, so we set up a view using the...

Microsoft SQL Server

6561

Update Statistics failed, incorrect set options

by: Steve Foster | last post by:

I have tried many variations (after reviewing other posts) and can not resolve the following issue: RUNNING SQL MAINTENANCE ---------------------------- SET ARITHABORT ON SET...

Microsoft SQL Server

7011

INSERT failed because of incorrect SET options

by: teddysnips | last post by:

I am trying to insert a row into a table using a stored procedure and I get the following error if I try this from QA: INSERT failed because the following SET options have incorrect settings:...

Microsoft SQL Server

3199

Dynamic proxy settings and HttpConnector30

by: Kenneth Jonsson | last post by:

I have posted this in microsoft.public.dotnet.framework.aspnet.webservices without any response. My problem is with connections from client computers with a dynamic proxy settings in IE to my...

.NET Framework

24960

DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'

by: ilo | last post by:

When I want to delete a data from a table that this tabl has a trigger and this trigger reached another tables to delete the data in cursor I have this messeage: DELETE failed because the...

Microsoft SQL Server

2624

How to save user options and settings to a file, like we used to do with INI files?

by: Richard Lewis Haggard | last post by:

I have an application that can't use the registry to save various user options and application settings. My first thought was to simply use an application configuration file but this approach seems...

C# / C Sharp

1642

Security, Storing Settings (.txt or .ini files) and Reports....together at last

by: Regnab | last post by:

I'm creating a database that will be used independently at different sites (in the same company). Given the fact that there will be inevitable changes down the track, I'm trying to work out the...

Microsoft Access / VBA

17571

How to Change Browser settings dynamically through javascript

by: balakrishnan.dinesh | last post by:

hi frnds I need to change the Browser settings dynamically in Onload event through javascript. Like Goto: Tools->Internet Options -General -Settings -Check for newer Versions of stored pages....

Javascript

1702

No tab/page refresh when internet options internet settings are set to never

by: srinivasyadavkota | last post by:

Hi Experts here, Pls help. first I set the following in IE6 :Under Tools/Internet Options... 1) Temporary Internet file 2) Check for newer versions of...

Javascript

7136

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

7182

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

6906

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

Windows Server

5490

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

Career Advice

4923

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

Microsoft Access / VBA

4611

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...

C# / C Sharp

3110

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The...

Networking - Hardware / Configuration

3106

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

316

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...

General