链接服务器上的身份插入失败 [英] Identity insert on linked server fails
问题描述
我想使用存储过程将表从我的测试数据库复制到具有相同 ID/身份的链接服务器,但我无法让它工作..我已将 IDENTITY_INSERT
设置为 ON
但它仍然抱怨 ID 列.
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.
这是我的程序:
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
但是当我执行 SP 时,它给了我以下错误:
But when I execute the SP it gives me the following error:
链接服务器的 OLE DB 提供程序SQLNCLI10".... 无法插入表[LINKEDSERVER].[ProductManager].[dbo].[UIData]",因为列Id".用户无权写入该列.
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.
链接的服务器属性 RPC 和 RPC out 设置为 true.我希望有人可以帮助我吗?
Linked server properties RPC and RPC out are set to true. I hope someboy can help me out here?
更新:我决定把事情分开,首先我将数据从本地服务器复制到 TEMP_TABLE
中的链接服务器,我不必处理IDENTITY
问题.
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.
然后我在链接/远程服务器上编写了一个存储过程,因为我没有使用 SELECT *
而是指定列列表.有可能这也可以在 SP 的本地服务器上运行,但我还没有时间或兴趣检查一下..
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
推荐答案
IDENTITY_INSERT
不适用于链接服务器 AFAIK,除非您执行包含 SET IDENTITY_INSERT
在批处理中或在远程服务器上有一些代码(例如存储过程)为您执行此操作.
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.
IDENTITY_INSERT
是针对每个会话的(请参阅 MSDN) 并且当您使用远程服务器时,这可能与您通过 [LINKEDSERVER].tempdb.sys.sp_sqlexec
执行的语句位于不同的会话中,这会导致它失败,如您所见它正在发生.
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.
这篇关于链接服务器上的身份插入失败的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!