I received the following error while indexing entire
database during optimization time set up by the
Maintenance Plan:
[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'
Any suggestions please?
Thanks.
Sang NahmThe maintenance plan has troubles with certain objects such as computed
columns or indexed views. I suggest you create your own scheduled job to do
the reindexing and then you can set those settings as needed.
--
Andrew J. Kelly
SQL Server MVP
"sang.nahm@.ots.treas.gov" <anonymous@.discussions.microsoft.com> wrote in
message news:095e01c3c8b0$97f28ac0$a501280a@.phx.gbl...
> I received the following error while indexing entire
> database during optimization time set up by the
> Maintenance Plan:
> [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'
> Any suggestions please?
> Thanks.
> Sang Nahm
Showing posts with label sql-dmo. Show all posts
Showing posts with label sql-dmo. Show all posts
Thursday, March 8, 2012
Saturday, February 25, 2012
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:
[vbcol=seagreen]
> Oh, this is a SQL 2000 SP3 running on Windows 2000. Upgrading to SP4 isn't
> planned as of yet.
> "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_indexnonclustered, unique located on PRIMARYdate, PID
PK_indexclustered, unique, primary key located on PRIMARYdate, 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:
[vbcol=seagreen]
> 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:
|||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:
[vbcol=seagreen]
> 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_indexnonclustered, unique located on PRIMARYdate, PID
> PK_indexclustered, unique, primary key located on PRIMARYdate, 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
If the stats are needed then you can re-create them after the reindexing!
John
"Joe" wrote:
[vbcol=seagreen]
> 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:
|||John,
Again. Thanks for all the help.
"John Bell" wrote:
[vbcol=seagreen]
> 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:
[vbcol=seagreen]
> 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:
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:
[vbcol=seagreen]
> Oh, this is a SQL 2000 SP3 running on Windows 2000. Upgrading to SP4 isn't
> planned as of yet.
> "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_indexnonclustered, unique located on PRIMARYdate, PID
PK_indexclustered, unique, primary key located on PRIMARYdate, 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:
[vbcol=seagreen]
> 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:
|||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:
[vbcol=seagreen]
> 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_indexnonclustered, unique located on PRIMARYdate, PID
> PK_indexclustered, unique, primary key located on PRIMARYdate, 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
If the stats are needed then you can re-create them after the reindexing!
John
"Joe" wrote:
[vbcol=seagreen]
> 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:
|||John,
Again. Thanks for all the help.
"John Bell" wrote:
[vbcol=seagreen]
> 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:
[vbcol=seagreen]
> 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:
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]&#
91;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 option
s have
> incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> This used to run fine, but recently stopped working. There are two indexe
s
> 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 hav
e
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:
[vbcol=seagreen]
> Oh, this is a SQL 2000 SP3 running on Windows 2000. Upgrading to SP4 isn'
t
> planned as of yet.
> "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:
[vbcol=seagreen]
> 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 h
ave
> 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:
>|||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:
[vbcol=seagreen]
> 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 suppor
t
> 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
If the stats are needed then you can re-create them after the reindexing!
John
"Joe" wrote:
[vbcol=seagreen]
> 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:
>|||John,
Again. Thanks for all the help.
"John Bell" wrote:
[vbcol=seagreen]
> 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:
[vbcol=seagreen]
> 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:
>
error is:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]&#
91;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 option
s have
> incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> This used to run fine, but recently stopped working. There are two indexe
s
> 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 hav
e
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:
[vbcol=seagreen]
> Oh, this is a SQL 2000 SP3 running on Windows 2000. Upgrading to SP4 isn'
t
> planned as of yet.
> "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:
[vbcol=seagreen]
> 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 h
ave
> 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:
>|||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:
[vbcol=seagreen]
> 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 suppor
t
> 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
If the stats are needed then you can re-create them after the reindexing!
John
"Joe" wrote:
[vbcol=seagreen]
> 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:
>|||John,
Again. Thanks for all the help.
"John Bell" wrote:
[vbcol=seagreen]
> 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:
[vbcol=seagreen]
> 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:
>
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.
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.
Friday, February 24, 2012
DB Maintenance
Does anyone have an idea why I am getting the following error:
[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'.
Thanks.A little more information would be helpful. What specifically are you
trying to do when you get the error?
My initial guess is that you are attempting to do index maintenance on a
table that has computed columns and ARITHABORT or QUOTED_IDENTIFIER is off,
or both...
"NewDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:81C5E626-F4FD-47F1-B693-3F61AFB6B108@.microsoft.com...
> Does anyone have an idea why I am getting the following error:
> [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'.
> Thanks.
>
>|||You'll also see this error when trying to run DBCC CHECKDB against tables
with indexed computed columns unless these SET options are both on.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Don Peterson" <no1@.nunya.com> wrote in message
news:OiZeXP7KEHA.3944@.tk2msftngp13.phx.gbl...
> A little more information would be helpful. What specifically are you
> trying to do when you get the error?
> My initial guess is that you are attempting to do index maintenance on a
> table that has computed columns and ARITHABORT or QUOTED_IDENTIFIER is
off,
> or both...
> "NewDBA" <anonymous@.discussions.microsoft.com> wrote in message
> news:81C5E626-F4FD-47F1-B693-3F61AFB6B108@.microsoft.com...
> > Does anyone have an idea why I am getting the following error:
> >
> > [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'.
> >
> > Thanks.
> >
> >
> >
>
[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'.
Thanks.A little more information would be helpful. What specifically are you
trying to do when you get the error?
My initial guess is that you are attempting to do index maintenance on a
table that has computed columns and ARITHABORT or QUOTED_IDENTIFIER is off,
or both...
"NewDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:81C5E626-F4FD-47F1-B693-3F61AFB6B108@.microsoft.com...
> Does anyone have an idea why I am getting the following error:
> [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'.
> Thanks.
>
>|||You'll also see this error when trying to run DBCC CHECKDB against tables
with indexed computed columns unless these SET options are both on.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Don Peterson" <no1@.nunya.com> wrote in message
news:OiZeXP7KEHA.3944@.tk2msftngp13.phx.gbl...
> A little more information would be helpful. What specifically are you
> trying to do when you get the error?
> My initial guess is that you are attempting to do index maintenance on a
> table that has computed columns and ARITHABORT or QUOTED_IDENTIFIER is
off,
> or both...
> "NewDBA" <anonymous@.discussions.microsoft.com> wrote in message
> news:81C5E626-F4FD-47F1-B693-3F61AFB6B108@.microsoft.com...
> > Does anyone have an idea why I am getting the following error:
> >
> > [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'.
> >
> > Thanks.
> >
> >
> >
>
DB Maintenance
Does anyone have an idea why I am getting the following error:
[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'.
Thanks.
A little more information would be helpful. What specifically are you
trying to do when you get the error?
My initial guess is that you are attempting to do index maintenance on a
table that has computed columns and ARITHABORT or QUOTED_IDENTIFIER is off,
or both...
"NewDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:81C5E626-F4FD-47F1-B693-3F61AFB6B108@.microsoft.com...
> Does anyone have an idea why I am getting the following error:
> [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'.
> Thanks.
>
>
|||You'll also see this error when trying to run DBCC CHECKDB against tables
with indexed computed columns unless these SET options are both on.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Don Peterson" <no1@.nunya.com> wrote in message
news:OiZeXP7KEHA.3944@.tk2msftngp13.phx.gbl...
> A little more information would be helpful. What specifically are you
> trying to do when you get the error?
> My initial guess is that you are attempting to do index maintenance on a
> table that has computed columns and ARITHABORT or QUOTED_IDENTIFIER is
off,
> or both...
> "NewDBA" <anonymous@.discussions.microsoft.com> wrote in message
> news:81C5E626-F4FD-47F1-B693-3F61AFB6B108@.microsoft.com...
>
[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'.
Thanks.
A little more information would be helpful. What specifically are you
trying to do when you get the error?
My initial guess is that you are attempting to do index maintenance on a
table that has computed columns and ARITHABORT or QUOTED_IDENTIFIER is off,
or both...
"NewDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:81C5E626-F4FD-47F1-B693-3F61AFB6B108@.microsoft.com...
> Does anyone have an idea why I am getting the following error:
> [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'.
> Thanks.
>
>
|||You'll also see this error when trying to run DBCC CHECKDB against tables
with indexed computed columns unless these SET options are both on.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Don Peterson" <no1@.nunya.com> wrote in message
news:OiZeXP7KEHA.3944@.tk2msftngp13.phx.gbl...
> A little more information would be helpful. What specifically are you
> trying to do when you get the error?
> My initial guess is that you are attempting to do index maintenance on a
> table that has computed columns and ARITHABORT or QUOTED_IDENTIFIER is
off,
> or both...
> "NewDBA" <anonymous@.discussions.microsoft.com> wrote in message
> news:81C5E626-F4FD-47F1-B693-3F61AFB6B108@.microsoft.com...
>
DB Maintenance
Does anyone have an idea why I am getting the following error:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]&#
91;ODBC
SQL Server Driver][SQL Server]DBCC failed because the following SET
options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
Thanks.A little more information would be helpful. What specifically are you
trying to do when you get the error?
My initial guess is that you are attempting to do index maintenance on a
table that has computed columns and ARITHABORT or QUOTED_IDENTIFIER is off,
or both...
"NewDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:81C5E626-F4FD-47F1-B693-3F61AFB6B108@.microsoft.com...
> Does anyone have an idea why I am getting the following error:
> [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'.
> Thanks.
>
>|||You'll also see this error when trying to run DBCC CHECKDB against tables
with indexed computed columns unless these SET options are both on.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Don Peterson" <no1@.nunya.com> wrote in message
news:OiZeXP7KEHA.3944@.tk2msftngp13.phx.gbl...
> A little more information would be helpful. What specifically are you
> trying to do when you get the error?
> My initial guess is that you are attempting to do index maintenance on a
> table that has computed columns and ARITHABORT or QUOTED_IDENTIFIER is
off,
> or both...
> "NewDBA" <anonymous@.discussions.microsoft.com> wrote in message
> news:81C5E626-F4FD-47F1-B693-3F61AFB6B108@.microsoft.com...
>
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]&#
91;ODBC
SQL Server Driver][SQL Server]DBCC failed because the following SET
options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
Thanks.A little more information would be helpful. What specifically are you
trying to do when you get the error?
My initial guess is that you are attempting to do index maintenance on a
table that has computed columns and ARITHABORT or QUOTED_IDENTIFIER is off,
or both...
"NewDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:81C5E626-F4FD-47F1-B693-3F61AFB6B108@.microsoft.com...
> Does anyone have an idea why I am getting the following error:
> [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'.
> Thanks.
>
>|||You'll also see this error when trying to run DBCC CHECKDB against tables
with indexed computed columns unless these SET options are both on.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Don Peterson" <no1@.nunya.com> wrote in message
news:OiZeXP7KEHA.3944@.tk2msftngp13.phx.gbl...
> A little more information would be helpful. What specifically are you
> trying to do when you get the error?
> My initial guess is that you are attempting to do index maintenance on a
> table that has computed columns and ARITHABORT or QUOTED_IDENTIFIER is
off,
> or both...
> "NewDBA" <anonymous@.discussions.microsoft.com> wrote in message
> news:81C5E626-F4FD-47F1-B693-3F61AFB6B108@.microsoft.com...
>
Subscribe to:
Posts (Atom)