SQL Server SCOPE_IDENTITY does not run linked server

Question

I have two servers that are connected using a linked server. When I run this procedure, it inserts a record one to other and that time I want ID from my second server - how do this?

  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() AND @@IDENTITY SHOW NULL VALUE HOW TO GET ID please help me

Try to extract identity in the procedure and pass it through an output parameter — PacoDePaco, Aug 19 '16 at 13:38
http://stackoverflow.com/questions/5708996/best-way-to-get-identity-of-inserted-row-in-linked-server — TheGameiswar, Aug 19 '16 at 13:41
Instead of an insert statement can you create an insert stored procedure on your other server? Then have it return the identity from SCOPE_IDENTITY as an output parameter. --LOL I just looked at the link posted by @TheGameiswar and it suggests the same thing. :D — Sean Lange, Aug 19 '16 at 14:02
Have you looked at the link posted above? It explains quite clearly a very simple way to accomplish this task. — Sean Lange, Aug 19 '16 at 14:14
@SeanLange i checked and i posted above query but its return 0 — Addi Khan, Aug 19 '16 at 14:16
That is because your above query is nothing like the example posted as the answer in the linked question. — Sean Lange, Aug 19 '16 at 14:18
INSERT INTO [111.163.103.122].Taxi.dbo.booking_ViaLocations ( BookingId,ViaLocTypeId,ViaLocTypeLabel,ViaLocTypeValue,ViaLocId,ViaLocValue,ViaLocLabel where bookingId = @ident_out) how to add where caluse this statement — Addi Khan, Aug 19 '16 at 14:19

PacoDePaco · Answer 1 · 2016-08-19T13:50:39.230

You can return int values using a stored procedure

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

Then when you call your procedure on other server:

DECLARE @ident INT;
EXECUTE @ident = stp_TransferJob (plus other parameters)

Since it is more common to do this with error codes, you might prefer an output parameter:

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

Then when you call the procedure you add a declared parameter with an output keyword. Please see msdn

I would the output parameter here. The return from a stored procedure is intended to be used to indicate a status of the execution. It is NOT intended to return data. That is what the OUTPUT parameters are for. — Sean Lange, Aug 19 '16 at 14:00
As posted this isn't going to work anyway. You need to get the identity on the remote server. And using @@IDENTITY is very rarely a good choice. SCOPE_IDENTITY is almost always a better choice. — Sean Lange, Aug 19 '16 at 14:02
True, he would have to create a stored procedure on the linked server which will return SCOPE_IDENTITY. Addi, are you permitted to create procedures on the linked server? — PacoDePaco, Aug 19 '16 at 14:04
yes. but how to get i for run my other query i edit my post query check — Addi Khan, Aug 19 '16 at 14:09
INSERT INTO [111.163.103.122].Taxi.dbo.booking_ViaLocations ( BookingId,ViaLocTypeId,ViaLocTypeLabel,ViaLocTypeValue,ViaLo‌cId,ViaLocValue,ViaL‌ocLabel where bookingId = @ident_out) how to add where caluse this statement — Addi Khan, Aug 19 '16 at 14:19

SQL Server SCOPE_IDENTITY does not run linked server

1 Answers1