QUOTED_IDENTIFIER and ARITHABORT for Maintenance plans

Hi,
I would like to setup a maintenance job to 'Rebuild Indexes' as my
'Optimization Job' keeps failing on "DBCC failed because the following set
options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
What command do I need to use to rebuild all the indexes on all the tables.
I see DBCC DBREINDEX but that needs table name as a parameter. Any kind of
help is greatly appreciated.
Thank you
Take a look at DBCC SHOWCONTIG in BooksOnLine for the sample near the
bottom. This will rebuild the indexes that actually require it given
certain percentage of fragmentation.
Andrew J. Kelly SQL MVP
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:7DB5F397-1671-4F97-9585-AC7C20382FA1@.microsoft.com...
> Hi,
> I would like to setup a maintenance job to 'Rebuild Indexes' as my
> 'Optimization Job' keeps failing on "DBCC failed because the following set
> options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> What command do I need to use to rebuild all the indexes on all the
> tables.
> I see DBCC DBREINDEX but that needs table name as a parameter. Any kind
> of
> help is greatly appreciated.
> Thank you

QUOTED_IDENTIFIER and ARITHABORT for Maintenance plans

Hi,
I would like to setup a maintenance job to 'Rebuild Indexes' as my
'Optimization Job' keeps failing on "DBCC failed because the following set
options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
What command do I need to use to rebuild all the indexes on all the tables.
I see DBCC DBREINDEX but that needs table name as a parameter. Any kind of
help is greatly appreciated.
Thank youTake a look at DBCC SHOWCONTIG in BooksOnLine for the sample near the
bottom. This will rebuild the indexes that actually require it given
certain percentage of fragmentation.
Andrew J. Kelly SQL MVP
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:7DB5F397-1671-4F97-9585-AC7C20382FA1@.microsoft.com...
> Hi,
> I would like to setup a maintenance job to 'Rebuild Indexes' as my
> 'Optimization Job' keeps failing on "DBCC failed because the following set
> options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> What command do I need to use to rebuild all the indexes on all the
> tables.
> I see DBCC DBREINDEX but that needs table name as a parameter. Any kind
> of
> help is greatly appreciated.
> Thank you

QUOTED_IDENTIFIER and ARITHABORT for Maintenance plans

Hi,
I would like to setup a maintenance job to 'Rebuild Indexes' as my
'Optimization Job' keeps failing on "DBCC failed because the following set
options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
What command do I need to use to rebuild all the indexes on all the tables.
I see DBCC DBREINDEX but that needs table name as a parameter. Any kind of
help is greatly appreciated.
Thank youTake a look at DBCC SHOWCONTIG in BooksOnLine for the sample near the
bottom. This will rebuild the indexes that actually require it given
certain percentage of fragmentation.
--
Andrew J. Kelly SQL MVP
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:7DB5F397-1671-4F97-9585-AC7C20382FA1@.microsoft.com...
> Hi,
> I would like to setup a maintenance job to 'Rebuild Indexes' as my
> 'Optimization Job' keeps failing on "DBCC failed because the following set
> options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> What command do I need to use to rebuild all the indexes on all the
> tables.
> I see DBCC DBREINDEX but that needs table name as a parameter. Any kind
> of
> help is greatly appreciated.
> Thank you

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 WalkerMore 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.aspx?scid=kb;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.aspx?scid=kb;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.aspx?scid=kb;en-us;301292
John
"DWalker" wrote:
> "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
>|||=?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.aspx?scid=kb;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:
>> Hi David
>> Check out
>> http://support.microsoft.com/default.aspx?scid=kb;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|||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:
> > Hi David
> >
> > Check out
> > http://support.microsoft.com/default.aspx?scid=kb;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
>|||=?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
news:D4BDB694-0A2E-408D-AA7A-562DBEC42515@.microsoft.com:
> Hi David
> You may want to use the send feedback option at the bottom of the
> article.
> John
>
I'll do that.|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:OsfT#fYMGHA.3460@.TK2MSFTNGP15.phx.gbl:
>> 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.
>
Thanks, Tibor.
Davidsql

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]&#
91;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 WalkerMore 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/defaul...b;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
examnotes <jbellnewsposts@.hotmail.com> wrote in
news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:

> Hi David
> Check out
> http://support.microsoft.com/defaul...b;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/defaul...kb;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
>|||examnotes <jbellnewsposts@.hotmail.com> wrote in
news:D8618655-6673-4DBD-BB77-7E0EE21F75C1@.microsoft.com:

> Hi David
> Check out
> http://support.microsoft.com/defaul...b;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 s
ession 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...[v
bcol=seagreen]
> examnotes <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[/vbcol]|||Hi David
You may want to use the send feedback option at the bottom of the article.
John
"DWalker" wrote:

> examnotes <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
>

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
>

quicker way to create indexes

Hi,

I have a new job. It needs to drop and re-create (by insert) a table
every night. The table contains approximately 3,000,000 (and growing)
records. The insert is fine, runs in 2 minutes. The problem is that
when I create the indexes on the table, it is taking 15-20 minutes.
There is one clustered index and 11 non-clustered. This is a lookup
table that takes many different paremeters, so it really needs the
indexes for the user interface to run efficiently. However, the
database owners aren't keen on a job taking 20 minutes to run every
night.

Any ideas?shelleybobelly (shelleybobelly@.yahoo.com) writes:

Quote:

Originally Posted by

I have a new job. It needs to drop and re-create (by insert) a table
every night. The table contains approximately 3,000,000 (and growing)
records. The insert is fine, runs in 2 minutes. The problem is that
when I create the indexes on the table, it is taking 15-20 minutes.
There is one clustered index and 11 non-clustered. This is a lookup
table that takes many different paremeters, so it really needs the
indexes for the user interface to run efficiently. However, the
database owners aren't keen on a job taking 20 minutes to run every
night.

Without knowing much about the data, it's difficult to tell. But I find it
difficult to believe that data changes that much in a lookup table. Then
again, I would not expect a look-up table to have three million rows.
Anyway, rather than dropping and recreating, maybe it's more effective
to load into staging table, and then update changed rows, insert new
ones, and delete old ones.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Right, I agree. If I run it the other way (just update the big lookup
table from the separate tables where there were changes), it runs much
quicker, only deleting/adding a thousand or so records a day. The
owners of this DB are doing things differently, but I guess I'll just
have to 'educate' them on this one. Then I'll create another job to
re-index this table about once a week or so to keep it from
fragmenting.

BTW, this is for a Legal department that has to look up files that can
be up to 20 years old, but they don't have all the information. That's
why the 3 million records in a lookup table. It is a subset of a HUGE
archive table that contains 255 columns, too.

Thanks Erland, keep up your postings.

Erland Sommarskog wrote:

Quote:

Originally Posted by

shelleybobelly (shelleybobelly@.yahoo.com) writes:

Quote:

Originally Posted by

I have a new job. It needs to drop and re-create (by insert) a table
every night. The table contains approximately 3,000,000 (and growing)
records. The insert is fine, runs in 2 minutes. The problem is that
when I create the indexes on the table, it is taking 15-20 minutes.
There is one clustered index and 11 non-clustered. This is a lookup
table that takes many different paremeters, so it really needs the
indexes for the user interface to run efficiently. However, the
database owners aren't keen on a job taking 20 minutes to run every
night.

>
Without knowing much about the data, it's difficult to tell. But I find it
difficult to believe that data changes that much in a lookup table. Then
again, I would not expect a look-up table to have three million rows.
Anyway, rather than dropping and recreating, maybe it's more effective
to load into staging table, and then update changed rows, insert new
ones, and delete old ones.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Not starting over every night would certainly be the best approach,
but anyway...

One thing you might try is to leave the clustered index on the table,
and order the data by the clustering key before loading. It is
possible that the longer time to load with the index in place will be
more than offset by the time saved in creating the clustered index.

Roy Harvey
Beacon Falls, CT

On 18 Jul 2006 13:58:55 -0700, "shelleybobelly"
<shelleybobelly@.yahoo.comwrote:

Quote:

Originally Posted by

>Hi,
>
>I have a new job. It needs to drop and re-create (by insert) a table
>every night. The table contains approximately 3,000,000 (and growing)
>records. The insert is fine, runs in 2 minutes. The problem is that
>when I create the indexes on the table, it is taking 15-20 minutes.
>There is one clustered index and 11 non-clustered. This is a lookup
>table that takes many different paremeters, so it really needs the
>indexes for the user interface to run efficiently. However, the
>database owners aren't keen on a job taking 20 minutes to run every
>night.
>
>Any ideas?

|||"shelleybobelly" <shelleybobelly@.yahoo.comwrote in message
news:1153260795.628994.213350@.h48g2000cwc.googlegr oups.com...

Quote:

Originally Posted by

Right, I agree. If I run it the other way (just update the big lookup
table from the separate tables where there were changes), it runs much
quicker, only deleting/adding a thousand or so records a day. The
owners of this DB are doing things differently, but I guess I'll just
have to 'educate' them on this one. Then I'll create another job to
re-index this table about once a week or so to keep it from
fragmenting.
>
BTW, this is for a Legal department that has to look up files that can
be up to 20 years old, but they don't have all the information. That's
why the 3 million records in a lookup table. It is a subset of a HUGE
archive table that contains 255 columns, too.
>

To suggestions:

1) move the indexes to an NDF file on a separate set of physical disks.

This may help if you're disk I/O bound at all.

2) May want to consider using full-text indexing for some of this.

Quote:

Originally Posted by

Thanks Erland, keep up your postings.
>
>
Erland Sommarskog wrote:

Quote:

Originally Posted by

shelleybobelly (shelleybobelly@.yahoo.com) writes:

Quote:

Originally Posted by

I have a new job. It needs to drop and re-create (by insert) a table
every night. The table contains approximately 3,000,000 (and growing)
records. The insert is fine, runs in 2 minutes. The problem is that
when I create the indexes on the table, it is taking 15-20 minutes.
There is one clustered index and 11 non-clustered. This is a lookup
table that takes many different paremeters, so it really needs the
indexes for the user interface to run efficiently. However, the
database owners aren't keen on a job taking 20 minutes to run every
night.

Without knowing much about the data, it's difficult to tell. But I find

it

Quote:

Originally Posted by

Quote:

Originally Posted by

difficult to believe that data changes that much in a lookup table. Then
again, I would not expect a look-up table to have three million rows.
Anyway, rather than dropping and recreating, maybe it's more effective
to load into staging table, and then update changed rows, insert new
ones, and delete old ones.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/pr...oads/books.mspx

Quote:

Originally Posted by

Quote:

Originally Posted by

Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

>

|||On 18 Jul 2006 13:58:55 -0700, "shelleybobelly"
<shelleybobelly@.yahoo.comwrote:

Quote:

Originally Posted by

>the database owners aren't keen on a job taking 20 minutes to run
>every night.

Is it automated? Are there users doing regular work at night? If
the answers are "yes" and "no" respectively, then why do they care
how long it runs?

Quick method to determine relative position of the index entry

I have a large table with some indexes.
And I have a value for an indexed field. So I'd like to get the order of the first entry of this value in a specified index.

I'd like to write a stored procedure, which returns such number for given value and index name.

How can I do it?I have found a quik decision!

select count(distinct indexed_col_name)
from Table
where index_col_name < value

Other ideas (with Cursor , for example) are slower.