Re: ALTER IDENTITY setting...
From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 01/03/05
- Next message: Jim Edgar: "Re: Command Object, SQL Stored Procedure, and Data Type"
- Previous message: Raterus: "Re: Sql Server Queue?"
- In reply to: Angel: "Re: ALTER IDENTITY setting..."
- Next in thread: Nigel Rivett: "Re: ALTER IDENTITY setting..."
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 3 Jan 2005 12:05:02 -0600
Why can't you drop the table? Make a backup (hopefully this is an obvious
step, but make sure you do it :) create the new table that looks like you
want it to, and the populate the new table with the data, and then apply all
of the constraints that were on the original table (pretty much what
Enterprise Manager would do. Now if this will take too much time, like it
is a 24/7 system and the table has 2.789 billion rows, that would make
sense, but normal production systems have down time, and dropping the table
temporarily is probably acceptable.
I also would suggest you use EM and save the script. When you "try" this
out on your test system and it works, you want to make 100% sure that you do
the exact same thing in production, lest you make a boo boo (again something
most everyone does once, but hopefully not twice!)
-- ---------------------------------------------------------------------------- Louis Davidson - drsql@hotmail.com SQL Server MVP Compass Technology Management - www.compass.net Pro SQL Server 2000 Database Design - http://www.apress.com/book/bookDisplay.html?bID=266 Note: Please reply to the newsgroups only unless you are interested in consulting services. All other replies may be ignored :) "Angel" <Angel@discussions.microsoft.com> wrote in message news:CB5BC37D-3B31-4192-9EAE-67459F633815@microsoft.com... >I can not drop the table, because it is on production server. > > I just need to alter column with Identity. > > I think that my unique option is doing it using Enterprise Manager, but > I prefer send a script because i reported it another person not technical > in order to do it the change. > > But i believe that exist one chance, which ??? > > > > "Aaron [SQL Server MVP]" wrote: > >> You can't add or remove the IDENTITY propety for an existing table. You >> can >> do it in enterprise manager, but watch how it does it... you will have to >> do >> the manual work yourself, also (copy all the data to a new working table >> with an IDENTITY column already defined, then drop the old table, then >> rename the new one). >> >> -- >> http://www.aspfaq.com/ >> (Reverse address to reply.) >> >> >> >> >> "Angel" <Angel@discussions.microsoft.com> wrote in message >> news:7073DA88-0F95-45E7-96F5-7E6EE7284C9A@microsoft.com... >> > Hello, >> > >> > I need to find the way to alter a column in order >> > to set IDENTITY (1,1), whats the script once table created ? >> > >> > I can not delete the table. >> > I need it to do using script ALTER. >> > >> > I have this table created: >> > >> > Table: CONVERSION_TIPO_TRABAJADOR >> > Columns: >> > PK [TT_COD_TIPO_TRABAJADOR] [int] NOT NULL , >> > [TT_DESC_TIPO_TRABAJADOR] [nchar] (15) NOT NULL >> > >> > >> > I am trying to write the script: >> > >> > ALTER TABLE [dbo].[CONVERSION_TIPO_TRABAJADOR] >> > ALTER COLUMN [TT_COD_TIPO_TRABAJADOR] [int] IDENTITY (1, 1) NOT NULL >> > GO >> > >> > I get this error: >> > >> > Server: Msg 156, Level 15, State 1, Line 2 >> > Incorrect syntax near the keyword 'IDENTITY'. >> > >> > so, whats the problem ? >> > >> > Any help is greatly appreciated. >> > >> > >> >> >>
- Next message: Jim Edgar: "Re: Command Object, SQL Stored Procedure, and Data Type"
- Previous message: Raterus: "Re: Sql Server Queue?"
- In reply to: Angel: "Re: ALTER IDENTITY setting..."
- Next in thread: Nigel Rivett: "Re: ALTER IDENTITY setting..."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
