SQL Server SCOPE_IDENTITY不运行链接服务器

Question

我有两台使用链接服务器连接的服务器。当我运行这个程序时，它会向其他人插入一条记录，那时我需要从我的第二台服务器获得ID--这是怎么回事？

Alter PROCEDURE [dbo].[stp_TransferJob] 
( 
    @JOB_id AS bigint, 
    @PartyId as int, 
    @Commission as decimal(18,2), 
    @myid as varchar(max), 
    @ident_out as bigint output 
) 
AS 
BEGIN 


INSERT INTO [111.163.103.122].Taxi.dbo.booking (FromLocTypeId,ToLocTypeId,FromLocId,ToLocId,VehicleTypeId,DriverId,ReturnDriverId,CustomerId,CustomerName,CustomerEmail,CustomerPhoneNo,CustomerMobileNo,JourneyTypeId,BookingNo,BookingDate,NoofPassengers,NoofLuggages,NoofHandLuggages,PickupDateTime,ReturnPickupDateTime,IsCompanyWise,CompanyId,FareRate,PaymentTypeId,SpecialRequirements,FromAddress,ToAddress,FromPostCode,ToPostCode,FromDoorNo,ToDoorNo,FromStreet,ToStreet,FromFlightNo,FromComing,BookingStatusId,DistanceString,AutoDespatch,AutoDespatchTime,AddOn,AddBy,AddLog,EditOn,EditBy,EditLog,OrderNo,PupilNo,ParkingCharges,WaitingCharges,ExtraDropCharges,MeetAndGreetCharges,CongtionCharges,TotalCharges,DepartmentId,ReturnFareRate 
,ArrivalDateTime,MasterJobId,DisablePassengerSMS,DisableDriverSMS,IsCommissionWise,DriverCommission,DespatchDateTime,JobOfferDateTime,BookingTypeId,DriverCommissionType,IsBidding,IsQuotation,CostCenterId,CashRate,AccountRate,WaitingMins 
,ExtraMile,AcceptedDateTime,POBDateTime,STCDateTime,ClearedDateTime,CancelReason,TotalTravelledMiles,CompanyPrice,SubCompanyId,PartyId, 
FromOther,ToOther,TransferJobId,TransferJobCommission,BookingTypeId,ViaString) 

SELECT 
FromLocTypeId,ToLocTypeId,FromLocId,ToLocId,VehicleTypeId,null,ReturnDriverId,CustomerId,CustomerName,CustomerEmail,CustomerPhoneNo,CustomerMobileNo,JourneyTypeId,BookingNo,BookingDate,NoofPassengers,NoofLuggages,NoofHandLuggages,PickupDateTime,ReturnPickupDateTime,IsCompanyWise,null,FareRate,PaymentTypeId,SpecialRequirements,FromAddress,ToAddress,FromPostCode,ToPostCode,FromDoorNo,ToDoorNo,FromStreet,ToStreet,FromFlightNo,FromComing,BookingStatusId,DistanceString,AutoDespatch,AutoDespatchTime,AddOn,AddBy,AddLog,EditOn,EditBy,EditLog,OrderNo,PupilNo,ParkingCharges,WaitingCharges,ExtraDropCharges,MeetAndGreetCharges,CongtionCharges,TotalCharges,DepartmentId,ReturnFareRate 
,ArrivalDateTime,MasterJobId,DisablePassengerSMS,DisableDriverSMS,IsCommissionWise,DriverCommission,DespatchDateTime,JobOfferDateTime,BookingTypeId,DriverCommissionType,IsBidding,IsQuotation,CostCenterId,CashRate,AccountRate,WaitingMins 
,ExtraMile,AcceptedDateTime,POBDateTime,STCDateTime,ClearedDateTime,CancelReason,TotalTravelledMiles,CompanyPrice,1,@PartyId 
,FromOther,ToOther,@JOB_id,@Commission,10,ViaString 

FROM Booking 
WHERE Id = @JOB_id ; 
select SCOPE_IDENTITY() 
SET @ident_out = @@IDENTITY 

------------------------------------------------------------------------- 
INSERT INTO [111.163.103.122].Taxi.dbo.booking_ViaLocations ( 
BookingId,ViaLocTypeId,ViaLocTypeLabel,ViaLocTypeValue,ViaLocId,ViaLocValue,ViaLocLabel ) 



SELECT 
@ident_out,ViaLocTypeId,ViaLocTypeLabel,ViaLocTypeValue,ViaLocId,ViaLocValue,ViaLocLabel 
FROM Booking_ViaLocations 
WHERE BookingId = @JOB_id 

END 
return @ident_out 

GO 

SCOPE_IDENTITY（）和@@ IDENTITY SHOW NULL值如何获得ID，请帮我

Answer 1

您可以返回使用存储过程INT值

create PROCEDURE [dbo].[stp_TransferJob] 
( 
    @JOB_id AS bigint, 
    @PartyId as int, 
    @Commission as decimal(18,2) 
) 
AS 
BEGIN 



    INSERT INTO [111.163.103.122].Taxi.dbo.booking (FromLocTypeId,ToLocTypeId,FromLocId,ToLocId,VehicleTypeId,DriverId,ReturnDriverId,CustomerId,CustomerName,CustomerEmail,CustomerPhoneNo,CustomerMobileNo,JourneyTypeId,BookingNo, 
    FromOther,ToOther,TransferJobId,TransferJobCommission,BookingTypeId,ViaString) 

    SELECT 
    FromLocTypeId,ToLocTypeId,FromLocId,ToLocId,VehicleTypeId,null,ReturnDriverId,CustomerId,CustomerName,CustomerEmail,CustomerPhoneNo,CustomerMobileNo,JourneyTypeId,BookingNo,FromOther,ToOther,@JOB_id,@Commission,10,ViaString 

    FROM Booking 
    WHERE Id = @JOB_id 

RETURN (@@IDENTITY) 

END 

然后当你打电话给你的程序在其他服务器上：

​​

由于通过错误代码执行此操作比较常见，所以您mi ght更喜欢输出参数：

create PROCEDURE [dbo].[stp_TransferJob] 
( 
    @JOB_id AS bigint, 
    @PartyId as int, 
    @Commission as decimal(18,2), 
    @ident_out as int output 
) 
AS 
BEGIN 



    INSERT INTO [111.163.103.122].Taxi.dbo.booking (FromLocTypeId,ToLocTypeId,FromLocId,ToLocId,VehicleTypeId,DriverId,ReturnDriverId,CustomerId,CustomerName,CustomerEmail,CustomerPhoneNo,CustomerMobileNo,JourneyTypeId,BookingNo, 
    FromOther,ToOther,TransferJobId,TransferJobCommission,BookingTypeId,ViaString) 

    SELECT 
    FromLocTypeId,ToLocTypeId,FromLocId,ToLocId,VehicleTypeId,null,ReturnDriverId,CustomerId,CustomerName,CustomerEmail,CustomerPhoneNo,CustomerMobileNo,JourneyTypeId,BookingNo,FromOther,ToOther,@JOB_id,@Commission,10,ViaString 

    FROM Booking 
    WHERE Id = @JOB_id 

SET @ident_out = @@IDENTITY 

END 

然后当您调用过程时，您添加一个带有输出关键字的声明参数。请参阅msdn