Identity insert on linked server fails

Question

I want to use a stored procedure to copy a table from my test database to a linked server with the same ID's / Identity but I can't get it to work.. I've set the IDENTITY_INSERT to ON but it still complains about the ID column.

Here's my procedure:

CREATE PROCEDURE [dbo].[TEST2PROD_CopyUIDataSServer] AS Begin declare @sql nvarchar(max) -- First truncate target table set @sql = 'EXEC [LINKEDSERVER].tempdb.sys.sp_sqlexec' + char(39)+ 'TRUNCATE Table [ProductManager].dbo.[UIData]' + char(39)+  ';' ---- SET IDENTITY_INSERT ON set @sql = @sql + 'EXEC [LINKEDSERVER].tempdb.sys.sp_sqlexec' + char(39)+ 'SET IDENTITY_INSERT [ProductManager].[dbo].[UIData] ON' + char(39)+  ';' ---- INSERT UIDATA records from DB1 into linked server DB2 set @sql = @sql + 'WITH TestData as (SELECT * from ProductManager.dbo.UIData UID)' + NCHAR(13)+  'INSERT INTO   [LINKEDSERVER].[ProductManager].[dbo].[UIData]' + NCHAR(13) + 'select * from TestData;' print @sql exec (@sql)  end 

But when I execute the SP it gives me the following error:

The OLE DB provider "SQLNCLI10" for linked server .... could not INSERT INTO table "[LINKEDSERVER].[ProductManager].[dbo].[UIData]" because of column "Id". The user did not have permission to write to the column.

Linked server properties RPC and RPC out are set to true. I hope someboy can help me out here?

UPDATE: I decided to pull things apart, first I copy the data from the local server to the linked server in a TEMP_TABLE where I don't have to deal with IDENTITY issues.

Then I wrote a stored procedure on the linked / remote server, since I'm not using SELECT * but specify the column list. Chances are this will work from the local server in an SP too but I don't have the time or interest to check it out yet..

USE [ProductManager] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[TEST2PROD_CopyBaseTables] AS BEGIN DECLARE @DestTable VARCHAR(50) DECLARE @DestPath VARCHAR(50) DECLARE @SrceTable VARCHAR(255) declare @sql nvarchar(max) DECLARE @columnList varchar(max) DECLARE @err int  Begin TRY  declare @comma_delimited_list varchar(4000)  --- FIRST TRY WITH ONE TABLE, EXTENDABLE... set @comma_delimited_list = 'UIData'   declare @cursor cursor  set @cursor = cursor static for     select * from dbo.Split(@comma_delimited_list,',') a   declare @naam varchar(50) open @cursor  while 1=1 begin    fetch next from @cursor into @DestTable   if @@fetch_status <> 0 break       --Create tablenames     SET @SrceTable = '[ProductManager].[dbo].TEMP_' + @DestTable     SET @DestPath = '[ProductManager].[dbo].'+ @DestTable      print @srceTable;     print @DestTable;      --Truncate target table     set @sql ='TRUNCATE TABLE '+ @DestPath + ';'      --Insert statement needs column names     set @columnList =''     SELECT  @columnList = coalesce(@columnList + '[' + name + '],','') FROM sys.columns Where OBJECT_NAME(OBJECT_ID) = @DestTable     if RIGHT(RTRIM(@columnList),1) = ','      begin         SET @columnList = LEFT(@columnList, LEN(@columnList) - 1)      end      --Transfer data from source table 2 destination      set @sql = @sql + ' SET IDENTITY_INSERT ' + @DestPath + ' ON;' + ' INSERT INTO ' + @DestPath + '(' + @columnList + ') SELECT ' + @columnList + ' FROM ' + @SrceTable      print @sql;      exec (@sql) end  -- not strictly necessary w/ cursor variables since the will go out of scope like a normal var  close @cursor  deallocate @cursor   End Try Begin Catch  declare @ErrorMsg nvarchar(MAX);  select @ErrorMsg =  ERROR_MESSAGE();  SELECT @err = @@error IF @err <> 0 Return @err end Catch END 

Answer 1

IDENTITY_INSERT doesn't work with linked servers AFAIK, unless you execute dynamic SQL that includes the SET IDENTITY_INSERT in the batch or have some code (Stored Proc for instance) on the remote server which does that for you.

The IDENTITY_INSERT is per-session (see MSDN) and when you use the remote server this will probably be in a different session from your statement executed via [LINKEDSERVER].tempdb.sys.sp_sqlexec, which causes it to fail as you see it happening.