Hello everyone,
i am trying to work with linked servers...
The local server is the one used by UPS worldwide software (it is Microsoft SQL Server Desktop Engine, v 8.00.2050). The linked server is 2005 MS server.
on update/insert event for some table i hooked up the trigger that connects to linked server and updates the record in some DB.
To test, i create a stored procedure that updates the record in UPS table that has that trigger. The table is updated, the trigger works, no updates happens if....
if use 'inserted' table to get the updated record, the fields are null. If i hardcode the parameters, i see them reflected in the linked server (the table on linked server is updated)... I DON'T UNDERSTAND, why the number of records in inserted is equal to 0... so naturally my parameters stay equal to NULL... here is the code:
STORED PROCEDURE:
AS
SET NOCOUNT ON
UPDATE upswsdb.dbo.calPackage
SET Sm_trackingNo = '11Z73X1A60343030610'
WHERE m_primaryKey = 2
RETURN
TRIGGER:
COMMIT TRANSACTION
DECLARE @TrackingNumber nvarchar(30)
DECLARE @TrackingOrderID nvarchar(35)
SET @TrackingNumber = (SELECT Sm_trackingNo FROM inserted)
SET @TrackingOrderID = (SELECT Sm_referenceText0 FROM inserted)
--SET @TrackingNumber = (SELECT COUNT(*) FROM inserted)
--SET @TrackingOrderID = (SELECT COUNT(*) FROM deleted)
PRINT @TrackingNumber
PRINT @TrackingOrderID
--SET XACT_ABORT ON
UPDATE RemoteServer.DB.dbo.OrderTable
SET ShippingTrackingNumber = @TrackingNumber
WHERE OrderId = @TrackingOrderID
BEGIN TRANSACTION
Thanks for any help to resolve it...
HF
| 
