ArithAbort ?

[only_me]

I've recently upgraded the server to latest SQL 2000.

Started getting a lot of errors "UPDATE failed because the following SET
options have incorrect settings: 'ARITHABORT'. "

Checked the MSKB, which said we needed to add the following once we opened
the connection
conn.Execute "SET ARITHABORT ON"
so added this in the comon function which opens database thoughout the app

That fixed all the errors bar this one

UPDATE company SET live = 1, SequenceNumber= 1, comments= 'Promoted',
MembershipExpiry = '12/31/2010', MembershipType = 0, PromoteUntil =
'01/01/1900', LastEdit='11/26/2003 11:16:12', LastEditBy=1, SuperEdit= 1,
Promotionlevel = 0 WHERE CompanyID = 11927
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE failed because the
following SET options have incorrect settings: 'ARITHABORT'

It fixed all the rest why not this one ?

[Bob Barrows]

only_me wrote:

> I've recently upgraded the server to latest SQL 2000.
>
> Started getting a lot of errors "UPDATE failed because the following
> SET options have incorrect settings: 'ARITHABORT'. "
>
> Checked the MSKB, which said we needed to add the following once we
> opened the connection
> conn.Execute "SET ARITHABORT ON"
> so added this in the comon function which opens database thoughout
> the app
>
> That fixed all the errors bar this one
>
> UPDATE company SET live = 1, SequenceNumber= 1, comments= 'Promoted',
> MembershipExpiry = '12/31/2010', MembershipType = 0, PromoteUntil =
> '01/01/1900', LastEdit='11/26/2003 11:16:12', LastEditBy=1,
> SuperEdit= 1, Promotionlevel = 0 WHERE CompanyID = 11927
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE failed because
> the following SET options have incorrect settings: 'ARITHABORT'
>
> It fixed all the rest why not this one ?

I've never seen this error. The first thing I will suggest is switching to
the native SQL OLEDB provider. There's no reason to persist in using the
obsolete ODBC driver. ([url]www.able-consulting.com/ado_conn.htm[/url])

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[only_me]

I've changed code to use the one I think you suggested - Is that the one yoy
suggested ?

UPDATE company SET live = 1, SequenceNumber= 1, comments= 'Promoted until
Nov 03 for being helpful', MembershipExpiry = '12/31/2010', MembershipType =
0, PromoteUntil = '11/30/2003', LastEdit='11/26/2003 13:58:19',
LastEditBy=1, SuperEdit= 1, Promotionlevel = 2 WHERE CompanyID = 11927
Microsoft OLE DB Provider for SQL Server error '80040e14'

UPDATE failed because the following SET options have incorrect settings:
'ARITHABORT'.

/companyadmin_action.asp, line 201

Its odd its only that SQL that fails, cant see that it fits the MS critera
documented here


[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_7gx1.asp[/url]

"When an INSERT, DELETE or UPDATE statement encounters an arithmetic error
(overflow, divide-by-zero, or a domain error) "

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:O%23Gj7ZBtDHA.1060@TK2MSFTNGP12.phx.gbl...

> only_me wrote:

> > I've recently upgraded the server to latest SQL 2000.
> >
> > Started getting a lot of errors "UPDATE failed because the following
> > SET options have incorrect settings: 'ARITHABORT'. "
> >
> > Checked the MSKB, which said we needed to add the following once we
> > opened the connection
> > conn.Execute "SET ARITHABORT ON"
> > so added this in the comon function which opens database thoughout
> > the app
> >
> > That fixed all the errors bar this one
> >
> > UPDATE company SET live = 1, SequenceNumber= 1, comments= 'Promoted',
> > MembershipExpiry = '12/31/2010', MembershipType = 0, PromoteUntil =
> > '01/01/1900', LastEdit='11/26/2003 11:16:12', LastEditBy=1,
> > SuperEdit= 1, Promotionlevel = 0 WHERE CompanyID = 11927
> > Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> >
> > [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE failed because
> > the following SET options have incorrect settings: 'ARITHABORT'
> >
> > It fixed all the rest why not this one ?

>
>
> I've never seen this error. The first thing I will suggest is switching to
> the native SQL OLEDB provider. There's no reason to persist in using the
> obsolete ODBC driver. ([url]www.able-consulting.com/ado_conn.htm[/url])
>
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

[Bob Barrows]

Does it fail when run in Query Analyzer?

Bob Barrows

only_me wrote:

> I've changed code to use the one I think you suggested - Is that the
> one yoy
> suggested ?
>
> UPDATE company SET live = 1, SequenceNumber= 1, comments= 'Promoted
> until
> Nov 03 for being helpful', MembershipExpiry = '12/31/2010',
> MembershipType = 0, PromoteUntil = '11/30/2003', LastEdit='11/26/2003
> 13:58:19',
> LastEditBy=1, SuperEdit= 1, Promotionlevel = 2 WHERE CompanyID = 11927
> Microsoft OLE DB Provider for SQL Server error '80040e14'
>
> UPDATE failed because the following SET options have incorrect
> settings: 'ARITHABORT'.
>
> /companyadmin_action.asp, line 201
>
> Its odd its only that SQL that fails, cant see that it fits the MS
> critera
> documented here
>
>
>

[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_7gx1.asp[/url]

>
> "When an INSERT, DELETE or UPDATE statement encounters an arithmetic
> error (overflow, divide-by-zero, or a domain error) "
>
> "Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:O%23Gj7ZBtDHA.1060@TK2MSFTNGP12.phx.gbl...

>> only_me wrote:

>>> I've recently upgraded the server to latest SQL 2000.
>>>
>>> Started getting a lot of errors "UPDATE failed because the following
>>> SET options have incorrect settings: 'ARITHABORT'. "
>>>
>>> Checked the MSKB, which said we needed to add the following once we
>>> opened the connection
>>> conn.Execute "SET ARITHABORT ON"
>>> so added this in the comon function which opens database thoughout
>>> the app
>>>
>>> That fixed all the errors bar this one
>>>
>>> UPDATE company SET live = 1, SequenceNumber= 1, comments=
>>> 'Promoted',
>>> MembershipExpiry = '12/31/2010', MembershipType = 0, PromoteUntil =
>>> '01/01/1900', LastEdit='11/26/2003 11:16:12', LastEditBy=1,
>>> SuperEdit= 1, Promotionlevel = 0 WHERE CompanyID = 11927
>>> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>>>
>>> [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE failed because
>>> the following SET options have incorrect settings: 'ARITHABORT'
>>>
>>> It fixed all the rest why not this one ?

>>
>>
>> I've never seen this error. The first thing I will suggest is
>> switching to
>> the native SQL OLEDB provider. There's no reason to persist in using
>> the
>> obsolete ODBC driver. ([url]www.able-consulting.com/ado_conn.htm[/url])
>>
>> Bob Barrows
>>

[Bob Barrows]

Do you have an indexed view? Or an index on a computed column?

Bob Barrows
only_me wrote:

> I've changed code to use the one I think you suggested - Is that the
> one yoy
> suggested ?
>
> UPDATE company SET live = 1, SequenceNumber= 1, comments= 'Promoted
> until
> Nov 03 for being helpful', MembershipExpiry = '12/31/2010',
> MembershipType = 0, PromoteUntil = '11/30/2003', LastEdit='11/26/2003
> 13:58:19',
> LastEditBy=1, SuperEdit= 1, Promotionlevel = 2 WHERE CompanyID = 11927
> Microsoft OLE DB Provider for SQL Server error '80040e14'
>
> UPDATE failed because the following SET options have incorrect
> settings: 'ARITHABORT'.
>
> /companyadmin_action.asp, line 201
>
> Its odd its only that SQL that fails, cant see that it fits the MS
> critera
> documented here
>
>
>

[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_7gx1.asp[/url]

>
> "When an INSERT, DELETE or UPDATE statement encounters an arithmetic
> error (overflow, divide-by-zero, or a domain error) "
>
> "Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:O%23Gj7ZBtDHA.1060@TK2MSFTNGP12.phx.gbl...

>> only_me wrote:

>>> I've recently upgraded the server to latest SQL 2000.
>>>
>>> Started getting a lot of errors "UPDATE failed because the following
>>> SET options have incorrect settings: 'ARITHABORT'. "
>>>
>>> Checked the MSKB, which said we needed to add the following once we
>>> opened the connection
>>> conn.Execute "SET ARITHABORT ON"
>>> so added this in the comon function which opens database thoughout
>>> the app
>>>
>>> That fixed all the errors bar this one
>>>
>>> UPDATE company SET live = 1, SequenceNumber= 1, comments=
>>> 'Promoted',
>>> MembershipExpiry = '12/31/2010', MembershipType = 0, PromoteUntil =
>>> '01/01/1900', LastEdit='11/26/2003 11:16:12', LastEditBy=1,
>>> SuperEdit= 1, Promotionlevel = 0 WHERE CompanyID = 11927
>>> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>>>
>>> [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE failed because
>>> the following SET options have incorrect settings: 'ARITHABORT'
>>>
>>> It fixed all the rest why not this one ?

>>
>>
>> I've never seen this error. The first thing I will suggest is
>> switching to
>> the native SQL OLEDB provider. There's no reason to persist in using
>> the
>> obsolete ODBC driver. ([url]www.able-consulting.com/ado_conn.htm[/url])
>>
>> Bob Barrows
>>
>> --
>> Microsoft MVP - ASP/ASP.NET
>> Please reply to the newsgroup. This email account is my spam trap so
>> I
>> don't check it very often. If you must reply off-line, then remove
>> the "NO SPAM"

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[only_me]

These are all normal columns, not compound.ones

This is a very simple SQL statement which its failing on, some of the other
updates we do are much more complex on the same table - so I suppose its
something specific to one of these fields, have to check which are indexed
or not

Not using any 'indexed views' that we are aware of

In the mean time ....
Googling around , found this
[url]http://www.databasejournal.com/features/mssql/article.php/10894_2119721_2[/url]

Following the advice around Table 1. I 'SET' all those options as suggested

i.e.
conn.Execute "SET ANSI_NULLS ON"
' conn.Execute "SET ANSI_WARNING ON"
conn.Execute "SET ANSI_PADDING ON"
conn.Execute "SET ARITHABORT ON"
' conn.Execute "SET CONCAT_NULL_YEILDS_NULL ON"
conn.Execute "SET NUMERIC_ROUNDABORT OFF"
conn.Execute "SET QUOTED_IDENTIFIER ON"

It then threw out the commented out ones as ' not valid options' !




"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eKV3H#CtDHA.3536@tk2msftngp13.phx.gbl...

> Do you have an indexed view? Or an index on a computed column?
>
> Bob Barrows
> only_me wrote:

> > I've changed code to use the one I think you suggested - Is that the
> > one yoy
> > suggested ?
> >
> > UPDATE company SET live = 1, SequenceNumber= 1, comments= 'Promoted
> > until
> > Nov 03 for being helpful', MembershipExpiry = '12/31/2010',
> > MembershipType = 0, PromoteUntil = '11/30/2003', LastEdit='11/26/2003
> > 13:58:19',
> > LastEditBy=1, SuperEdit= 1, Promotionlevel = 2 WHERE CompanyID = 11927
> > Microsoft OLE DB Provider for SQL Server error '80040e14'
> >
> > UPDATE failed because the following SET options have incorrect
> > settings: 'ARITHABORT'.
> >
> > /companyadmin_action.asp, line 201
> >
> > Its odd its only that SQL that fails, cant see that it fits the MS
> > critera
> > documented here
> >
> >
> >

>

[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_[/url]
set-set_7gx1.asp

> >
> > "When an INSERT, DELETE or UPDATE statement encounters an arithmetic
> > error (overflow, divide-by-zero, or a domain error) "
> >
> > "Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
> > news:O%23Gj7ZBtDHA.1060@TK2MSFTNGP12.phx.gbl...

> >> only_me wrote:
> >>> I've recently upgraded the server to latest SQL 2000.
> >>>
> >>> Started getting a lot of errors "UPDATE failed because the following
> >>> SET options have incorrect settings: 'ARITHABORT'. "
> >>>
> >>> Checked the MSKB, which said we needed to add the following once we
> >>> opened the connection
> >>> conn.Execute "SET ARITHABORT ON"
> >>> so added this in the comon function which opens database thoughout
> >>> the app
> >>>
> >>> That fixed all the errors bar this one
> >>>
> >>> UPDATE company SET live = 1, SequenceNumber= 1, comments=
> >>> 'Promoted',
> >>> MembershipExpiry = '12/31/2010', MembershipType = 0, PromoteUntil =
> >>> '01/01/1900', LastEdit='11/26/2003 11:16:12', LastEditBy=1,
> >>> SuperEdit= 1, Promotionlevel = 0 WHERE CompanyID = 11927
> >>> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> >>>
> >>> [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE failed because
> >>> the following SET options have incorrect settings: 'ARITHABORT'
> >>>
> >>> It fixed all the rest why not this one ?
> >>
> >>
> >> I've never seen this error. The first thing I will suggest is
> >> switching to
> >> the native SQL OLEDB provider. There's no reason to persist in using
> >> the
> >> obsolete ODBC driver. ([url]www.able-consulting.com/ado_conn.htm[/url])
> >>
> >> Bob Barrows
> >>
> >> --
> >> Microsoft MVP - ASP/ASP.NET
> >> Please reply to the newsgroup. This email account is my spam trap so
> >> I
> >> don't check it very often. If you must reply off-line, then remove
> >> the "NO SPAM"

>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

[only_me]

By removing some of the fields one by one, have narrowed it down to a couple
it doesn't llike so gives us something to look at

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eKV3H#CtDHA.3536@tk2msftngp13.phx.gbl...

> Do you have an indexed view? Or an index on a computed column?
>
> Bob Barrows
> only_me wrote:

> > I've changed code to use the one I think you suggested - Is that the
> > one yoy
> > suggested ?
> >
> > UPDATE company SET live = 1, SequenceNumber= 1, comments= 'Promoted
> > until
> > Nov 03 for being helpful', MembershipExpiry = '12/31/2010',
> > MembershipType = 0, PromoteUntil = '11/30/2003', LastEdit='11/26/2003
> > 13:58:19',
> > LastEditBy=1, SuperEdit= 1, Promotionlevel = 2 WHERE CompanyID = 11927
> > Microsoft OLE DB Provider for SQL Server error '80040e14'
> >
> > UPDATE failed because the following SET options have incorrect
> > settings: 'ARITHABORT'.
> >
> > /companyadmin_action.asp, line 201
> >
> > Its odd its only that SQL that fails, cant see that it fits the MS
> > critera
> > documented here
> >
> >
> >

>

[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_[/url]
set-set_7gx1.asp

> >
> > "When an INSERT, DELETE or UPDATE statement encounters an arithmetic
> > error (overflow, divide-by-zero, or a domain error) "
> >
> > "Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
> > news:O%23Gj7ZBtDHA.1060@TK2MSFTNGP12.phx.gbl...

> >> only_me wrote:
> >>> I've recently upgraded the server to latest SQL 2000.
> >>>
> >>> Started getting a lot of errors "UPDATE failed because the following
> >>> SET options have incorrect settings: 'ARITHABORT'. "
> >>>
> >>> Checked the MSKB, which said we needed to add the following once we
> >>> opened the connection
> >>> conn.Execute "SET ARITHABORT ON"
> >>> so added this in the comon function which opens database thoughout
> >>> the app
> >>>
> >>> That fixed all the errors bar this one
> >>>
> >>> UPDATE company SET live = 1, SequenceNumber= 1, comments=
> >>> 'Promoted',
> >>> MembershipExpiry = '12/31/2010', MembershipType = 0, PromoteUntil =
> >>> '01/01/1900', LastEdit='11/26/2003 11:16:12', LastEditBy=1,
> >>> SuperEdit= 1, Promotionlevel = 0 WHERE CompanyID = 11927
> >>> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> >>>
> >>> [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE failed because
> >>> the following SET options have incorrect settings: 'ARITHABORT'
> >>>
> >>> It fixed all the rest why not this one ?
> >>
> >>
> >> I've never seen this error. The first thing I will suggest is
> >> switching to
> >> the native SQL OLEDB provider. There's no reason to persist in using
> >> the
> >> obsolete ODBC driver. ([url]www.able-consulting.com/ado_conn.htm[/url])
> >>
> >> Bob Barrows
> >>
> >> --
> >> Microsoft MVP - ASP/ASP.NET
> >> Please reply to the newsgroup. This email account is my spam trap so
> >> I
> >> don't check it very often. If you must reply off-line, then remove
> >> the "NO SPAM"

>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

[Bob Barrows]

only_me wrote:

> By removing some of the fields one by one, have narrowed it down to a
> couple it doesn't llike so gives us something to look at
>

If you can provide a repro script (CREATE TABLE ...) we may be able to help.

Bob Barrows



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[only_me]

Ok we are sorted

It seems the Tuning Wizard does create indexed views

Whilst I tried SET'ing the relavent options on/off after I open the
connection - didn't help

A clue here
[url]http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=2249[/url]
suggested that the options MUST be set before the indexes are created

Once we did that seemed to clear most ( heres hoping) of the problems


"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:ue8pGZHtDHA.1872@TK2MSFTNGP09.phx.gbl...

> only_me wrote:

> > By removing some of the fields one by one, have narrowed it down to a
> > couple it doesn't llike so gives us something to look at
> >

> If you can provide a repro script (CREATE TABLE ...) we may be able to

help.

>
> Bob Barrows
>
>
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

[Bob Barrows]

only_me wrote:

> Ok we are sorted
>
> It seems the Tuning Wizard does create indexed views
>
> Whilst I tried SET'ing the relavent options on/off after I open the
> connection - didn't help
>
> A clue here
> [url]http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=2249[/url]
> suggested that the options MUST be set before the indexes are created
>
> Once we did that seemed to clear most ( heres hoping) of the problems
>

Thanks for the feedback. Glad to hear you figured it out.

Bob
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"