You cannot have nulls in the column. If you are receiving an error there are probably nulls.

Scripting from enterprise manager changing a column to Identity, it creates a table called Tmp_youtablename with identity

Then it moves the data to tmp_yourtable from your table

then it drops yourtablename

then it renames to yourtablename from tmp_yourtablename.

Use the same way it works every time.

Code:

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_Orders

(

id_Order int NOT NULL IDENTITY (1, 1),

stuffy char(10) NULL

) ON [PRIMARY]

GO

SET IDENTITY_INSERT dbo.Tmp_Orders ON

GO

IF EXISTS(SELECT * FROM dbo.Orders)

EXEC('INSERT INTO dbo.Tmp_Orders (id_Order, stuffy)

SELECT id_Order, stuffy FROM dbo.Orders TABLOCKX')

GO

SET IDENTITY_INSERT dbo.Tmp_Orders OFF

GO

DROP TABLE dbo.Orders

GO

EXECUTE sp_rename N'dbo.Tmp_Orders', N'Orders', 'OBJECT'

GO

COMMIT