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


AS


         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


 


Thanks,


Laxmi Narsimha Rao ORUGANTI