IDENTITY Insert doesnt work for Linked servers

• Sachin-210484

  Valued Member

  Points: 68

  More actions

  February 21, 2005 at 8:34 am

  #113455

  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]..[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.

• peterhe

  SSChampion

  Points: 11469

  More actions

  February 22, 2005 at 6:46 am

  #543446

  You can set up the link server on the destination server, and perform your inserting there.