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 <esquel@sommarskog.se> wrote in message news:<Xns9590EDDB09AEAYazorman@127.0.0.1>...
> [posted and mailed, please reply in news]
>
> Iqbal (iqbalkotwal@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.