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

How to change or alter a user defined data type?

Hi all,
I already read this: http://vyaskn.tripod.com/administration_faq.htm#q12
and it's ok for the table/columns but I can NOT drop my user defined data
type using sp_droptype because it's already used in some Stored procs :(
Is there a solution WITHOUT a "delete & re-create" of these stored procs ?
Lilian.This example should demonstrate how to swap out the type. Note that the
rename affects tables, but not stored procs (so you won't have to recompile
your procedures, just hope they don't get called during the brief period
where the type doesn't exist).
-- originally, we create a datatype called fax,
-- and made it able to accept 32 characters
EXEC sp_addType 'fax', 'VARCHAR(32)'
GO
-- so we created a table that uses this datatype
CREATE TABLE dbo.foobar0
(
[fax] fax
)
GO
-- and a simple stored procedure as well
CREATE PROC dbo.foobar1
@.fax fax
AS
BEGIN
DECLARE @.fax2 fax
END
GO
-- now, we realize that fax numbers on
-- jupiter can contain 64 characters, so we
-- have to increase the size of the UDT
-- but we can't just alter the type, and we
-- can't drop it and re-create it either, without
-- altering or dropping / re-creating tables,
-- stored procedures, etc. that use the UDT
-- but we can use a little rename trick to
-- create an interim UDT that meets our needs
-- first, rename the existing UDT. This will
-- change table definitions but it will not alter
-- the text for a procedure / function
EXEC sp_rename 'fax', 'oldfax', 'USERDATATYPE'
GO
-- let's just make sure that it affected our table
-- but not our procedure:
EXEC sp_help foobar0
EXEC sp_helptext foobar1
GO
-- okay, now let's add the larger fax UDT back
-- into the system
EXEC sp_addtype 'fax', 'VARCHAR(64)'
GO
-- alter any tables / views that reference the old
-- fax UDT
ALTER TABLE foobar0 ALTER COLUMN [fax] fax
GO
-- now we should be able to drop the interim
-- UDT:
EXEC sp_droptype 'oldfax'
GO
-- (now let's clean up my silly example)
DROP TABLE dbo.foobar0
DROP PROCEDURE dbo.foobar1
EXEC sp_droptype 'fax'
GO
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:#pSUDo9PDHA.2036@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I already read this: http://vyaskn.tripod.com/administration_faq.htm#q12
> and it's ok for the table/columns but I can NOT drop my user defined data
> type using sp_droptype because it's already used in some Stored procs :(
> Is there a solution WITHOUT a "delete & re-create" of these stored procs
?
> Lilian.
>|||Sorry, you will have to recompile stored procedures that use the datatype as
in/out parameters, but not those that only use the type for local
variables...|||Very interesting...but do you "recompile" a stored proc by script ?
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:elXMq29PDHA.2676@.TK2MSFTNGP10.phx.gbl...
> Sorry, you will have to recompile stored procedures that use the datatype
as
> in/out parameters, but not those that only use the type for local
> variables...
>