Wednesday, October 03, 2012 7:28 PMI 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.
Wednesday, October 03, 2012 7:47 PMModerator
Wednesday, October 03, 2012 8:29 PM
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 PMModerator
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.
- Edited by Brandon WilliamsMicrosoft Community Contributor, Moderator Wednesday, October 03, 2012 10:33 PM
- Proposed As Answer by Brandon WilliamsMicrosoft Community Contributor, Moderator Thursday, October 11, 2012 4:20 AM
- Marked As Answer by Maggie LuoMicrosoft Contingent Staff, Moderator Monday, October 15, 2012 10:24 AM