Problem running stored procedures

[warmunger]

I created a database and then upsized the database to a SQL database. In my normal database my Query's worked fine but after upsizing it gives me following error "Cannot insert explicit value for identity column in table (MY TABLE NAME) when IDENTITY_INSERT is set to OFF"

 

[jgoodman00]

Fortunately, the solution to this is fairly simple.

Your primary key column must be set to 'identity' field. In SQL server, if Identity Insert is set to off (a default I believe) it will not allow you to insert a value into the primary key field, it must be provided by the server.

In order to recitify this, open your table in design view, select your primary key column & find the Identity property. Set this to No & your query should work fine. Be careful to remember to switch this back to yes before resuming normal database use.

James Goodman

http://www.james-goodman.co.uk

 

[jgoodman00]

Another solution is to include SET IDENTITY_INSERT [tablename] ON

However, this function is not supported graphically by Access, so your queries will have to be written in T-SQL if you use this method... James Goodman

http://www.james-goodman.co.uk

 

[jgoodman00]

Another solution is to include SET IDENTITY_INSERT [tablename] ON

However, this function is not supported graphically by Access, so your queries will have to be written in T-SQL if you use this method...

MSDN hyperlink:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_7zas.asp

James Goodman

http://www.james-goodman.co.uk