远程服务器上不允许使用范围标识

Question

我试图从一台服务器上由外键链接的两个表中复制数据，然后将它们插入另一台服务器的master数据库。

第一个表将生成一个新的IDENTITY列，然后我想将其存储在一个变量中，并将其插入到第二个表中。

我尝试过使用内部插入表和SCOPE_IDENTITY()，但收到了在REMOTE服务器上不允许这样做的错误

DECLARE 
@VisitSourceId  int,
@SiteDomainId   int,
@trpUTMid   bigint,
@FlightPlus bit,
@StartDate  datetime,
@CountryId  int,
@ProvinceId int,
@Locationid int,
@PlaceId    int,
@EstabId    int,
@CheckInDate    smalldatetime,
@CheckOutDate   smalldatetime,
@Rooms  int,
@Room1Adults    int,
@Room1Children  int,
@Room2Adults    int,
@Room2Children  int,
@Room3Adults    int,
@Room3Children  int,
@OutcomeDate    datetime,
@OutcomeId  smallint,
@HotelsFound    smallint,
@Notes  nvarchar,
@id bigint
DECLARE c CURSOR STATIC FOR 
SELECT t.VisitSourceId, t.SiteDomainId, t.trpUTMid, t.FlightPlus, t.StartDate, t.CountryId, t.ProvinceId, t.Locationid, 
t.PlaceId, t.EstabId, t.CheckInDate, t.CheckOutDate, t.Rooms, t.Room1Adults, t.Room1Children, t.Room2Adults, t.Room2Children, t.Room3Adults, 
t.Room3Children, tc.OutcomeDate, tc.OutcomeId, tc.HotelsFound, tc.Notes
FROM [MLT_VisitTracking].[dbo].TrackingAcomSearches_tbl t
INNER JOIN TrackingAcomSearchesOutcome_tbl tc
ON t.trpUTMid = tc.trpUTMid

open c
FETCH FIRST FROM c INTO @VisitSourceId, @SiteDomainId, @trpUTMid, @FlightPlus, @StartDate, @CountryId, @ProvinceId, @Locationid, 
@PlaceId, @EstabId, @CheckInDate, @CheckOutDate, @Rooms, @Room1Adults, @Room1Children, @Room2Adults, @Room2Children, @Room3Adults, 
@Room3Children, @OutcomeDate, @OutcomeId, @HotelsFound, @Notes  
while @@fetch_status=0
begin
DECLARE @TmpTable TABLE (ID BIGINT)
INSERT INTO [YAZOO].[MLT_VisitTracking].[dbo].TrackingAcomSearches_tbl
(VisitSourceId, SiteDomainId, trpUTMid, FlightPlus, StartDate, CountryId, ProvinceId, Locationid, 
PlaceId, EstabId, CheckInDate, CheckOutDate, Rooms, Room1Adults, Room1Children, Room2Adults, Room2Children, Room3Adults, 
Room3Children)
OUTPUT inserted.searchid into @TmpTable
SELECT @VisitSourceId, @SiteDomainId, @trpUTMid, @FlightPlus, @StartDate, @CountryId, @ProvinceId, @Locationid, 
@PlaceId, @EstabId, @CheckInDate, @CheckOutDate, @Rooms, @Room1Adults, @Room1Children, @Room2Adults, @Room2Children, @Room3Adults, 
@Room3Children

select top 1 @id = searchid from @tmptable

INSERT INTO [YAZOO].[MLT_VisitTracking].[dbo].TrackingAcomSearchesOutcome_tbl
(SearchId,
trpUTMid,
OutcomeDate,
OutcomeId,
HotelsFound,
Notes)
SELECT @id,
@trpUTMid,
@OutcomeDate,
@OutcomeId,
@HotelsFound,
@Notes

DELETE FROM [MLT_VisitTracking].[dbo].TrackingAcomSearches_tbl WHERE trpUTMid=@trpUTMid
DELETE FROM [MLT_VisitTracking].[dbo].TrackingAcomSearchesOutcome_tbl WHERE trpUTMid=@trpUTMid
FETCH NEXT FROM c INTO @VisitSourceId, @SiteDomainId, @trpUTMid, @FlightPlus, @StartDate, @CountryId,
@ProvinceId, @Locationid, @PlaceId, @EstabId, @CheckInDate, @CheckOutDate, @Rooms, @Room1Adults, @Room1Children,
@Room2Adults, @Room2Children, @Room3Adults, @Room3Children, @OutcomeDate, @OutcomeId, @HotelsFound, @Notes  
end
close c
deallocate c

Answer 1

@@IDENTITY和SCOPE_IDENTITY()是本地

你在使用链接服务器吗？检查此项：从另一个服务器(链接服务器)获取@@Identity

Answer 2

为了解决这个问题，我最终在目标服务器上将脚本创建为存储过程，然后我可以使用作用域标识。性能也有了很大的提高，然后我配置了一个启用了RPC输出的新链接服务器，并远程调用了该过程。这不是一个优雅的解决方案，但它只是一个临时的