maintenance plan

Howdy,
I am trying to determine the best way to set up a
maintenance plan using the SQL 2000 wizard.
The problem I'm having is related to rebuilding an index
on a table that contains a computed column.
When the maintenance job reaches the table with a
computed column the job craters.
From the job log I have this entry:
Rebuilding indexes for
table 'tblTEval_Response_Demographic'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC
failed because the following SET options have incorrect
settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
Script for the table being addressed:
CREATE TABLE [dbo].[tblTEval_Response_Demographic] (
[RowID] [int] IDENTITY (1, 1) NOT NULL ,
[strEvalSubID] AS ('ES' + right([RowID],10)) ,
[intEvalID] [int] NOT NULL ,
[ClassID] [int] NOT NULL ,
[Badge] [int] NOT NULL ,
[dEval_Date] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
select @.@.Version
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec
17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft
Corporation Standard Edition on Windows NT 5.0 (Build
2195: Service Pack 3)
I'm using the simple recovery mode and I have auto update
statistics and auto create statistics checked in the
database properties. Compatibility level is 80. Other
properties are unchecked.
What's the best way of handling this situation?
Thanks,
--Terry
Replace msnd with msn to reply directly.Andrew,
Thanks for the input.
If I'm writing my own maintenance plan what would I do differently to
obviate the error? Would it be as simple as changing the settings
'QUOTED_IDENTIFIER, ARITHABORT' just before addressing the index on
the table in question and resetting them afterward?
--Terry
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:<ueyo$1wfDHA.1712@.TK2MSFTNGP11.phx.gbl>...
> The way to handle it that I know of is to create your own scheduled job that
> rebuilds the index and don't use the maintenance plan for this.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Terry" <ntuser_man@.msdn.com> wrote in message
> news:047c01c37ee2$9937b760$a401280a@.phx.gbl...
> >
> > Howdy,
> >
> > I am trying to determine the best way to set up a
> > maintenance plan using the SQL 2000 wizard.
> >
> > The problem I'm having is related to rebuilding an index
> > on a table that contains a computed column.
> > When the maintenance job reaches the table with a
> > computed column the job craters.
> >
> > From the job log I have this entry:
> >
> > Rebuilding indexes for
> > table 'tblTEval_Response_Demographic'
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
> > [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC
> > failed because the following SET options have incorrect
> > settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> >
> > Script for the table being addressed:
> >
> > CREATE TABLE [dbo].[tblTEval_Response_Demographic] (
> > [RowID] [int] IDENTITY (1, 1) NOT NULL ,
> > [strEvalSubID] AS ('ES' + right([RowID],10)) ,
> > [intEvalID] [int] NOT NULL ,
> > [ClassID] [int] NOT NULL ,
> > [Badge] [int] NOT NULL ,
> > [dEval_Date] [smalldatetime] NOT NULL
> > ) ON [PRIMARY]
> > GO
> >
> > select @.@.Version
> > Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec
> > 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft
> > Corporation Standard Edition on Windows NT 5.0 (Build
> > 2195: Service Pack 3)
> >
> > I'm using the simple recovery mode and I have auto update
> > statistics and auto create statistics checked in the
> > database properties. Compatibility level is 80. Other
> > properties are unchecked.
> >
> > What's the best way of handling this situation?
> >
> > Thanks,
> >
> > --Terry
> >
> > Replace msnd with msn to reply directly.