Hi

I'm trying to insert a number of records into a linked server. One of
the columns I'm trying to insert into, is an IDENTITY field so I need to
run SET IDENTITY INSERT ON for this. How do I do this for a linked
server? The SET IDENTITY INSERT ON statement only accepts the database
and table name - not the server name as identifier.

Steen,

you cannot do this (as far as I'm aware).
The set statement is 3 part and not 4 part.

An idea - you could create a stored procedure on the linked server that sets the identity value off and on, then you could call the procedure before executing your insert statetment?

Immy
  I'm trying to insert a number of records into a linked server. One of the columns I'm trying to insert into, is an IDENTITY field so I need to run SET IDENTITY INSERT ON for this. How do I do this for a linked server? The SET IDENTITY INSERT ON statement only accepts the database and table name - not the server name as identifier.


  --
  Regards
  Steen Schlüter Persson
  Databaseadministrator / Systemadministrator

You may be able to get away with it by placing all of the required commands
inside a string and executing via:

exec MyLinkedServer.MyDB.dbo.sp_executesql ...

I'm trying to insert a number of records into a linked server. One of
the columns I'm trying to insert into, is an IDENTITY field so I need to
run SET IDENTITY INSERT ON for this. How do I do this for a linked
server? The SET IDENTITY INSERT ON statement only accepts the database
and table name - not the server name as identifier.

Tom Moreau wrote:
....of course.....why didn't I think about that...:-(.

Thanks for the help.

how to pass in a long string to stored procedure
Need Help With a Simple Query
Error converting data type varchar to numeric
Check int value
Strange characters with Bulk Load