Very Computer

Hi,
    am updating a table via a stored procedure called by an adodb.command
object via VB6.
I get the following error description when i call the execute method.

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

I have located the following  knowledge article 305333.

I am using a table with an index on a computed column.
I have tried both calling a sp (set arithabort on ) from the sp which
updates my table i have also tried connection.execute ("set arithabort on")
and am still getting the error

Table def is

CREATE TABLE [dbo].[Users] (
 [UserID] AS (isnull((isnull([storeid],'') + substring('0000',1,(4 -
len(convert(varchar(10),isnull([StoreUserNo],0))))) +
convert(varchar(4),isnull([StoreUserNo],0))),'')) ,
 [StoreUserNo] [smallint] IDENTITY (1, 1) NOT NULL ,
 [StoreID] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [CardNumber] [smallint] NOT NULL ,
 [Forename] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [Surname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [Password] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [SecurityLevel] [tinyint] NOT NULL ,
 [Deleted] [bit] NOT NULL ,
 [Fullname] AS (isnull([forename],'') + ' ' + isnull([surname],''))
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Users] WITH NOCHECK ADD
 CONSTRAINT [PK_Users] PRIMARY KEY  CLUSTERED
 (
  [UserID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Users] ADD
 CONSTRAINT [DF_Users_StoreID] DEFAULT ('isnull(select top 1 StoreID from
parameters),'')') FOR [StoreID],
 CONSTRAINT [DF_User_Password] DEFAULT ('PASSWORD') FOR [Password],
 CONSTRAINT [DF_User_SecurityLevel] DEFAULT (0) FOR [SecurityLevel],
 CONSTRAINT [DF_Users_Deleted] DEFAULT (0) FOR [Deleted]
GO

 CREATE  INDEX [IX_Users_Surname_Forename] ON [dbo].[Users]([Surname],
[Forename]) ON [PRIMARY]
GO

 CREATE  UNIQUE  INDEX [IX_CardNumber] ON [dbo].[Users]([CardNumber]) ON
[PRIMARY]
GO

Can anyone help?

Thanks in advance

Tim B

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.443 / Virus Database: 248 - Release Date: 10/01/2003

Bob Barrows

Quote:>-----Original Message-----
>Hi,
>    am updating a table via a stored procedure called by
an adodb.command
>object via VB6.
>I get the following error description when i call the
execute method.

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

>I have located the following  knowledge article 305333.

>I am using a table with an index on a computed column.
>I have tried both calling a sp (set arithabort on ) from
the sp which
>updates my table i have also tried connection.execute

Quote:>and am still getting the error

>Table def is

>CREATE TABLE [dbo].[Users] (
> [UserID] AS (isnull((isnull([storeid],'') + substring
('0000',1,(4 -
>len(convert(varchar(10),isnull([StoreUserNo],0))))) +
>convert(varchar(4),isnull([StoreUserNo],0))),'')) ,
> [StoreUserNo] [smallint] IDENTITY (1, 1) NOT NULL ,
> [StoreID] [char] (2) COLLATE

Quote:> [CardNumber] [smallint] NOT NULL ,
> [Forename] [varchar] (50) COLLATE

Quote:> [Surname] [varchar] (50) COLLATE

Quote:> [Password] [varchar] (20) COLLATE

Quote:> [SecurityLevel] [tinyint] NOT NULL ,
> [Deleted] [bit] NOT NULL ,
> [Fullname] AS (isnull([forename],'') + ' ' + isnull
([surname],''))
>) ON [PRIMARY]
>GO

>ALTER TABLE [dbo].[Users] WITH NOCHECK ADD
> CONSTRAINT [PK_Users] PRIMARY KEY  CLUSTERED
> (
>  [UserID]
> )  ON [PRIMARY]
>GO

>ALTER TABLE [dbo].[Users] ADD
> CONSTRAINT [DF_Users_StoreID] DEFAULT ('isnull(select
top 1 StoreID from
>parameters),'')') FOR [StoreID],
> CONSTRAINT [DF_User_Password] DEFAULT ('PASSWORD') FOR
[Password],
> CONSTRAINT [DF_User_SecurityLevel] DEFAULT (0) FOR
[SecurityLevel],
> CONSTRAINT [DF_Users_Deleted] DEFAULT (0) FOR [Deleted]
>GO

> CREATE  INDEX [IX_Users_Surname_Forename] ON [dbo].
[Users]([Surname],
>[Forename]) ON [PRIMARY]
>GO

> CREATE  UNIQUE  INDEX [IX_CardNumber] ON [dbo].[Users]
([CardNumber]) ON
>[PRIMARY]
>GO

>Can anyone help?

>Thanks in advance

>Tim B

>---
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.443 / Virus Database: 248 - Release Date:
10/01/2003

>.

You might try a profiler trace to ensure the 'SET ARITHABORT ON' is
being executed on the same connection as the call to the stored
procedure.

Note that you can also turn on ARITHABORT as the database default:

    ALTER DATABASE MyDatabase
        SET ARITHABORT ON
    GO

or server level:

    --assuming only ARITHABORT is to be set
    EXEC sp_configure 'user options', 64
    GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy  Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

Quote:> Hi,
>     am updating a table via a stored procedure called by an
adodb.command
> object via VB6.
> I get the following error description when i call the execute method.

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

> I have located the following  knowledge article 305333.

> I am using a table with an index on a computed column.
> I have tried both calling a sp (set arithabort on ) from the sp which
> updates my table i have also tried connection.execute ("set arithabort
on")
> and am still getting the error

> Table def is

> CREATE TABLE [dbo].[Users] (
>  [UserID] AS (isnull((isnull([storeid],'') + substring('0000',1,(4 -
> len(convert(varchar(10),isnull([StoreUserNo],0))))) +
> convert(varchar(4),isnull([StoreUserNo],0))),'')) ,
>  [StoreUserNo] [smallint] IDENTITY (1, 1) NOT NULL ,
>  [StoreID] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>  [CardNumber] [smallint] NOT NULL ,
>  [Forename] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
>  [Surname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
>  [Password] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
>  [SecurityLevel] [tinyint] NOT NULL ,
>  [Deleted] [bit] NOT NULL ,
>  [Fullname] AS (isnull([forename],'') + ' ' + isnull([surname],''))
> ) ON [PRIMARY]
> GO

> ALTER TABLE [dbo].[Users] WITH NOCHECK ADD
>  CONSTRAINT [PK_Users] PRIMARY KEY  CLUSTERED
>  (
>   [UserID]
>  )  ON [PRIMARY]
> GO

> ALTER TABLE [dbo].[Users] ADD
>  CONSTRAINT [DF_Users_StoreID] DEFAULT ('isnull(select top 1 StoreID
from
> parameters),'')') FOR [StoreID],
>  CONSTRAINT [DF_User_Password] DEFAULT ('PASSWORD') FOR [Password],
>  CONSTRAINT [DF_User_SecurityLevel] DEFAULT (0) FOR [SecurityLevel],
>  CONSTRAINT [DF_Users_Deleted] DEFAULT (0) FOR [Deleted]
> GO

>  CREATE  INDEX [IX_Users_Surname_Forename] ON [dbo].[Users]([Surname],
> [Forename]) ON [PRIMARY]
> GO

>  CREATE  UNIQUE  INDEX [IX_CardNumber] ON [dbo].[Users]([CardNumber])
ON
> [PRIMARY]
> GO

> Can anyone help?

> Thanks in advance

> Tim B

> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.443 / Virus Database: 248 - Release Date: 10/01/2003