sp_reinitsubscription multiple subscribers

• dmike

  SSC-Addicted

  Points: 400

  More actions

  September 10, 2013 at 5:17 pm

  #277845

  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.

• Dennis Post

  SSCarpal Tunnel

  Points: 4473

  More actions

  September 11, 2013 at 2:45 am

  #1649142

  Hi,

  Depending on your criteria you could do something like:

  DECLARE@SQL Varchar(MAX)

  SELECT@SQL = ''

  --SELECTP.Name PubName, S.SrvName SubScriber, S.Dest_DB

  SELECT@SQL = @SQL +

  '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

  WHERES.SrvName <> ''

  AND S.SrvName NOT IN ('List', 'of', 'subscribers', 'to', 'filter', 'out')

  GROUPBY P.Name, S.SrvName, S.Dest_DB

  PRINT @SQL

  --EXEC (@SQL)

  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.[/url]

  "Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort