DB Maintenance Plan error....

I have a Maintenance Plan that keeps failing on an inherited system. The
error is:
[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'.
This used to run fine, but recently stopped working. There are two indexes
the PK_index and IX_index but they are on the same columns. Isn't that
redundant.
I'm not sure what the next step is. I would appreciate any assistance.Oh, this is a SQL 2000 SP3 running on Windows 2000. Upgrading to SP4 isn't
planned as of yet.
"Joe" wrote:
> I have a Maintenance Plan that keeps failing on an inherited system. The
> error is:
> [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'.
> This used to run fine, but recently stopped working. There are two indexes
> the PK_index and IX_index but they are on the same columns. Isn't that
> redundant.
> I'm not sure what the next step is. I would appreciate any assistance.|||Hi Joe
This error usually occurs because there is an index or statistics on a
computed column see http://support.microsoft.com/kb/902388 and
http://support.microsoft.com/kb/902388 if this is the case and you don't have
the SupportComputedColumn option on xp_sqlmaint then you could drop the
index/statistics yourself before running the maintenance task and then
re-create it.
sp_helpindex and sp_helpstatistics will display indexes and statistics on
the table
John
"Joe" wrote:
> Oh, this is a SQL 2000 SP3 running on Windows 2000. Upgrading to SP4 isn't
> planned as of yet.
> "Joe" wrote:
> > I have a Maintenance Plan that keeps failing on an inherited system. The
> > error is:
> >
> > [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'.
> >
> > This used to run fine, but recently stopped working. There are two indexes
> > the PK_index and IX_index but they are on the same columns. Isn't that
> > redundant.
> >
> > I'm not sure what the next step is. I would appreciate any assistance.|||John,
Thanks for the reply. Both links are to the same KB.
The one KB that you reference is for SQL 2000 SP4 only, SP3 doesn't support
the
SupportComputedColumn option.
The indexes are as follows:
IX_index nonclustered, unique located on PRIMARY date, PID
PK_index clustered, unique, primary key located on PRIMARY date, PID
I had tried adding the lines:
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
GO
Prior to kicking off the maintplan but it had no effect. Neither of the
indexed
fields are computed. I'll have to take a closer look at the statistics.
"John Bell" wrote:
> Hi Joe
> This error usually occurs because there is an index or statistics on a
> computed column see http://support.microsoft.com/kb/902388 and
> http://support.microsoft.com/kb/902388 if this is the case and you don't have
> the SupportComputedColumn option on xp_sqlmaint then you could drop the
> index/statistics yourself before running the maintenance task and then
> re-create it.
> sp_helpindex and sp_helpstatistics will display indexes and statistics on
> the table
> John
> "Joe" wrote:
> > Oh, this is a SQL 2000 SP3 running on Windows 2000. Upgrading to SP4 isn't
> > planned as of yet.
> >
> > "Joe" wrote:
> >
> > > I have a Maintenance Plan that keeps failing on an inherited system. The
> > > error is:
> > >
> > > [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'.
> > >
> > > This used to run fine, but recently stopped working. There are two indexes
> > > the PK_index and IX_index but they are on the same columns. Isn't that
> > > redundant.
> > >
> > > I'm not sure what the next step is. I would appreciate any assistance.|||Hi
Sorry about posting the same link twice, I am not sure what I was supposed
to be pasting there possibly http://support.microsoft.com/kb/301292/
You can certainly drop IX_index.
Setting:
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
will have no effect as the process that runs SQLMaint is a separate
connection.
Are there any computed columns in the table, not just the indexed ones?
John
"Joe" wrote:
> John,
> Thanks for the reply. Both links are to the same KB.
> The one KB that you reference is for SQL 2000 SP4 only, SP3 doesn't support
> the
> SupportComputedColumn option.
> The indexes are as follows:
> IX_index nonclustered, unique located on PRIMARY date, PID
> PK_index clustered, unique, primary key located on PRIMARY date, PID
> I had tried adding the lines:
> SET QUOTED_IDENTIFIER ON
> SET ARITHABORT ON
> GO
> Prior to kicking off the maintplan but it had no effect. Neither of the
> indexed
> fields are computed. I'll have to take a closer look at the statistics.
> "John Bell" wrote:
> > Hi Joe
> >
> > This error usually occurs because there is an index or statistics on a
> > computed column see http://support.microsoft.com/kb/902388 and
> > http://support.microsoft.com/kb/902388 if this is the case and you don't have
> > the SupportComputedColumn option on xp_sqlmaint then you could drop the
> > index/statistics yourself before running the maintenance task and then
> > re-create it.
> >
> > sp_helpindex and sp_helpstatistics will display indexes and statistics on
> > the table
> >
> > John
> > "Joe" wrote:
> >
> > > Oh, this is a SQL 2000 SP3 running on Windows 2000. Upgrading to SP4 isn't
> > > planned as of yet.
> > >
> > > "Joe" wrote:
> > >
> > > > I have a Maintenance Plan that keeps failing on an inherited system. The
> > > > error is:
> > > >
> > > > [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'.
> > > >
> > > > This used to run fine, but recently stopped working. There are two indexes
> > > > the PK_index and IX_index but they are on the same columns. Isn't that
> > > > redundant.
> > > >
> > > > I'm not sure what the next step is. I would appreciate any assistance.|||Hi
If the stats are needed then you can re-create them after the reindexing!
John
"Joe" wrote:
> John,
> There is one computed column. It is a Year computed from Start Date.
> That column does a have a statistic related to it. I will have to go back
> to the
> developers to find out if that statistic is needed so I can see if I can
> remove
> it before the reindexing starts.
> Thanks again for all your assistance.
> "John Bell" wrote:
> > Hi
> >
> > Sorry about posting the same link twice, I am not sure what I was supposed
> > to be pasting there possibly http://support.microsoft.com/kb/301292/
> >
> > You can certainly drop IX_index.
> > Setting:
> >
> > SET QUOTED_IDENTIFIER ON
> > SET ARITHABORT ON
> >
> > will have no effect as the process that runs SQLMaint is a separate
> > connection.
> >
> > Are there any computed columns in the table, not just the indexed ones?
> >
> > John
> >
> > "Joe" wrote:
> >
> > > John,
> > >
> > > Thanks for the reply. Both links are to the same KB.
> > >
> > > The one KB that you reference is for SQL 2000 SP4 only, SP3 doesn't support
> > > the
> > > SupportComputedColumn option.
> > >
> > > The indexes are as follows:
> > > IX_index nonclustered, unique located on PRIMARY date, PID
> > > PK_index clustered, unique, primary key located on PRIMARY date, PID
> > >
> > > I had tried adding the lines:
> > > SET QUOTED_IDENTIFIER ON
> > > SET ARITHABORT ON
> > > GO
> > >
> > > Prior to kicking off the maintplan but it had no effect. Neither of the
> > > indexed
> > > fields are computed. I'll have to take a closer look at the statistics.
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi Joe
> > > >
> > > > This error usually occurs because there is an index or statistics on a
> > > > computed column see http://support.microsoft.com/kb/902388 and
> > > > http://support.microsoft.com/kb/902388 if this is the case and you don't have
> > > > the SupportComputedColumn option on xp_sqlmaint then you could drop the
> > > > index/statistics yourself before running the maintenance task and then
> > > > re-create it.
> > > >
> > > > sp_helpindex and sp_helpstatistics will display indexes and statistics on
> > > > the table
> > > >
> > > > John
> > > > "Joe" wrote:
> > > >
> > > > > Oh, this is a SQL 2000 SP3 running on Windows 2000. Upgrading to SP4 isn't
> > > > > planned as of yet.
> > > > >
> > > > > "Joe" wrote:
> > > > >
> > > > > > I have a Maintenance Plan that keeps failing on an inherited system. The
> > > > > > error is:
> > > > > >
> > > > > > [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'.
> > > > > >
> > > > > > This used to run fine, but recently stopped working. There are two indexes
> > > > > > the PK_index and IX_index but they are on the same columns. Isn't that
> > > > > > redundant.
> > > > > >
> > > > > > I'm not sure what the next step is. I would appreciate any assistance.|||John,
Again. Thanks for all the help.
"John Bell" wrote:
> Hi
> If the stats are needed then you can re-create them after the reindexing!
> John
> "Joe" wrote:
> > John,
> >
> > There is one computed column. It is a Year computed from Start Date.
> > That column does a have a statistic related to it. I will have to go back
> > to the
> > developers to find out if that statistic is needed so I can see if I can
> > remove
> > it before the reindexing starts.
> >
> > Thanks again for all your assistance.
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > Sorry about posting the same link twice, I am not sure what I was supposed
> > > to be pasting there possibly http://support.microsoft.com/kb/301292/
> > >
> > > You can certainly drop IX_index.
> > > Setting:
> > >
> > > SET QUOTED_IDENTIFIER ON
> > > SET ARITHABORT ON
> > >
> > > will have no effect as the process that runs SQLMaint is a separate
> > > connection.
> > >
> > > Are there any computed columns in the table, not just the indexed ones?
> > >
> > > John
> > >
> > > "Joe" wrote:
> > >
> > > > John,
> > > >
> > > > Thanks for the reply. Both links are to the same KB.
> > > >
> > > > The one KB that you reference is for SQL 2000 SP4 only, SP3 doesn't support
> > > > the
> > > > SupportComputedColumn option.
> > > >
> > > > The indexes are as follows:
> > > > IX_index nonclustered, unique located on PRIMARY date, PID
> > > > PK_index clustered, unique, primary key located on PRIMARY date, PID
> > > >
> > > > I had tried adding the lines:
> > > > SET QUOTED_IDENTIFIER ON
> > > > SET ARITHABORT ON
> > > > GO
> > > >
> > > > Prior to kicking off the maintplan but it had no effect. Neither of the
> > > > indexed
> > > > fields are computed. I'll have to take a closer look at the statistics.
> > > >
> > > > "John Bell" wrote:
> > > >
> > > > > Hi Joe
> > > > >
> > > > > This error usually occurs because there is an index or statistics on a
> > > > > computed column see http://support.microsoft.com/kb/902388 and
> > > > > http://support.microsoft.com/kb/902388 if this is the case and you don't have
> > > > > the SupportComputedColumn option on xp_sqlmaint then you could drop the
> > > > > index/statistics yourself before running the maintenance task and then
> > > > > re-create it.
> > > > >
> > > > > sp_helpindex and sp_helpstatistics will display indexes and statistics on
> > > > > the table
> > > > >
> > > > > John
> > > > > "Joe" wrote:
> > > > >
> > > > > > Oh, this is a SQL 2000 SP3 running on Windows 2000. Upgrading to SP4 isn't
> > > > > > planned as of yet.
> > > > > >
> > > > > > "Joe" wrote:
> > > > > >
> > > > > > > I have a Maintenance Plan that keeps failing on an inherited system. The
> > > > > > > error is:
> > > > > > >
> > > > > > > [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'.
> > > > > > >
> > > > > > > This used to run fine, but recently stopped working. There are two indexes
> > > > > > > the PK_index and IX_index but they are on the same columns. Isn't that
> > > > > > > redundant.
> > > > > > >
> > > > > > > I'm not sure what the next step is. I would appreciate any assistance.|||John,
There is one computed column. It is a Year computed from Start Date.
That column does a have a statistic related to it. I will have to go back
to the
developers to find out if that statistic is needed so I can see if I can
remove
it before the reindexing starts.
Thanks again for all your assistance.
"John Bell" wrote:
> Hi
> Sorry about posting the same link twice, I am not sure what I was supposed
> to be pasting there possibly http://support.microsoft.com/kb/301292/
> You can certainly drop IX_index.
> Setting:
> SET QUOTED_IDENTIFIER ON
> SET ARITHABORT ON
> will have no effect as the process that runs SQLMaint is a separate
> connection.
> Are there any computed columns in the table, not just the indexed ones?
> John
> "Joe" wrote:
> > John,
> >
> > Thanks for the reply. Both links are to the same KB.
> >
> > The one KB that you reference is for SQL 2000 SP4 only, SP3 doesn't support
> > the
> > SupportComputedColumn option.
> >
> > The indexes are as follows:
> > IX_index nonclustered, unique located on PRIMARY date, PID
> > PK_index clustered, unique, primary key located on PRIMARY date, PID
> >
> > I had tried adding the lines:
> > SET QUOTED_IDENTIFIER ON
> > SET ARITHABORT ON
> > GO
> >
> > Prior to kicking off the maintplan but it had no effect. Neither of the
> > indexed
> > fields are computed. I'll have to take a closer look at the statistics.
> >
> > "John Bell" wrote:
> >
> > > Hi Joe
> > >
> > > This error usually occurs because there is an index or statistics on a
> > > computed column see http://support.microsoft.com/kb/902388 and
> > > http://support.microsoft.com/kb/902388 if this is the case and you don't have
> > > the SupportComputedColumn option on xp_sqlmaint then you could drop the
> > > index/statistics yourself before running the maintenance task and then
> > > re-create it.
> > >
> > > sp_helpindex and sp_helpstatistics will display indexes and statistics on
> > > the table
> > >
> > > John
> > > "Joe" wrote:
> > >
> > > > Oh, this is a SQL 2000 SP3 running on Windows 2000. Upgrading to SP4 isn't
> > > > planned as of yet.
> > > >
> > > > "Joe" wrote:
> > > >
> > > > > I have a Maintenance Plan that keeps failing on an inherited system. The
> > > > > error is:
> > > > >
> > > > > [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'.
> > > > >
> > > > > This used to run fine, but recently stopped working. There are two indexes
> > > > > the PK_index and IX_index but they are on the same columns. Isn't that
> > > > > redundant.
> > > > >
> > > > > I'm not sure what the next step is. I would appreciate any assistance.