I have a production database that I created a maintenance plan for that includes integrity checks, optimizations, DB Backup and trans log backups. Everything works fine except that the optimization job fails every time. This is the case on our identical test and development databases as well. Specifically when looking at the maintenance plan history, the failure is due to "options not being set for "QUOTED_IDENTIFIERS". I have set this option in each of these databases via both the database options dialog and using the db_options stored proc. The bottom line is that this option is set and the job still fails. I even retired the job with it unset! I am thoroughly stumped! Anyone have any thoughts

PS. These databases do in fact have computed fields in them, hence the need for this option to be set (along with ARITHABORT). Originally, the maintenance plan failure was referencing ARITHABORT to, but after I set this via the sp_dboptions SP, that part of it went away even though the QUOTED_IDENTIFIERS error hangs on doggedly

Thanks

Stephen

When SQL Agent logs in to run the maintenance plans, SQL
Agent uses set options that won't work if you have indexes
on computed columns. You need to pull the dbcc reindex (and
checkdb) out of the maintenance plan and create your own
T-SQL scripts to do this which have the appropriate
settings, e.g.
SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON
DBCC <dbreindex, checkdb, etc>

-Sue

On Thu, 1 Apr 2004 09:06:09 -0800, Stephen Rybacki
<anonymous (AT) discussions (DOT) microsoft.com> wrote:

Sue

Thanks for the response. To the best of my knowledge, I don't have an index set on a computed column. Could this happen because of Full-Text Indexing? Could there be a hidden index I am missing? I am by no means a SQL server guru so pardon me if I am a bit ignorant here..

Stephe

----- Sue Hoegemeier wrote: ----

When SQL Agent logs in to run the maintenance plans, SQ
Agent uses set options that won't work if you have indexe
on computed columns. You need to pull the dbcc reindex (an
checkdb) out of the maintenance plan and create your ow
T-SQL scripts to do this which have the appropriat
settings, e.g
SET ARITHABORT O
SET QUOTED_IDENTIFIER O
DBCC <dbreindex, checkdb, etc

-Su

On Thu, 1 Apr 2004 09:06:09 -0800, Stephen Ryback
<anonymous (AT) discussions (DOT) microsoft.com> wrote

Stephen,
There are probably statistics on the column. You will hit
the same issue in such a case.
It's best to just write your own job to handle the
reindexing and checkdbs for the databases that have computed
columns (or indexed views).

-Sue

On Thu, 1 Apr 2004 11:06:11 -0800, Stephen Rybacki
<anonymous (AT) discussions (DOT) microsoft.com> wrote: