Thank you very much Mr.Thomas. I came across the same issue ie non- identity to identity type change and your article has helped a me a lot.

But when I have tried it in a temp database it shows the error

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value 'TMP' to data type int.

But again I have tried in another database its working fine.

--drop table tmp

create table tmp

(s_noint,

namevarchar(10)

)

insert into tmp

values

(12,'name1')

insert into tmp

values

(13,'name2')

insert into tmp

values

(14,'name3')

ALTER TABLE tmp ADD s_no_II INT IDENTITY(1,1)

ALTER TABLE tmp DROP COLUMN s_no

EXEC sp_rename

'tmp.s_no_II','s_no', 'COLUMN'

Can u brief and how the error can be overcome?

Thanks in advance.