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

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

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

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

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

> 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/

Thanks Dan

"Dan Guzman" <(E-Mail Removed)> wrote in message
newsBF3351C-C685-443C-9ACD-(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/
>
>