title: Identity insert on linked server fails
body: <p>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 <code>IDENTITY_INSERT</code> to <code>ON</code> but it still complains about the ID column.</p>
  
  <p>Here's my procedure:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>But when I execute the SP it gives me the following error:</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>Linked server properties RPC and RPC out are set to true. I hope someboy can help me out here?</p>
  
  <p><strong>UPDATE:</strong> I decided to pull things apart, first I copy the data from the local server to the linked server in a <code>TEMP_TABLE</code> where I don't have to deal with <code>IDENTITY</code> issues.</p>
  
  <p>Then I wrote a stored procedure on the linked / remote server, since I'm not using <code>SELECT *</code> 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..</p>
  
  <pre><code>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 &lt;&gt; 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 &lt;&gt; 0 Return @err
  end Catch
  END
  </code></pre>