DELETE Statement

Hi folks,
I am running the following statement on SQL Server 2000 Database:
DELETE from tablea
WHERE effective_date >=CONVERT(varchar(11),GETDATE()-3,121)
AND effective_date < CONVERT(varchar(10),GETDATE(),120)
tablea has effective_date defined as smalldatetime.
When i run the sql, i get the following error:
DELETE failed because the following SET options have incorrect settings:
'ARITHABORT'
The effective_date column is not a computed column. Any idea as to why the
statement should genrate the error.Can you post the table DDL, including indexes?. Is tablea an indexed view or
does tablea have an index on a computed column?

> WHERE effective_date >=CONVERT(varchar(11),GETDATE()-3,121)
> AND effective_date < CONVERT(varchar(10),GETDATE(),120)
effective_date >= CONVERT(char(8), dateadd(day, -3, GETDATE()), 112)
effective_date < CONVERT(char(8), GETDATE(), 112)
AMB
"Ram" wrote:

> Hi folks,
> I am running the following statement on SQL Server 2000 Database:
> DELETE from tablea
> WHERE effective_date >=CONVERT(varchar(11),GETDATE()-3,121)
> AND effective_date < CONVERT(varchar(10),GETDATE(),120)
> tablea has effective_date defined as smalldatetime.
> When i run the sql, i get the following error:
> DELETE failed because the following SET options have incorrect settings:
> 'ARITHABORT'
> The effective_date column is not a computed column. Any idea as to why the
> statement should genrate the error.|||Is there a calculated column or an indexed view on the table you are
attempting to delete from?
Andrew J. Kelly SQL MVP
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:4EF71394-62B6-49C0-8BF6-3FA849F22770@.microsoft.com...
> Hi folks,
> I am running the following statement on SQL Server 2000 Database:
> DELETE from tablea
> WHERE effective_date >=CONVERT(varchar(11),GETDATE()-3,121)
> AND effective_date < CONVERT(varchar(10),GETDATE(),120)
> tablea has effective_date defined as smalldatetime.
> When i run the sql, i get the following error:
> DELETE failed because the following SET options have incorrect settings:
> 'ARITHABORT'
> The effective_date column is not a computed column. Any idea as to why the
> statement should genrate the error.|||Hi,
Here is the DDL:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tablea]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tablea]
GO
CREATE TABLE [dbo].[tablea] (
[row_id] [int] IDENTITY (1, 1) NOT NULL ,
[iex_id] [int] NOT NULL ,
[mu_id] [int] NOT NULL ,
[effective_date] [smalldatetime] NOT NULL ,
[start_time] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[end_time] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[update_date] [smalldatetime] NULL
) ON [PRIMARY]
GO
Indexes:
IDX_mu_id mu_id
IDX_mu_id_agent_id mu_id, iex_id
PK_tblASAgentSchedule iex_id, mu_id, effective_date
Ram
"Alejandro Mesa" wrote:
> Can you post the table DDL, including indexes?. Is tablea an indexed view
or
> does tablea have an index on a computed column?
>
> effective_date >= CONVERT(char(8), dateadd(day, -3, GETDATE()), 112)
> effective_date < CONVERT(char(8), GETDATE(), 112)
>
> AMB
> "Ram" wrote:
>|||I can not reproduce the error.
CREATE TABLE [dbo].[tablea] (
[row_id] [int] IDENTITY (1, 1) NOT NULL ,
[iex_id] [int] NOT NULL ,
[mu_id] [int] NOT NULL ,
[effective_date] [smalldatetime] NOT NULL ,
[start_time] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[end_time] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[update_date] [smalldatetime] NULL,
constraint PK_tblASAgentSchedule primary key (iex_id, mu_id, effective_date)
) ON [PRIMARY]
GO
DELETE from tablea
WHERE effective_date >=CONVERT(varchar(11),GETDATE()-3,121)
AND effective_date < CONVERT(varchar(10),GETDATE(),120)
go
drop table tablea
go
AMB
"Ram" wrote:
> Hi,
> Here is the DDL:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tablea]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[tablea]
> GO
> CREATE TABLE [dbo].[tablea] (
> [row_id] [int] IDENTITY (1, 1) NOT NULL ,
> [iex_id] [int] NOT NULL ,
> [mu_id] [int] NOT NULL ,
> [effective_date] [smalldatetime] NOT NULL ,
> [start_time] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [end_time] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [update_date] [smalldatetime] NULL
> ) ON [PRIMARY]
> GO
> Indexes:
> IDX_mu_id mu_id
> IDX_mu_id_agent_id mu_id, iex_id
> PK_tblASAgentSchedule iex_id, mu_id, effective_date
> Ram
> "Alejandro Mesa" wrote:
>|||The whole design looks wrong. A SQL programmer does not use IDENTITY
for keys, only for display in cursors. Times are not stored in CHAR(n),
but in temporal data types which they do not have to CONVERT() in weird
ways. Audit information is not stored in the tables, but externally
and best done with log tools.
Having something go into effect is a status change, so where is the
status code? You have two identifiers that are not in this table, so
where is the DRI to the base tables that define them? My guess is that
it should look like this:
CREATE TABLE FoobarHistory
(iex_id INTEGER NOT NULL
REFERENCES IEX(iex_id),
mu_id INTEGER NOT NULL
REFERENCES MU(mu_id),
status_code INTEGER NOT NULL, -- let 0 be effective
start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_time DATETIME, -- null means current
PRIMARY KEY (iex_id, mu_id));
Then you can write:
DELETE FROM Foobar
WHERE status = 0;|||Hi,
I dropped and recreated the index on the table.
The delete statement worked fine after that.
Thank you
"Andrew J. Kelly" wrote:

> Is there a calculated column or an indexed view on the table you are
> attempting to delete from?
> --
> Andrew J. Kelly SQL MVP
>
> "Ram" <Ram@.discussions.microsoft.com> wrote in message
> news:4EF71394-62B6-49C0-8BF6-3FA849F22770@.microsoft.com...
>
>