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


Earlier in the other blog article we discussed on “HOWTO: Pending Changes for a Merge Subscription (Client Side)” and this article will focus on finding the pending changes on server side for download. This blog article requires a little understanding of Remote Data Access (RDA). Note that, RDA does NOT need any extra configuration and can use/piggyback on the current merge replication configuration both on IIS and in SQL Server.

1) Create a stored procedure on server side like the one mentioned below


CREATE PROC sp_IsThereAnyChangesToReplicateServerSide


         DECLARE @AnythingToReplicateServerSide int

        DECLARE @pending_deletes int

        DECLARE @pending_ins_and_upd int

         SET @AnythingToReplicateServerSide = 0

         CREATE TABLE #My_PendingChanges (destination_server sysname, pub_name sysname, destination_db_name sysname, is_dest_subscriber bit, article_name sysname, pending_deletes int, pending_ins_and_upd int)

         INSERT INTO #My_PendingChanges EXEC sp_showpendingchanges @publication = 'Repl_Mobile_Publication'

         SELECT @pending_deletes = pending_deletes, @pending_ins_and_upd = pending_ins_and_upd FROM #My_PendingChanges

         IF (@pending_deletes <> 0 OR @pending_ins_and_upd <> 0)

                   SET @AnythingToReplicateServerSide = 1

         DROP TABLE #My_PendingChanges

         SELECT @AnythingToReplicateServerSide


a. You can make it smarter by specifying a @destination_server, @publication to sp_ShowPendingChanges.

2) Call this stored procedure from client using RDA.Pull which will create local table



Laxmi Narsimha Rao ORUGANTI