ALTER TABLE tblname ALTER COLUMN col_name INT (101,1) IDENTITY NOT NULL

Error : Incorrect syntax near the keyword 'IDENTITY'.

Please help me to figure out this correct syntax....

Thanks,
Vamsi

vamsi

__________________
vamsi Krishna G
mobile: 91 + 9986023966.
124 Puttappa layout new Tippasandhra,
bangalore.
Inida

This is the SQL Language forum. Your question concerns what some implementations' DDL can and cannot do about the IDENTITY property, something unique to specific RDBMS implementations.

That having been said, if you are talking about SQL Server, I think you have a couple things wrong with your attempt.

The syntax for defining the IDENTITY property is IDENTITY(<seed>, <incr>) and not the way you have written it.

The real problem, though, is that you cannot alter a column with the IDENTITY property this way. The IDENTITY(<seed>, <incr>) expression can only be specified when you ADD a column. It cannot be changed via DDL this way once the column exists.

In SQL Server, you can use the DBCC CHECKIDENT utility command to change an Identity column's seed value, as:

    DBCC CHECKIDENT('yourtable', RESEED, <newseed>)

I don't know of a way to change the increment value without dropping then readding the column.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

  U'r reply helped me a Lot. thanks for u r solution.

vamsi