September 17, 2004 at 8:54 am
I have a SQL DTS job that runs every night to populate the FaxList table of a publication with data from our Contact Management database.
The job has failed recently because the Identity range is not large enough. The publication automatically gave the article a range of 5000. Currently there are 5010 records in the Contact management database and it grows daily.
SQL automatically creates a check constraint on the FaxList table that limits it to a range of 5000 rows.
I have changed the FaxList article to allow a range of 9000 rows and manually altered the check constraint to allow 9000 rows, however after the DTS job runs, the check constraint is reset to a range of 5000 I have also unchecked "Enforce constraint for INSERTs and UPDATEs", however after the DTS job runs, it is checked again by SQL.
Both of the workarounds above work one time, then they get automatically set back to a range of 5000.
I noticed in the subscribers Msrepl_identity_range table, the range is still set to 5000 even after it has been changed on the Publisher to 9000.
My question: How do we get the ranges expanded to both the publisher and the subscribers and make them stick?
Do we need to manually edit the Msrepl_identity_range table and change the range field to 9000 on all subscribers?
I have thought of dropping the article from the publication and then adding it back in with a larger identity range.
I know if I do that, I must drop all subscriptions first, then re-create them.
If I do that, it will push all of the data out again. (The subscribers already have the data). Should I check "Keep the existing table unchanged" under Name conflicts on all articles except the FaxList?
It takes over 10 hours to push the snapshot owver the WAN to the subscribers, I would like to only have the faxlist article pushed while maintaing the existing data in the other table on the subscribers in order to minimize the time it takes to apply the snapshot.
Any help would be appreciated.
Thanks,
September 19, 2004 at 12:06 am
I figured it out. I think it is a bug in the stored procedure sp_changemergearticle
When the Publisher Identity range is changed in the Publication properties GUI, the new range is updated in the publication database's MSrepl_identity_range table, but NOT in the distribution database's MSrepl_identity_range table.
Changing the range for the subscribers does however change the MSrepl_identity_range table in both the Publication and the Distribution databases.
The work around would be to edit the MSrepl_identity_range table in the Distribution database to update the desired range for the publisher field (pub_range)
The constraint on the Faxlist table is now correctly generated containing 9000 rows.
([FaxListId] > 9646000 and [FaxListId] < 9655000)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply