July 16, 2012 at 1:53 pm
We have a simple setup, Server A copies a subset of information to Server B, with a row level filter.
Replication works, but I need to refresh the contents of the row level filters everynight as they do change (based on table contents).
What commands do I need to run to do this? Can't find on BOL/web
Reason I say is that I can force a snapshot generation and apply it, however it upsets the identity values. So first day it runs and I can insert new rows etc, following the "resync" I cannot insert new rows without "insert statement conflicts with check constraint "repl_identity_range_tran_xxxxxx"
July 19, 2012 at 2:36 am
Right more on this. It appears the target database was corrupt. It had remnants of snapshot replication that had been removed. I have run the exec sp_MSUNmarkreplinfo 'Table_Name' on each of the affected tables, but no joy. Ran exec sp_removedbreplication 'Database_Name','both' which cleared it out and resyncs now refresh the identity ranges correctly.
So the question is now, rather than dropping and recreating the replication topology each night to update filters, is there a way of replicating the "Reinitialise all subscriptions" in code? I have run profiler whilst doing this and can't identify the golden procedure call!
July 19, 2012 at 4:16 pm
Even more...
Right I can manually run Reinitialize All Subscriptions, Mark and Create New Snapshot in SSMS which refreshes the data (snapshot) and resets the identity ranges if required, but running these stored procedures generates a new identity range...not what we want. E.g. from SSMS it only generates a new range of identities on a table if required. with the following SPs the range on the subscriber is incremented by the number in the range e.g. 1000 regardless if its required or not.
exec sp_reinitsubscription
exec sp_startpublication_snapshot
Anyone shed any light on that?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply