set ARITHABORT on

Discussion in 'Microsoft VB .NET' started by fniles, Apr 18, 2010.

  1. fniles

    fniles Guest

    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
     
    fniles, Apr 18, 2010
    #1
    1. Advertisements

  2. fniles

    Uri Dimant Guest

    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" <> wrote in message
    news:...
    >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
    >
    >
     
    Uri Dimant, Apr 18, 2010
    #2
    1. Advertisements

  3. http://msdn.microsoft.com/en-us/library/aa259212(SQL.80).aspx

    "fniles" <> wrote in message
    news:...
    > 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
    >
    >
    >
     
    Cor Ligthert[MVP], Apr 18, 2010
    #3
  4. fniles

    fniles Guest

    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" <> wrote in message
    news:...
    > 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" <> wrote in message
    > news:...
    >>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
    >>
    >>

    >
    >
     
    fniles, Apr 18, 2010
    #4
  5. fniles

    Dan Guzman Guest

    > 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" <> wrote in message
    news:...
    > 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" <> wrote in message
    > news:...
    >> 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" <> wrote in message
    >> news:...
    >>>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
    >>>
    >>>

    >>
    >>

    >
    >
     
    Dan Guzman, Apr 18, 2010
    #5
  6. fniles

    Dan Guzman Guest

    > 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/
     
    Dan Guzman, Apr 18, 2010
    #6
  7. fniles

    Uri Dimant Guest

    Thanks Dan

    "Dan Guzman" <> wrote in message
    news:D...
    >> 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/
    >
    >
     
    Uri Dimant, Apr 21, 2010
    #7
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Sean
    Replies:
    2
    Views:
    1,057
    Shahram Khosraviani
    Jul 9, 2003
  2. Jerry
    Replies:
    2
    Views:
    971
    Herfried K. Wagner
    Jul 9, 2003
  3. Adda
    Replies:
    5
    Views:
    187
  4. Martin Widmer
    Replies:
    0
    Views:
    548
    Martin Widmer
    Jan 23, 2006
  5. Pieter

    SET ARITHABORT ON: Why (not)?

    Pieter, May 24, 2006, in forum: Microsoft VB .NET
    Replies:
    4
    Views:
    790
    Uri Dimant
    May 24, 2006
Loading...

Share This Page