Edit Question - KnowledgeHub

Title Body

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 QUO

Tags (comma-separated) Cancel