Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Identity insert on linked server fails

Tags:

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 
like image 342
Mike Dole Avatar asked Sep 12 '12 15:09

Mike Dole


People also ask

Can we insert data using linked server?

To insert to a "local" table using a linked server, just use the insert into as with any other table but using the fully qualified name.

Can we insert value in identity column in SQL Server?

You can also reseed the identity field value. By doing so identity field values will start with a new defined value. Suppose you want to reseed the Customer table ID field from 3 then the new record s will be inserted with ID 4,5,6..and so on.

Can you insert into an identity column?

By default, it's not possible to manually insert a value directly into an identity column value, but identity values can be manually entered if you turn on a session option.


1 Answers

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.

like image 119
Lucero Avatar answered Dec 13 '22 11:12

Lucero