HOWTO: Pending Changes for a Merge Subscription (Client Side)

 

Often there will be need to find out if there are any pending changes on client side to upload. Note that, It is always better to avoid empty sync both from the resource (network bandwidth, web server, SQL Server) usage and performance. This blog post discusses on how to find if there are any pending changes to be uploaded for a particular subscription.

In the earlier blog post “Merge Replication system tables”, we discussed on how to find the list of tables/articles for a particular publication/subscription.

For each table in the *interested* merge subscription,

                SELECT MAX(__sysIG) AS MaxIG FROM UserTable;

                SELECT MAX(__sysCG) AS MaxCG FROM UserTable;

                __sysMergeSubscriptions AS sms;

SQL Server 9.0 and 10.0 (SQL Server Codename YUKON and KATMAI) ,

If (MaxIG > sms.LastUploadedGen OR MaxCG > sms.LastUploadedGen), then there are pending changes for this User Table

SQL Server 8.0 (SQL Server Codename SHILOH) ,

If (MaxIG > sms.SentGen80 OR MaxCG > sms.SentGen80), then there are pending changes for this User Table

If none of the tables have pending changes, then user can avoid the empty sync. (Note: Finding the pending changes on SQL Server side for download is not covered)

 

<Update Type="Missing Information" Courtesy="Daniel Stolt">

This blog post covers detection of pending INSERTs and pending UPDATEs. And, does not cover detection of pending DELETEs. You can detect the pending DELETEs with the help of __sysDeletedRows table.

</Update>

 

Thanks,

Laxmi Narsimha Rao ORUGANTI