cannot update linked table

[trinard]

I get the following error when I try to use an append
query to update a linked table. Anyone can point me in a
the right direction as to what I may be doing wrong.

Explicit value must be specified for identity column in
table 'tablename' when IDENTITY_INSERT is set to ON.

 

[Joe Fallon]

In SQL Server an Auotnumber field is known as an Identity.
If IDENTITY_INSERT is set to ON then you have to insert the Identity value
as part of your query.
If it is OFF (the normal state!) then you can omit the field and the server
will add it for you.

***Looks like someone set it ON and forgot to set it OFF when they were
done.***

When I migrate data from an old database to a new version I use scripts like
this in Query Analyzer:
(This way I can insert the "old" existing Identity values into a blank table
and then turn it back off so that new records get created with their own key
automatically.)

set identity_insert database.dbo.table1 on
go

insert into table1 (mykey,descr,userid,tstamp)
select mykey,descr,userid,tstamp from otherdatabase.dbo.table1
go

set identity_insert database.dbo.table1 off
go