Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Article's "Updates" count incorrect in Synchronization History

Con risposta Article's "Updates" count incorrect in Synchronization History

  • Wednesday, October 03, 2012 7:28 PM
     
     
    Sign In to Vote
    0
    I have an application that will insert records into a table that is part of Merge replication.  The application does not have any way to update a record.  No-one but the application is accessing the database.  When I look in the Replication Monitor, I see the Inserts which look to have the correct number of Inserted records, but there are hundreds of records that have been recorded as being updated.  This is a bidirectional replicated table but the databases on the Publisher nor the distributer have a means to update records.  I am using a custom resolver for the article.  I am using the Microsoft SQL Server DATETIME (Later Wins) Conflict Resolver.
     

All Replies

  • Wednesday, October 03, 2012 7:47 PM
    Moderator
     
     
    Sign In to Vote
    0
    You say the application does not have a way to update records but you are using Microsoft SQL Server DATETIME (Later Wins) Conflict Resolver which supports update conflicts.  Can you clarify this?

    Brandon Williams (blog | linkedin)

     
  • Wednesday, October 03, 2012 8:29 PM
     
     
    Sign In to Vote
    0

    Correct, the intial set up of the article was set up so that it would using the conflict resolver of DATETIME (Later Wins). This was done should any backend datbase updates be made.  No updates have been made yet. 

    The table in question is a login history table.  When a user logs into our application it Inserts a record into our login history table with the current DateTime.  Our application has one stored procedure that it uses to accomplish this.  I have verified that the stored procedure is only inserting records.

    When merge replication runs, I would expect to only see counts for Inserted records.  However, I am seeing counts for Updates as well as the counts for the inserted records.

     
  • Wednesday, October 03, 2012 10:25 PM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0

    Honestly, it will be difficult to identify the rows that were updated postmortem and who they came from.  You can get some information using sp_showrowreplicainfo and sp_showlineage but I don't think that will work for you in this case.

    I will share with you an auditing technique I use to troubleshoot these issues.

    /***********************************

This is script to implement audit trigger of update
It is based on a generic scenario for a table with four columns 
Col1, Col2, Col3, Col4.

It will insert to a table called source_audit.

Test first to be sure it is working as expected!

After the problem occurs, export the contents of the source_audit table.

************************************/



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[source_audit]') AND type in (N'U'))
DROP TABLE [dbo].[source_audit]


CREATE TABLE [dbo].[source_audit](
	[tstamp] datetime NULL, 
    [ProgramName] nvarchar(128) NULL, 
    [hostname] nvarchar(128) NULL, 
    [suser] nvarchar(128) NULL,
	[col1] int ,
	[col2] nchar(10) NULL,
	[col3] datetime NULL,
	[col4] binary NULL,
	[actiontype] char(2) NULL,
    [inputbuffer] nvarchar(255) NULL,
    [spid] int NULL
)
GO

-------------------------------------------
-- update trigger

--Delete Trigger if Exists
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[audit_source_UPD]'))
DROP TRIGGER [dbo].[audit_source_UPD]
GO

CREATE TRIGGER audit_source_UPD
on Table_1
FOR UPDATE
AS
DECLARE @command NVARCHAR(255)
 
if 0 = (select count(*) from inserted) return
 
CREATE TABLE #InputBuffer ([eventtype] NVARCHAR(30), [parameters] INT, [eventinfo] NVARCHAR(255))
INSERT INTO #InputBuffer exec('DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS')
SELECT @command=eventinfo from #InputBuffer

INSERT INTO source_audit
SELECT getdate(), app_name(), host_name(), suser_name(), col1, col2, col3, col4, 'UD', @command, @@SPID
FROM deleted
INSERT INTO source_audit
SELECT getdate(), app_name(), host_name(), suser_name(), col1, col2, col3, col4, 'UI', @command, @@SPID
FROM inserted
GO

    You will have to modify the script to adjust the name of the table being audited and the relevant columns that you think should be included in the audit data.  Usually just the primary key column is enough.

    The script will give you the getdate(), app_name(), host_name(), suser_name(), and command which should help us identify precisely where the updated rows are coming from and what is getting updated.

    Brandon Williams (blog | linkedin)