Error 1934 from maintenance plan

I'm getting an error resulting from one of the jobs for my maintenance plan:
Rebuilding indexes for table 'MyTable'
[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'.
Now, I know that there is an issue with indexes on computed columns and the
maintenance wizard. This table has a computed column, but there is no index
on that column (just a primary key on the table). Plus, there are several
other databases with the identical table (structure) that do NOT generate
this error. And the maintenance on this table worked fine until a month
ago. The structure did not change, but there could have been some records
inserted and then deleted.
I'm at a loss as to why this one database throws the error, whereas others
don't. Any ideas? We are SQL 2K SP3.Can you post the output of sp_helpindex and sp_helpstats for the table it's
failing on
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Microsoft Public" <allisonanderson_nospam@.visi.com> wrote in message
news:etIJuoqsDHA.536@.tk2msftngp13.phx.gbl...
I'm getting an error resulting from one of the jobs for my maintenance plan:
Rebuilding indexes for table 'MyTable'
[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'.
Now, I know that there is an issue with indexes on computed columns and the
maintenance wizard. This table has a computed column, but there is no index
on that column (just a primary key on the table). Plus, there are several
other databases with the identical table (structure) that do NOT generate
this error. And the maintenance on this table worked fine until a month
ago. The structure did not change, but there could have been some records
inserted and then deleted.
I'm at a loss as to why this one database throws the error, whereas others
don't. Any ideas? We are SQL 2K SP3.|||sp_helpindex:
PK__GREENSHEETITEM__00AA174D clustered, unique, primary key located on
PRIMARY RECORDID
sp_helpstats:
_WA_Sys_ADJOB_5F492382 ADJOB
_WA_Sys_BINDCOUNT_5F492382 BINDCOUNT
_WA_Sys_DEPARTMENTNUMBER_5F492382 DEPARTMENTNUMBER
_WA_Sys_DESCRIPTION_5F492382 DESCRIPTION
_WA_Sys_GREENSHEETNUMBER_5F492382 GREENSHEETNUMBER
_WA_Sys_ITEMCOUNT_5F492382 ITEMCOUNT
_WA_Sys_ITEMDATE_5F492382 ITEMDATE
_WA_Sys_LINETOTAL_5F492382 LINETOTAL
_WA_Sys_PRICE_5F492382 PRICE
The computed column is LINETOTAL, which is ITEMCOUNT * PRICE
I'm comparing similar output from sp_helpstats to other databases with this
table, and they either have no stats or just one on GREENSHEETNUMBER. I've
only selected a few dbs so far, but could this be the reason for the
difference?
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:uqxhDErsDHA.3236@.tk2msftngp13.phx.gbl...
> Can you post the output of sp_helpindex and sp_helpstats for the table
it's
> failing on
>|||i have the same problem. computed column with no index creates this error for
me too. weird thing is that i can drop the computed column, recreate it and the
maintenance plan optimization job will work. eventually, the error message will
start happening again. i have yet to figure out what is causing the error to
reappear after weeks or months of not appearing.
Microsoft Public wrote:
> I'm getting an error resulting from one of the jobs for my maintenance plan:
> Rebuilding indexes for table 'MyTable'
> [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'.
> Now, I know that there is an issue with indexes on computed columns and the
> maintenance wizard. This table has a computed column, but there is no index
> on that column (just a primary key on the table). Plus, there are several
> other databases with the identical table (structure) that do NOT generate
> this error. And the maintenance on this table worked fine until a month
> ago. The structure did not change, but there could have been some records
> inserted and then deleted.
> I'm at a loss as to why this one database throws the error, whereas others
> don't. Any ideas? We are SQL 2K SP3.|||I was actually thinking it might be caused by hypothetical indexes created
by the Index Tuning Wizard confusing the maintenance plan. If you drop the
_WA_Sys_LINETOTAL_5F492382 LINETOTAL statistics on the computed column, does
that have any effect on the ability to reindex the table ?
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Microsoft Public" <allisonanderson_nospam@.visi.com> wrote in message
news:eFKOyRrsDHA.684@.TK2MSFTNGP09.phx.gbl...
sp_helpindex:
PK__GREENSHEETITEM__00AA174D clustered, unique, primary key located on
PRIMARY RECORDID
sp_helpstats:
_WA_Sys_ADJOB_5F492382 ADJOB
_WA_Sys_BINDCOUNT_5F492382 BINDCOUNT
_WA_Sys_DEPARTMENTNUMBER_5F492382 DEPARTMENTNUMBER
_WA_Sys_DESCRIPTION_5F492382 DESCRIPTION
_WA_Sys_GREENSHEETNUMBER_5F492382 GREENSHEETNUMBER
_WA_Sys_ITEMCOUNT_5F492382 ITEMCOUNT
_WA_Sys_ITEMDATE_5F492382 ITEMDATE
_WA_Sys_LINETOTAL_5F492382 LINETOTAL
_WA_Sys_PRICE_5F492382 PRICE
The computed column is LINETOTAL, which is ITEMCOUNT * PRICE
I'm comparing similar output from sp_helpstats to other databases with this
table, and they either have no stats or just one on GREENSHEETNUMBER. I've
only selected a few dbs so far, but could this be the reason for the
difference?
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:uqxhDErsDHA.3236@.tk2msftngp13.phx.gbl...
> Can you post the output of sp_helpindex and sp_helpstats for the table
it's
> failing on
>|||Yep, that did the trick. Thanks for your help!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23fB%23V6ssDHA.2520@.TK2MSFTNGP10.phx.gbl...
> I was actually thinking it might be caused by hypothetical indexes created
> by the Index Tuning Wizard confusing the maintenance plan. If you drop the
> _WA_Sys_LINETOTAL_5F492382 LINETOTAL statistics on the computed column,
does
> that have any effect on the ability to reindex the table ?
>|||This is because the statistics for the column will be dropped when you drop
the column but be automatically created when you use the column in a
predicate for a query. You could add a job step to the maintenance plan job
to check for the existence of statistics on computed columns and drop them
before the reindex runs. They will be automatically recreated.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ch" <ch@.dontemailme.com> wrote in message
news:3FC2525D.45C3BC3D@.dontemailme.com...
i have the same problem. computed column with no index creates this error
for
me too. weird thing is that i can drop the computed column, recreate it and
the
maintenance plan optimization job will work. eventually, the error message
will
start happening again. i have yet to figure out what is causing the error
to
reappear after weeks or months of not appearing.
Microsoft Public wrote:
> I'm getting an error resulting from one of the jobs for my maintenance
plan:
> Rebuilding indexes for table 'MyTable'
> [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'.
> Now, I know that there is an issue with indexes on computed columns and
the
> maintenance wizard. This table has a computed column, but there is no
index
> on that column (just a primary key on the table). Plus, there are several
> other databases with the identical table (structure) that do NOT generate
> this error. And the maintenance on this table worked fine until a month
> ago. The structure did not change, but there could have been some records
> inserted and then deleted.
> I'm at a loss as to why this one database throws the error, whereas others
> don't. Any ideas? We are SQL 2K SP3.