How to change QUOTED_IDENTIFIER for table?

Hi,
I want to change QUOTED_IDENTIFIER setting for table because it makes
problem with DBCC DBREINDEX. I don't want to drop and recreate table because
it is huge. How can I change it?
I use SS2000, SP4, Win 2000 Advance, SP4.
Thanks in advance
Nikola MilicSyntax
SET QUOTED_IDENTIFIER { ON | OFF }
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Nikola Milic" wrote:

> Hi,
> I want to change QUOTED_IDENTIFIER setting for table because it makes
> problem with DBCC DBREINDEX. I don't want to drop and recreate table becau
se
> it is huge. How can I change it?
> I use SS2000, SP4, Win 2000 Advance, SP4.
> Thanks in advance
> Nikola Milic
>
>|||Hi,
You didn't understand me. Table was created with SET QUOTED_IDENTIFIER OFF
and I want to change it to be ON.
Thanks for reply
Nikola
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:1A122A1B-1631-42D2-8CB7-5E2BF2876E69@.microsoft.com...
> Syntax
> SET QUOTED_IDENTIFIER { ON | OFF }
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and
> time
> asking back if its 2000 or 2005]
>
> "Nikola Milic" wrote:
>|||On Thu, 9 Mar 2006 08:36:05 +0200, Nikola Milic wrote:

>Hi,
>You didn't understand me. Table was created with SET QUOTED_IDENTIFIER OFF
>and I want to change it to be ON.
Hi Nikola,
As far as I know, there's no easy way to do this. You'll have to
1. Drop all constraints referencing the table
2. Drop all triggers on the table
3. Rename the table
4. Create a new table with the required setting and the same structure
5. Use INSERT INTO NewTable (Col1, Col2, ...) SELECT Col1, Col2, ...
FROM OldTable to move over all existing data
6. Recreate all triggers you dropped in step 2
7. Recreate all constraints you dropped in step 1
And finally (but you could postpone this a while, to give you some other
fallback scneario options)
8. Drop the "old" table
Hugo Kornelis, SQL Server MVP|||Thanks for reply,
I said in my first post that I don't want to drop and recreate table
because it is huge.
I will avoid problem with DBCC DBREINDEX by dropping calculated column (it
makes problem) and recreating it after re-indexing.
Regards
Nikol
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:tjd112lpcn6bo75hfct2vhvhio6anj6sdc@.
4ax.com...
> On Thu, 9 Mar 2006 08:36:05 +0200, Nikola Milic wrote:
>
> Hi Nikola,
> As far as I know, there's no easy way to do this. You'll have to
> 1. Drop all constraints referencing the table
> 2. Drop all triggers on the table
> 3. Rename the table
> 4. Create a new table with the required setting and the same structure
> 5. Use INSERT INTO NewTable (Col1, Col2, ...) SELECT Col1, Col2, ...
> FROM OldTable to move over all existing data
> 6. Recreate all triggers you dropped in step 2
> 7. Recreate all constraints you dropped in step 1
> And finally (but you could postpone this a while, to give you some other
> fallback scneario options)
> 8. Drop the "old" table
> --
> Hugo Kornelis, SQL Server MVP