User's Manual

Chapter 8. SQL Remote Design for Adaptive Server Enterprise
♦ Report the name of the remote user whose update failed, along with the
lost and won names.
The conflict resolution
objects
In this case, the ConflictLog table has an additional column to record the
user ID of the remote user. The table 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 )
)
The stored procedure is more elaborate. As the update will be disallowed,
rather than allowed, the lost_name value now refers to the value arriving in
the message. It is first applied, but then the conflict resolution procedure
replaces it with the value that was previously present.
The stored procedure uses data from the temporary table #remote. In order
to create a procedure that references a temporary table you first need to
create that temporary table. The statement is as follows:
CREATE TABLE #remote (
current_remote_user varchar(128),
current_publisher varchar(128)
)
This table is created in TEMPDB, and exists only for the current session.
The Message Agent creates its own #remote table when it connects, and uses
it when 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