Chapter 8. SQL Remote Design forAdaptive Ser verEnter prise
♦Reportthe name of the remote user whose update failed, along with the
lostand won names.
Theconflict resolution
objects Inthis case, the ConflictLog table has an additional column to record the
userID of the remote user. Thetable is as follows:
CREATE TABLE ConflictLog (
conflict_key numeric(5, 0) identity not null,
lost_name char(40) not null ,
won_name char(40) not null ,
remote_user char(40) not null ,
primary key ( conflict_key )
)
Thestored procedure is more elaborate. Asthe update will be disallowed,
ratherthan allowed, the lost_name value now refers to the value arriving in
themessage. Itis first applied, but then the conflict resolution procedure
replacesit with thevalue that was previously present.
Thestored procedure uses data from the temporary table #remote. Inorder
tocreate a procedure that references a temporary table you first need to
createthat temporary table. Thestatement is as follows:
CREATE TABLE #remote (
current_remote_user varchar(128),
current_publisher varchar(128)
)
Thistable is created in TEMPDB, and exists only for the current session.
TheMessage Agent creates its own #remote table when it connects, and uses
itwhen the procedure is executed.
CREATE PROCEDURE ResolveCustomer
AS
BEGIN
DECLARE @cust_key CHAR(12)
DECLARE @lost_name CHAR(40)
DECLARE @won_name CHAR(40)
DECLARE @remote_user varchar(128)
-- Get the name that was present before
-- the message was applied, from OldCustomer
-- This will "win" in the end
SELECT @won_name=name,
@cust_key=cust_key
FROM OldCustomer
-- Get the name that was applied by the
-- Message Agent from Customer. This will
-- "lose" in the end
SELECT @lost_name=name
FROM Customer
WHERE cust_key = @cust_key
171