SET ARITHABORT ON - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SET ARITHABORT ON

Author

Topic

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-06-21 : 05:19:44

Hi,

I need to 'SET ARITHABORT ON' for a stored procedure so that I can update a table with an index on a calculated column.

However, putting this statement inside the stored procedure appears to have no effect.

I tried putting it with the other 'SET' statements that Query Analyzer shows above a sp when you edit one and it still appeared to have no effect.

I think you need to set ARITHABORT in a different batch but if you put 'GO' after the set statement within a sp, all the sp's variables are suddenly undefined.

How do I do this? And why have they made doing this so tricky?

Cheers,

XFactor.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-21 : 07:55:11

It should take affect on the batch in which it operates so if you put a "set arithabort on" in the sp before the statements you want to affect it should work.

e.g.

create proc a
as
set arithabort on
create table #a (i tinyint, j as i+1)
create index ix on #a (j)
set arithabort off
go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-06-21 : 09:34:50

It doesn't work here when 'set arithabort on' is put within the sp!

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-21 : 11:13:34

If I run a it works whereas b fails.
I seem to recall someone saying that you needed to set settings when you create the SP for it to take effect but that doesn't seem to be the case.

What happens when you run the SPs below?
create proc a
as
set arithabort on
create table #a (i tinyint, j as i+1)
create index ix on #a (j)
set arithabort off
go

create proc b
as
set arithabort off
create table #a (i tinyint, j as i+1)
create index ix on #a (j)
set arithabort off
go

Your comment about batches isn't true. The set command applies to that bacth so e.g. putting the set in dynamic sql won't work as it wil revert when the batch completes - hence this works.

set arithabort on
exec('set arithabort off')
create table #a (i tinyint, j as i+1)
create index ix on #a (j)
drop table #a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-06-22 : 04:11:45

Still no luck.


set arithabort on
create table #a (i tinyint, j as i+1)
create index ix on #a (j)
set arithabort off

insert #a values (8)

drop table #a

This gives the following error...

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

...and so does this...


set arithabort on
create table #a (i tinyint, j as i+1)
create index ix on #a (j)
set arithabort off

set arithabort on

insert #a values (8)

drop table #a

This on the other hand works just fine...


set arithabort on
create table #a (i tinyint, j as i+1)
create index ix on #a (j)
set arithabort off

set arithabort on
go

insert #a values (8)

drop table #a

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-22 : 05:25:39

Looks like a bug.
You can get around it by using dynamic sql but it's not very good.

set arithabort on
exec('insert #a values (8)')

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-06-22 : 05:43:36

Well that's interesting. Though, I think I'll just use a trigger instead of a computed column.

Thanks for your help.

Kristen
Test

22859 Posts

Posted - 2004-06-22 : 08:47:27

quote:
Originally posted by X-Factor

Well that's interesting. Though, I think I'll just use a trigger instead of a computed column.

If the data storage requiremetns are not too onerous I would recommend that - I've had all sorts of side effects using Computed Columns which ahve taken me an age to learn about & work around. It would be OK if we had gazillions of computed columns, but we've got 2 - one is the Quantity * Price and the other is Special Price, if not NULL, otherwise Normal price. A column would have been WAY better - probably still will be!

Kristen

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-22 : 22:58:39

Same thing happened to me. Worse yet the application that used to update the table failed after adding the computed column because it did not have ARITHABORT ON.

It is possible to run sp_dboption set arithabort on (or something similar) to make that the db default, but I hesitated to do it, not knowing what else it might break.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-22 : 23:33:05

You can do that in Query Analyzer kselvia. If you select the option there, it applies to SQL Server. X-Factor, you will need to recreate your table with the SET ARITHABORT ON option when you create the table. Your procedure should then work.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-06-23 : 04:21:40

Hi there,

quote:
you will need to recreate your table with the SET ARITHABORT ON option when you create the table. Your procedure should then work.

But one of the examples that I posted was doing this and it still doesn't work.

set arithabort on
create table #a (i tinyint, j as i+1)
create index ix on #a (j)
set arithabort off

set arithabort on

insert #a values (8)

drop table #a

Kristen
Test

22859 Posts

Posted - 2004-07-10 : 03:40:11

I've just fallen over this too ... looks like
http://support.microsoft.com/default.aspx?kbid=305333

I've got belt&braces and some velcro:


SET ARITHABORT ON
GO
CREATE PROCEDURE
...
AS
...
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON	-- Handle the fact that INDEX exists on VIEW
...

and its been fine calling this SProc from IIS, but I'm now trying to call it from WHScript and I'm getting the

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

message.

So I changed the WHScript to:


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

and it runs fine.

Note that doing as per the Knowledgebase article ("To resolve this problem, add 'MyConnection.Execute "SET ARITHABORT ON"' to your application after you open the connection to your database") did NOT work, it had to be in the batch itself.

Damn thing! Its all 'coz I've got an index on a VIEW on this table - which in turn is there so I can enforce uniqueness on a column that allows NULLs - which I now wish I had handled via a trigger.

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-10 : 10:45:18

The GO is required after the SET statements. Then you do the CREATE PROCEDURE or TABLE or whatever. We have computed columns. When our developers create any object, they have to create it with all the SET statements first, then they have a GO, then they have a CREATE statement.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-07-11 : 03:47:47

I can't get that to work on this one.

My CREATE SPROC has SET ... GO and then CREATE SPROC. The SPROC has the same SET just after the AS (can't have a GO there too), but it still gets hosed when the application calls it (its just fine from QA) unless I use "SET ... [newline] EXEC SPROC ..." as the command

Kristen

vamsibharathcnv
Starting Member

1 Post

Posted - 2009-04-21 : 16:04:53

You can avoid this by setting your database options.
1. Right click your database
2. Click Properties
3. Click Options on the Left pane
4. Turn "Arithmetic Abort Enabled" to "True"

Execute your application and it should work properly

Vamshi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources