If I'm inserting records and I want to specify a value for an identity column on a local table I do this and everything works fine:
set identity_insert <table> on
insert <table> (f1 , f2) select 1 , 'blah'
But when I try to do the same thing from a linked server I encounter an error. I execute this SQL after setting up the linked server:
set identity_insert <server>.<database>.<owner>.<table> on
error message I encounter:
Server: Msg 8103, Level 16, State 1, Line 1
Table '<server>.<database>.<owner>.<table>' does not exist or cannot be opened for SET operation.
Can you not execute a SET command on a linked server? If not how can I explicitly insert values into the identity columns of linked tables?