Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

sp_reinitsubscription multiple subscribers Expand / Collapse
Posted Tuesday, September 10, 2013 5:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 2:56 PM
Points: 8, Visits: 95
we are trying to target the re-initialization of a subscription at certain subscribers at the same time.

the following has been successful for a single subscriber:
sp_reinitsubscription @publication = 'my_publication',
@subscriber = 'subscriber1', @destination_db = 'dest_dbase',
@invalidate_snapshot = 1

substituting 'all' works for all the subscribers:
sp_reinitsubscription @publication = 'my_publication',
@subscriber = 'all', @destination_db = 'dest_dbase',
@invalidate_snapshot = 1

is there a syntax that allows for specifying multiple subscriptions but not all - say defining 10 subscriptions out of 20?

any help would be appreciated.
Post #1493398
Posted Wednesday, September 11, 2013 2:45 AM


Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
Points: 175, Visits: 547

Depending on your criteria you could do something like:

--SELECT P.Name PubName, S.SrvName SubScriber, S.Dest_DB
'sp_reinitsubscription @publication = '''+P.Name+
''', @subscriber = '''+S.SrvName+
''', @destination_db = '''+S.Dest_DB+
''', @invalidate_snapshot = 1' + CHAR(13)
FROM [DBO].[sysPublications] P
INNER JOIN [DBO].[sysArticles] A ON P.PubID = A.PubID
INNER JOIN [DBO].[sysSubscriptions] S ON A.ArtID = S.ArtID
WHERE S.SrvName <> ''
AND S.SrvName NOT IN ('List', 'of', 'subscribers', 'to', 'filter', 'out')
GROUP BY P.Name, S.SrvName, S.Dest_DB

Better yet, create a table of subscriber servers you wish to reinitiate and join to it instead of using IN ('blah', 'blah')

If more complex requirements are needed, alter the statement to use in a cursor.

Hope this helps.

For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Post #1493502
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse