set ARITHABORT on
in [Programming SQL]
|
Prev: Fast SQL Server Stored Procedure runs slow in VB.NET
Next: Conversion failed when converting datetime from character string
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 >>> >>> >> >> > > |