Making an Existing column an Identity column

Hi,

I have a column that is unique that I would like to make into an IDENTITIY column after I insert some data into it.

I tried

alter table <table_name>
alter column <col_name> int Identity (1,1)

but it fails.

Ajay

WORD4LIFE
(http://www.word4life.com)Nope...can't do it...

If you have a table with a int column and want to change it...you can graphically do it in EM...

But guess what it's really doing...

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Table1a
(
col1 int NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_Table1a ON
GO
IF EXISTS(SELECT * FROM dbo.Table1a)
EXEC('INSERT INTO dbo.Tmp_Table1a (col1)
SELECT col1 FROM dbo.Table1a TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_Table1a OFF
GO
DROP TABLE dbo.Table1a
GO
EXECUTE sp_rename N'dbo.Tmp_Table1a', N'Table1a', 'OBJECT'
GO
GRANT SELECT ON dbo.Table1a TO public AS dbo
COMMIT

Just make sure you don't already have a temp_table...

What a hack

M$ is good at them...lots of practice...|||Look it up in BOL

TABLOCKX Use an exclusive lock on a table. This lock prevents others from reading or updating the table and is held until the end of the statement or transaction.

And the IDENTITY_INSERTS Are inb the correct order...

btw, sql server itself generated it...look a save sql server change script in EM after you make a change to a table...sql

Make Changes to Published Database

Vince,
are you making changes through the GUI (SSMS)? It sounds like you are, but
you'll need to use ALTER TABLE instead (see
http://www.replicationanswers.com/AlterSchema2005.asp).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Yes, I am using the SSMS gui. It is really the only thing I feel safe
working with.
I read your post and that is the exact error I get when trying to make
changes to my published tables. I may have bitten off more than I can chew
with this. I guess my real question/problem/concern is this:
Let's say I have 500+ subscribers and they all have between 25 and 50 MB of
data in their local store (SQLce). At some point I will want to make
changes to my program which require new fields, field name changes, new
tables, etc. From what I am encountering I have to go into the SSMS gui,
delete the publication and all subscriptions and then make changes to my
tables. At this point all the users still have their data in their local
SQLce file; this is what I want so far. However, when I recreate my
publication on the central server and the subscribers go to synchronize what
will happen? I assume it will not find the subscription on the server. So,
will the user be required to delete the file and start over with a new
initialization or can I just add that subscription back to the central
server and just propogate only the changes to the subscribers? What about
changes that the subscriber makes to their local SQLce file during the time
that I removed the publication and then recreated it?
I understand I can do this stuff using scripts and stored procedures, etc.,
but I have zero experience with that and I am kind of afraid to mess with
anything other than the SSMS gui when I have this in production.
I am only a programmer not a dba. I understand
indexing/relationships/tables/field types and so on, but TSQL is foreign to
me. Thanks in advance for your patience.
Vince
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eGC7XcgKHHA.4460@.TK2MSFTNGP03.phx.gbl...
> Vince,
> are you making changes through the GUI (SSMS)? It sounds like you are, but
> you'll need to use ALTER TABLE instead (see
> http://www.replicationanswers.com/AlterSchema2005.asp).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||Vincent,
you really don't want to reinitialize for all schema changes. If you are
confortable with the SSMS gui, then by all means use that to add articles.
To chnage an existing article, just study ALTER TABLE in BOL - as you're a
programmer, this will be easy to pick up.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .