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
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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With