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

[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

[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.

[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