How to reinitialize a merge subscription from publisher - SQL 2005 using TSQL script?
Lately we have had a requirement to re-initialize handful of subscriptions to a publication within the merge replicaton setup. I was looking for a way out to achieve these operations programmatically using a TSQL script. I'm aware that using SP_REINITMERGEPULLSUBSCRIPTION procedure is possible, but the majority of subscribers in this replication will be in disconnected state to perform such operation. When they connect the process to publication will be very busy and have very limited time to achieve using GUI (SSMS) based approach.
After a bit of research with the web and BOL I found this can be used quickly using as follows as a scheduled job:
exec sp_reinitmergesubscription @publication=<Publication Name>, @subscriber=<Subscriber Name>, @subscriber_db = <Subscriber DB>, @upload_first = <TRUE|FALSE>
BOL refers: At the Subscriber on the subscription database, execute sp_reinitmergepullsubscription (Transact-SQL). Specify @publisher, @publisher_db, and @publication. To upload changes from the Subscriber before reinitialization occurs, specify a value of true for @upload_first. This marks the subscription for reinitialization the next time the Merge Agent runs.
You can get the details of the publishers as per the code example in the BOL:
DECLARE @publication AS sysname;
SET @publication = N'AdvWorksSalesOrdersMerge';
USE [AdventureWorks]
EXEC sp_helpmergepublication @publication = @publication;
GO
Further information can be obtained using HOW-TO topics in the books online for SQL Server 2005 *updated.