IDENTITY Insert doesnt work for Linked servers

Hi Everybody,
In my program, I am inserting rows from the source database to the
destination database which are on different servers. From source database,
with the help of the link server, I am connecting to the destination server.
In the destination table(destination db), I have one identity column which
is a primary key. But in that table, I need to insert the rows with the same
values as in the source table(source db). So i am using
SET IDENTITY_INSERT [linked server].[database].[user].[tablename] ON
But this when executed shows me an error - "Table does not exist or cannot
be opened for SET operation."
Please tell me how to make the identity insert ON on the table through the
linked server. I can execute the same command on any table on the same
database server, but not on destination database server.
Thanks.You can't. You'll have to do it on the destination server - for instance you
can create a SP on the target server that inserts the data and then call that
SP remotely. Or use DTS for example.
Why do you care that the IDENTITY value is preserved in the destination DB?
This rather defeats the point of an IDENTITY column. Allow the server to
assign a new IDENTITY value and then assign the new ID to any referencing
rows as you insert them. For queries across the two servers relate the data
on a natural key, not on the IDENTITY.
--
David Portas
SQL Server MVP
--