Can Transactional Replication With Updateable Subs Re Sync Automatically?

• Jamie Batiste

  SSCommitted

  Points: 1880

  More actions

  March 26, 2012 at 5:14 am

  #258244

  Hi,

  We have a fairly simple setup. Data on client X goes to client Y, however there are some articles that are filtered from Y.

  Table A

  ID Name Value Can_Replicate

  1 'A' 234 1

  2 'B' 0.22 0

  3 'C' 10.4 0

  4 'D' 123 0

  So in the example above I can see A exists on server Y (filtered on Can_Replicate).

  If I add another value set 5,'C',200,1 - the data on Y doesn't include it -filters seem to be set when the initial snapshot takes place.

  I know resyncing the data with a snapshot would work, but you can't schedule a snapshot as it fails because the tables are 'used in replication'! How do I resync the db's to cater for the new filters?

  Just for reference the resync doesn't have to happen per transaction change, just once a day.

  Cheers

  Jamie

• Jamie Batiste

  SSCommitted

  Points: 1880

  More actions

  March 29, 2012 at 2:47 am

  #1466235

  Hmm - sorry oversimplified my query - that example should work normally. Actually the "Can_Replicate" flag is stored in a seperate table.

  Table_B

  ID Can_Replicate

  1 1

  2 0

  3 0

  4 0

  etc

  The article filter is:

  Select * From Table_A Where ID in (Select ID From Table_B Where Can_Replicate=1)

  My issue is that new rows added to table B don't automatically replicate and other than a manual resync I can't get them to come across.

• Jamie Batiste

  SSCommitted

  Points: 1880

  More actions

  March 29, 2012 at 9:53 am

  #1466458

  Just re read in BOL that filters are Static in transactional replication (nice and clear on the 2012 version) - am now trying to set the subscription to dirty with sp_reinitpullsubscription.