PC Review


Reply
Thread Tools Rating: Thread Rating: 2 votes, 3.00 average.

set ARITHABORT on

 
 
fniles
Guest
Posts: n/a
 
      18th Apr 2010
I have a SQL 2000 database that I just reindex a table.
After I do that, in my program before I can delete/insert I need to "set
ARITHABORT on", otherwise I get an error.
Also, a select statement without it runs very slow.
Why after I re-index a table I need to "set ARITHABORT on" in my program ?
How can I set it so that I don't have to do "set ARITHABORT on" in my
program ?

Thank you

Using Command = New SqlCommand("SET ARITHABORT ON", connection)
Command.CommandType = CommandType.Text
Command.ExecuteNonQuery()
End Using


 
Reply With Quote
 
 
 
 
Uri Dimant
Guest
Posts: n/a
 
      18th Apr 2010
Hi
Dim cmd As adodb.Command

Set cmd = New adodb.Command
With cmd
Set .ActiveConnection = cnn
.CommandType = adodb.CommandTypeEnum.adCmdText
.CommandText = "set arithabort on"
Call .Execute
End With
Set cmd = Nothing

------
You can turn this on as the database level default with
ALTER DATABASE....




"fniles" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have a SQL 2000 database that I just reindex a table.
> After I do that, in my program before I can delete/insert I need to "set
> ARITHABORT on", otherwise I get an error.
> Also, a select statement without it runs very slow.
> Why after I re-index a table I need to "set ARITHABORT on" in my program ?
> How can I set it so that I don't have to do "set ARITHABORT on" in my
> program ?
>
> Thank you
>
> Using Command = New SqlCommand("SET ARITHABORT ON", connection)
> Command.CommandType = CommandType.Text
> Command.ExecuteNonQuery()
> End Using
>
>



 
Reply With Quote
 
 
 
 
Cor Ligthert[MVP]
Guest
Posts: n/a
 
      18th Apr 2010
http://msdn.microsoft.com/en-us/library/aa259212(SQL.80).aspx

"fniles" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I have a SQL 2000 database that I just reindex a table.
> After I do that, in my program before I can delete/insert I need to "set
> ARITHABORT on", otherwise I get an error.
> Also, a select statement without it runs very slow.
> Why after I re-index a table I need to "set ARITHABORT on" in my program ?
> How can I set it so that I don't have to do "set ARITHABORT on" in my
> program ?
>
> Thank you
>
> Using Command = New SqlCommand("SET ARITHABORT ON", connection)
> Command.CommandType = CommandType.Text
> Command.ExecuteNonQuery()
> End Using
>
>
>

 
Reply With Quote
 
fniles
Guest
Posts: n/a
 
      18th Apr 2010
Thank you, everyone.

My question is, why before I reindex the table, I didn't need to add the
"set ARITHABORT on" in my program, but after re-indexing the table, I need
to do that, otherwise I either get an error on insert/update or the query
runs slow on a select statement ?


"Uri Dimant" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi
> Dim cmd As adodb.Command
>
> Set cmd = New adodb.Command
> With cmd
> Set .ActiveConnection = cnn
> .CommandType = adodb.CommandTypeEnum.adCmdText
> .CommandText = "set arithabort on"
> Call .Execute
> End With
> Set cmd = Nothing
>
> ------
> You can turn this on as the database level default with
> ALTER DATABASE....
>
>
>
>
> "fniles" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>I have a SQL 2000 database that I just reindex a table.
>> After I do that, in my program before I can delete/insert I need to "set
>> ARITHABORT on", otherwise I get an error.
>> Also, a select statement without it runs very slow.
>> Why after I re-index a table I need to "set ARITHABORT on" in my program
>> ?
>> How can I set it so that I don't have to do "set ARITHABORT on" in my
>> program ?
>>
>> Thank you
>>
>> Using Command = New SqlCommand("SET ARITHABORT ON", connection)
>> Command.CommandType = CommandType.Text
>> Command.ExecuteNonQuery()
>> End Using
>>
>>

>
>



 
Reply With Quote
 
Dan Guzman
Guest
Posts: n/a
 
      18th Apr 2010
> My question is, why before I reindex the table, I didn't need to add the
> "set ARITHABORT on" in my program, but after re-indexing the table, I need
> to do that, otherwise I either get an error on insert/update or the query
> runs slow on a select statement ?


ARITHABORT ON is required on connections that update tables with indexes on
computed columns or indexed views. I suggest you change the database
default as Uri suggested so that you don't need to change the app code or
procs.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"fniles" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thank you, everyone.
>
> My question is, why before I reindex the table, I didn't need to add the
> "set ARITHABORT on" in my program, but after re-indexing the table, I need
> to do that, otherwise I either get an error on insert/update or the query
> runs slow on a select statement ?
>
>
> "Uri Dimant" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Hi
>> Dim cmd As adodb.Command
>>
>> Set cmd = New adodb.Command
>> With cmd
>> Set .ActiveConnection = cnn
>> .CommandType = adodb.CommandTypeEnum.adCmdText
>> .CommandText = "set arithabort on"
>> Call .Execute
>> End With
>> Set cmd = Nothing
>>
>> ------
>> You can turn this on as the database level default with
>> ALTER DATABASE....
>>
>>
>>
>>
>> "fniles" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>>I have a SQL 2000 database that I just reindex a table.
>>> After I do that, in my program before I can delete/insert I need to "set
>>> ARITHABORT on", otherwise I get an error.
>>> Also, a select statement without it runs very slow.
>>> Why after I re-index a table I need to "set ARITHABORT on" in my program
>>> ?
>>> How can I set it so that I don't have to do "set ARITHABORT on" in my
>>> program ?
>>>
>>> Thank you
>>>
>>> Using Command = New SqlCommand("SET ARITHABORT ON", connection)
>>> Command.CommandType = CommandType.Text
>>> Command.ExecuteNonQuery()
>>> End Using
>>>
>>>

>>
>>

>
>

 
Reply With Quote
 
Dan Guzman
Guest
Posts: n/a
 
      18th Apr 2010
> Dim cmd As adodb.Command

Uri,

Note that the OP is using the ADO.NET/SqlClient rather than classic ADO.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/


 
Reply With Quote
 
Uri Dimant
Guest
Posts: n/a
 
      21st Apr 2010
Thanks Dan

"Dan Guzman" <(E-Mail Removed)> wrote in message
news(E-Mail Removed)...
>> Dim cmd As adodb.Command

>
> Uri,
>
> Note that the OP is using the ADO.NET/SqlClient rather than classic ADO.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SET ARITHABORT ON makes ADO.NET query way faster. WHY? cmay Microsoft ADO .NET 1 14th Jun 2007 04:41 PM
ARITHABORT + IF Statement + Label Causing SqlException craig.wagner@gmail.com Microsoft ADO .NET 0 12th Dec 2006 02:01 AM
SET ARITHABORT ON: Why (not)? Pieter Microsoft VB .NET 4 24th May 2006 02:00 PM
ARITHABORT.... Ollie Riches Microsoft ADO .NET 6 31st Jan 2005 12:31 PM
ARITHABORT error Trevor Lawrence Microsoft ADO .NET 0 18th Oct 2003 05:22 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:47 AM.