IDENTITY_INSERT Fails - Why?

• cecil_a_galbraith

  Valued Member

  Points: 63

  More actions

  February 28, 2008 at 6:26 am

  #187128

  Can anyone tell me why the following fails? It works fine on SQL Server Express 2005 but not on CE. The issue is the second part of the query.

  create table MyTable (id integer identity(1,1), name nChar(50))

  go

  set identity_insert MyTable on

  go

  insert into MyTable(id,name) values(2,'test')

  go

  Major Error 0x80040E14, Minor Error 25501

  > set identity_insert MyTable on

  There was an error parsing the query. [ Token line number = 1,Token line offset = 5,Token in error = identity_insert ]

  Major Error 0x80004005, Minor Error 25004

  > insert into MyTable(id,name) values(2,'test')

  The column cannot be modified. [ Column name = id ]

• martin-693567

  Valued Member

  Points: 61

  More actions

  March 30, 2008 at 9:43 pm

  #795762

  Are you using version 3.5? SET IDENTITY_INSERT support was introduced in version 3.5

• Mohan Kumar

  SSCertifiable

  Points: 5365

  More actions

  March 30, 2008 at 10:20 pm

  #795767

  In addition to what Martin said, If you are using CE 3.0 then use ALTER TABLE to SET identity.

  [font="Verdana"]--www.sqlvillage.com[/size][/font]