Prev: Fast SQL Server Stored Procedure runs slow in VB.NET
Next: How to read a Stream into an XElement
From: fniles on 17 Apr 2010 21:54
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


From: Uri Dimant on 18 Apr 2010 03:17
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" <fniles(a)pfmail.com> wrote in message
news:uRlsSop3KHA.1660(a)TK2MSFTNGP04.phx.gbl...
>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
>
>


From: Cor Ligthert[MVP] on 18 Apr 2010 06:48
http://msdn.microsoft.com/en-us/library/aa259212(SQL.80).aspx

"fniles" <fniles(a)pfmail.com> wrote in message
news:uRlsSop3KHA.1660(a)TK2MSFTNGP04.phx.gbl...
> 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
>
>
>
From: fniles on 18 Apr 2010 11:30
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" <urid(a)iscar.co.il> wrote in message
news:uyKHLcs3KHA.4964(a)TK2MSFTNGP05.phx.gbl...
> 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" <fniles(a)pfmail.com> wrote in message
> news:uRlsSop3KHA.1660(a)TK2MSFTNGP04.phx.gbl...
>>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
>>
>>
>
>


From: Dan Guzman on 18 Apr 2010 13:22
> 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" <fniles(a)pfmail.com> wrote in message
news:e6glOww3KHA.3728(a)TK2MSFTNGP06.phx.gbl...
> 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" <urid(a)iscar.co.il> wrote in message
> news:uyKHLcs3KHA.4964(a)TK2MSFTNGP05.phx.gbl...
>> 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" <fniles(a)pfmail.com> wrote in message
>> news:uRlsSop3KHA.1660(a)TK2MSFTNGP04.phx.gbl...
>>>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
>>>
>>>
>>
>>
>
>
 |  Next  |  Last
Pages: 1 2
Prev: Fast SQL Server Stored Procedure runs slow in VB.NET
Next: How to read a Stream into an XElement