Quoted identifiers

SQL 2000: I have a DB maintenance plan where one step rebuilds indexes.
I get the following entries in the text log file that the step creates:
Starting maintenance plan 'CDW Backup' on 2/13/2006 1:00:02 AM
[1] Database cdw: Index Rebuild (leaving 5%% free space)...
Rebuilding indexes for table 'Accounts'
Rebuilding indexes for table 'Advisor Names'
Rebuilding indexes for table 'Advisor Rep Nums'
Rebuilding indexes for table 'Asset Master'
Rebuilding indexes for table 'BP Claim Filed'
Rebuilding indexes for table 'Breakpoints'
Rebuilding indexes for table 'BSE Trans Prefixes'
Rebuilding indexes for table 'BSE Trans Prefixes Rev'
Rebuilding indexes for table 'BSE Trans Prefixes Std'
Rebuilding indexes for table 'Core_MM_NFS'
Rebuilding indexes for table 'Core_MM_Pershing'
Rebuilding indexes for table 'Dates'
Rebuilding indexes for table 'Dates_MktOpen'
Rebuilding indexes for table 'DAZL Delete'
Rebuilding indexes for table 'Direct Business Transactions'
[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'.
Well it would be nice if it didn't say "INCORRECT SETTING" but instead
would say that the setting should be ON, or should be OFF. (Quoted
Identifiers is ON in my database, according to Select
databasepropertyex.)
After reading all about quoted identifiers, I didn't think they were
stored by table -- I thought it was a database-wide property setting.
According to BOL, "When a table is created, the QUOTED IDENTIFIER option
is always stored as ON in the table's meta data even if the option is
set to OFF when the table is created."
SO, why would so many tables successfully get reindexed and then one of
them (was it 'Direct Business Transactions' or the next one in line?)
fail?
This doesn't make sense. What direction does Set Quoted Identifiers
need to be in for the reindex step of the DB maintenance plan to
succeed? I wish the actual DBCC command was displayed somewhere in this
log also, that would make debugging easier.
Thanks for any information.
David Walker
More than likely you have a computed column or an indexed view. Both require
certain settings that the MP can't handle. Although I believe this was fixed
in SP4. Otherwise you need to do the reindex in your own custom job with
the proper settings set.
Andrew J. Kelly SQL MVP
"DWalker" <none@.none.com> wrote in message
news:uWrSsgLMGHA.1532@.TK2MSFTNGP12.phx.gbl...
> SQL 2000: I have a DB maintenance plan where one step rebuilds indexes.
> I get the following entries in the text log file that the step creates:
> Starting maintenance plan 'CDW Backup' on 2/13/2006 1:00:02 AM
> [1] Database cdw: Index Rebuild (leaving 5%% free space)...
> Rebuilding indexes for table 'Accounts'
> Rebuilding indexes for table 'Advisor Names'
> Rebuilding indexes for table 'Advisor Rep Nums'
> Rebuilding indexes for table 'Asset Master'
> Rebuilding indexes for table 'BP Claim Filed'
> Rebuilding indexes for table 'Breakpoints'
> Rebuilding indexes for table 'BSE Trans Prefixes'
> Rebuilding indexes for table 'BSE Trans Prefixes Rev'
> Rebuilding indexes for table 'BSE Trans Prefixes Std'
> Rebuilding indexes for table 'Core_MM_NFS'
> Rebuilding indexes for table 'Core_MM_Pershing'
> Rebuilding indexes for table 'Dates'
> Rebuilding indexes for table 'Dates_MktOpen'
> Rebuilding indexes for table 'DAZL Delete'
> Rebuilding indexes for table 'Direct Business Transactions'
> [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'.
> Well it would be nice if it didn't say "INCORRECT SETTING" but instead
> would say that the setting should be ON, or should be OFF. (Quoted
> Identifiers is ON in my database, according to Select
> databasepropertyex.)
> After reading all about quoted identifiers, I didn't think they were
> stored by table -- I thought it was a database-wide property setting.
> According to BOL, "When a table is created, the QUOTED IDENTIFIER option
> is always stored as ON in the table's meta data even if the option is
> set to OFF when the table is created."
> SO, why would so many tables successfully get reindexed and then one of
> them (was it 'Direct Business Transactions' or the next one in line?)
> fail?
> This doesn't make sense. What direction does Set Quoted Identifiers
> need to be in for the reindex step of the DB maintenance plan to
> succeed? I wish the actual DBCC command was displayed somewhere in this
> log also, that would make debugging easier.
> Thanks for any information.
> David Walker
|||Hi David
Check out http://support.microsoft.com/default...;en-us;902388,
the particular table probably failed because it is the first that has the
index on a computed column.
If not you may want to look at SQL profiler to see exactly what statements
are being sent to the database by the maintenance plan.
John
"DWalker" wrote:

> SQL 2000: I have a DB maintenance plan where one step rebuilds indexes.
> I get the following entries in the text log file that the step creates:
> Starting maintenance plan 'CDW Backup' on 2/13/2006 1:00:02 AM
> [1] Database cdw: Index Rebuild (leaving 5%% free space)...
> Rebuilding indexes for table 'Accounts'
> Rebuilding indexes for table 'Advisor Names'
> Rebuilding indexes for table 'Advisor Rep Nums'
> Rebuilding indexes for table 'Asset Master'
> Rebuilding indexes for table 'BP Claim Filed'
> Rebuilding indexes for table 'Breakpoints'
> Rebuilding indexes for table 'BSE Trans Prefixes'
> Rebuilding indexes for table 'BSE Trans Prefixes Rev'
> Rebuilding indexes for table 'BSE Trans Prefixes Std'
> Rebuilding indexes for table 'Core_MM_NFS'
> Rebuilding indexes for table 'Core_MM_Pershing'
> Rebuilding indexes for table 'Dates'
> Rebuilding indexes for table 'Dates_MktOpen'
> Rebuilding indexes for table 'DAZL Delete'
> Rebuilding indexes for table 'Direct Business Transactions'
> [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'.
> Well it would be nice if it didn't say "INCORRECT SETTING" but instead
> would say that the setting should be ON, or should be OFF. (Quoted
> Identifiers is ON in my database, according to Select
> databasepropertyex.)
> After reading all about quoted identifiers, I didn't think they were
> stored by table -- I thought it was a database-wide property setting.
> According to BOL, "When a table is created, the QUOTED IDENTIFIER option
> is always stored as ON in the table's meta data even if the option is
> set to OFF when the table is created."
> SO, why would so many tables successfully get reindexed and then one of
> them (was it 'Direct Business Transactions' or the next one in line?)
> fail?
> This doesn't make sense. What direction does Set Quoted Identifiers
> need to be in for the reindex step of the DB maintenance plan to
> succeed? I wish the actual DBCC command was displayed somewhere in this
> log also, that would make debugging easier.
> Thanks for any information.
> David Walker
>
|||Thanks to you both. I do have SP4. I'll double-check that table for a
computed column; I created it a long time ago.
David
=?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:

> Hi David
> Check out
> http://support.microsoft.com/default...;en-us;902388, the
> particular table probably failed because it is the first that has the
> index on a computed column.
> If not you may want to look at SQL profiler to see exactly what
> statements are being sent to the database by the maintenance plan.
> John
>
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
news:#UsAzvMMGHA.2628@.TK2MSFTNGP15.phx.gbl:

> More than likely you have a computed column or an indexed view. Both
> require certain settings that the MP can't handle. Although I believe
> this was fixed in SP4. Otherwise you need to do the reindex in your
> own custom job with the proper settings set.
>
Ah, so the maintenance plan generator can't maintain any table that has
computed coumns? Interesting.
It also was complaining about other settings that required the database to
be in single-user mode. WTF? It tries to run statements that require
single-user mode? Maybe it ought to try to put the database in single-user
mode, or maybe that's not a good idea. The check-marks in the maintenance
plan generator ought to tell you that single-user mode is required.
My conclusion from this is that the maintenace plan generator is not
suitable for use on databases in the real world. Is that the right
conclusion?
Thanks.
David
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
news:#UsAzvMMGHA.2628@.TK2MSFTNGP15.phx.gbl:

> More than likely you have a computed column or an indexed view. Both
> require certain settings that the MP can't handle. Although I believe
> this was fixed in SP4. Otherwise you need to do the reindex in your
> own custom job with the proper settings set.
>
So yes, the table did have one computed column. The table can't be
maintained with a maintenance plan?
Or the indexes can't ever be rebuilt?
David
|||Hi David
You may want to look at the code in:
http://support.microsoft.com/default...b;en-us;301292
John
"DWalker" wrote:

> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
> news:#UsAzvMMGHA.2628@.TK2MSFTNGP15.phx.gbl:
>
> So yes, the table did have one computed column. The table can't be
> maintained with a maintenance plan?
> Or the indexes can't ever be rebuilt?
> David
>
|||=?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:

> Hi David
> Check out
> http://support.microsoft.com/default...;en-us;902388, the
> particular table probably failed because it is the first that has the
> index on a computed column.
> If not you may want to look at SQL profiler to see exactly what
> statements are being sent to the database by the maintenance plan.
> John
>
That article is sure confusing; it says "These statements require that
the QUOTED_IDENTIFIER SET option is set to ON."
It *IS* set to On in my database. Then it goes on to say, I think, that
the command is built wrong by the maintenance wizard. Why it couldn't
have been fixed in SP4 without requiring me to add the -
SupportcomputedColumn parameter is not explained...
I added the -SupportComputedColumn option to the plan step, and it will
probably fix it, but I'm still a bit confused by the wording in the
article.
Tthanks for the pointer though!
David Walker
|||> That article is sure confusing; it says "These statements require that
> the QUOTED_IDENTIFIER SET option is set to ON."
> It *IS* set to On in my database.
The database setting is essentially useless since it will be overridden by session setting. And most
API's will set this setting, whether the developer is aware of it or not.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"DWalker" <none@.none.com> wrote in message news:%23ZTTnSYMGHA.1532@.TK2MSFTNGP12.phx.gbl...
> =?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
> news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:
>
> That article is sure confusing; it says "These statements require that
> the QUOTED_IDENTIFIER SET option is set to ON."
> It *IS* set to On in my database. Then it goes on to say, I think, that
> the command is built wrong by the maintenance wizard. Why it couldn't
> have been fixed in SP4 without requiring me to add the -
> SupportcomputedColumn parameter is not explained...
> I added the -SupportComputedColumn option to the plan step, and it will
> probably fix it, but I'm still a bit confused by the wording in the
> article.
> Tthanks for the pointer though!
> David Walker
|||Hi David
You may want to use the send feedback option at the bottom of the article.
John
"DWalker" wrote:

> =?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
> news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:
>
> That article is sure confusing; it says "These statements require that
> the QUOTED_IDENTIFIER SET option is set to ON."
> It *IS* set to On in my database. Then it goes on to say, I think, that
> the command is built wrong by the maintenance wizard. Why it couldn't
> have been fixed in SP4 without requiring me to add the -
> SupportcomputedColumn parameter is not explained...
> I added the -SupportComputedColumn option to the plan step, and it will
> probably fix it, but I'm still a bit confused by the wording in the
> article.
> Tthanks for the pointer though!
> David Walker
>