Modify column to an identity column

Posted on 2009-12-27
Medium Priority
Last Modified: 2012-05-08

I am working on MSSQL 2005

In my existing table I wanted to modify column tripdataID as an identity

I wrote query


It gives me error saying

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IDENTITY'.

Please help me in fixing it

Many Thanks
Question by:tia_kamakshi
  • 4
  • 2
  • 2
  • +1
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 26126847
what is the database compatibility mode? it must be 90 (or higher)

Author Comment

ID: 26126873
Thanks for your reply.

How should I check database  compatibility mode?

Thanks again
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 26127029

I have successfully executed query
sp_dbcmptlevel tempdb, 90
select * from dbo.trip_data where tripdata_trip_link= 2568 order by tripdata_trip_link, tripdataID

Means my sql supports database  compatibility mode 90

Now How can I modify my column to Identity

Many Thanks
LVL 51

Expert Comment

by:Mark Wills
ID: 26127500
Well, easiest way is to use SSMS and navigate to you table via your database (in Object Explorer on the left in SSMS) and right click on the table and go into design.

Should show a list of columns on the right hand side. Click on your column name. Now, down the bottom are column properties. First up the checkbox for Allow Nulls should not be ticked (ie does not allow NULL values). The down the bottom if you scroll down a bit you will see a group heading (ie a small plus sign to the left) "Identity Specification" expand that and the first entry underneath is (is identity) and to the right, change it to "Yes".

You can now close that window and it will then ask you if you want to save changes (close just the query / designer window not the whole SSMS window), to which you answer "Yes" and it will try to apply the changes. If there are any errors, they will display, and will prevent the change from happening. We will need to see any errors. Typically it is the NULL value if nulls were previously permitted.


Author Comment

ID: 26127572
I know using Object exlporer, but for some reason I am not able to open table in design mode

Can I get the script, which can modify my column to Identity. There is no null value in the column and no column has duplicate values. Its is integer and has value of int type

So, Please help me with the script

See my error details which comes in object explorer in code snippet part

Many Thanks

Unspecified error
 (MS Visual Database Tools)

Program Location:

   at Microsoft.VisualStudio.DataTools.Interop.IDTTableDesignerFactory.EditTable(Object dsRef, Object pServiceProvider)
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.TableDesignerNode.CreateDesigner(IDTDocToolFactoryProvider factoryProvider, IVsDataConnection dataConnection)
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.CreateDesigner()
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.Open()
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProject.Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ScriptFactory.CreateDesigner(DocumentType editorType, DocumentOptions aeOptions, Urn parentUrn, IManagedConnection mc)
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.CreateDesignerWindow(IManagedConnection mc, DocumentOptions options)

Open in new window

LVL 51

Accepted Solution

Mark Wills earned 1000 total points
ID: 26127766
Well that's is a pity, it is by far the easiest way to proceed - must have a permissions issue (or something)...

Doing it via script is not going to work all that easily.

You cannot use alter table to alter a column to become an identity.

So, what opten ends up happening is creating a new table with the identity property specific, turn on SET IDENTITY_INSERT <tablename> ON, load that new table, then rename the old and rename the new to the original table.

But if you have other tables depending on the the above table + columns (such as foreign keys) then it does make it especially dificult.

Do you have other dependancies on that column ?

Expert Comment

ID: 26129637
Open  SSMS GUI to open the table in Design view and set the identity property to True on the column. It worked and even started incrementing at the right place.

Assisted Solution

sureshchsahu earned 1000 total points
ID: 26129640
There is no convenient ALTER TABLE command for this. (Except in SQL
Server CE!). So you need to:

1) Rename the existing table and any constraints it may have.
2) Run a CREATE TABLE statement with the new definition of the column,
including constraints for the table.
3) Isssue SET IDENTITY_INSERT ON for the table to permit inserting of
explicit values in the IDENTITY column.
4) INSERT newtbl (...) SELECT ... FROM oldtbl.
5) Move referencing foreign keys to refer to the new table.
6) Restore triggers and indexes on the new table.
7) drop the old table

From the Enterprise Manager you can carry out this by point-and-click.
However, what EM performs behind the scenes is something like the above

Author Closing Comment

ID: 31670175
Many Thanks

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question