ALTER COLUMN using ADO (need to change column to int identity)

[jstiegelmeyer]

Does anyone know the correct syntax for Altering the type of a field to "int identity"? I need the field to be an autokey, but it is currently only an int. I can't make the change manually because the database is in production in a number of sites outside of my reach. Does anyone know the correct syntax using Visual Basic, ADO and MS SQL Server?

I've tried:
Alter Table MyTable Alter Column MyCol int identity

And get the following error:
Incorrect syntax near the keyword 'identity'..

If I just use "int" instead of "int identity", it executes without errors, but that doesn't solve my problem.

If anyone has any ideas, or a useful website for me to look at, it would be greatly appreciated.

Thanks,
Jamie

 

[sunila7]

hi,

This Script should Help u change a column to identity. I think u cannot ALTER a column defintion to Identity .... So this script which I created is Created from Enterprise manager.....

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.MyTable1
(
MyCol int NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.MyTable1 ON
GO
IF EXISTS(SELECT * FROM dbo.MyTable)
EXEC('INSERT INTO dbo.MyTable1 (Mycol)
SELECT Mycol FROM dbo.MyTable TABLOCKX')
GO
SET IDENTITY_INSERT dbo.MyTable1 OFF
GO
DROP TABLE dbo.MyTable
GO
EXECUTE sp_rename N'dbo.Mytable1', N'MyTable', 'OBJECT'
GO
COMMIT


Sunil

 

[jstiegelmeyer]

Sunil-

Thanks for the help. Can I run that script through ADO (w/VB)? I can't access the DBs through Enterprise manager.

The best possible solution would be separate SQL commands. Can I run this script one command at a time through ADO?

Thanks for your time,
Jamie

 

[sunila7]

hi,

u can run this script from query analyzer also....

Yes u should be able to VB also. If u have permission to the Database.


Sunil

 

[jstiegelmeyer]

Sunil-

Thanks so much for your help. Things have been extremely busy, and I haven't tried your solution yet, but I will try it soon. I just wanted to tell you thanks again before letting any more time pass.

Jamie