Merge Replication Issues

Simon,
using DBCCCHECKIDENT to reseed is not recommended for the
reasons you indicate in your second post - the range is
validated using a check constraint that is not updated.
Usually when people "set the identity ranges to something
high" they are talking about the range and not the seed.
It looks like your range is 100 while it is more usual to
have a range so large that no change is required -
10,000, 100,000 etc. If you use an int datatype then you
have +/- 2 billion so there's no reason to run out, and
if you have loads of subscribers then bigint will work
instead. I use manual range management, but an
appropriate use of the range for automatin management
amounts to the same thing really.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thank you very much for the reply Paul.
I'm not sure I entirely follow. I can see 2 constraints on the table with
issues:
([VID] > 180000 and [VID] < 240000)
And also:
([VID] > 200 and [VID] < 60200)
(Those are my ranges for the database with more issues than the other ones)
Should there only be one constraint? Is this an error from when I deleted
the publication and recreated it?
Also what are these constraints validating. Is it validating inserts via
web interface, to my publisher, or is it validating range from my handhelds.
I'm guessing only the publisher since its a hard set range, and not one per
handheld. Or is there 2 constraints because I had 2 handhelds?
Clarity on this would certainly help understanding what the constraints do
and what I can do about it.
Also, here is my "article identity range" configuration from this table,
perhaps you could tell me if it is matching up with the constraint properly.
Next Range Starting Value: 300000
Identity Increment: 1
Range Size at Publisher: 60000
Range Size at Subscriber: 60000
Assign a new range when this percent of values is used: 80
But DBCC CHECKIDENT ('V', NORESEED) returns:
Checking identity information: current identity value '180026', current
column value '180026'.
I guess I'm a little confused as to what your saying and what I can do about
this.
I guess the bottom line is. Is my configuration workable, and can I modify
to fix this issue, or do I need to figure out how to scrap it, and all merge
settings, and start again with different settings? The problem with
handhelds replicating, is I can't set hard set ranges like many people
suggest. It's almost like an anonymous replication, where the database gives
it auto ranges.
Thanks again for the response. I really do appreciate it.
Take care,
Simon
"Paul Ibison" wrote:

> Simon,
> using DBCCCHECKIDENT to reseed is not recommended for the
> reasons you indicate in your second post - the range is
> validated using a check constraint that is not updated.
> Usually when people "set the identity ranges to something
> high" they are talking about the range and not the seed.
> It looks like your range is 100 while it is more usual to
> have a range so large that no change is required -
> 10,000, 100,000 etc. If you use an int datatype then you
> have +/- 2 billion so there's no reason to run out, and
> if you have loads of subscribers then bigint will work
> instead. I use manual range management, but an
> appropriate use of the range for automatin management
> amounts to the same thing really.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Yes - as far as I know there should only ever be one
check constraint which is being enforced, and
sp_adjustpublisheridentityrange removes the old one,
replacing it with a new check which enforces the new
range. This constraint is on any data added to the table,
regardless of the source.
Your couurent setup seems OK. The value 180026 is in your
180000 to 240000 range and if 6000 is a large enough
range that'll never need incrementing then fine. If not,
I'd reconfigure with something much larger then you can
forget about it all. If it is OK, then I'd also remove
the unnecessary check constraints that are orphans from
earlier on.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Alright thats the answer I needed. Thanks a ton Paul.
A few questions left ;)
Will the publisher, give itself new ranges when it hits the max if a
replication doesn't occur? If not, Then the publisher definately needs a
range that it will never reach, correct? Since inserts for the publisher are
coming from a website.
Since as you pointed out, my range seems to be 100 identities, but in my
replication it says 60,000. How to I change this. Do I go right into the
constraint and change the range value, or do I have to redo the publication?
Or is there a stored procedure to run for this.
Is there a table somewhere I can go to see the ranges, and current values.
For example, what is the publishers start/end range, and where is its current
next number going to be.
Still a little confused as to why the constraint says 100 items in the
range, when I set the publication at 60,000.
Thanks a ton again for all the help,
Simon
"Paul Ibison" wrote:

> Yes - as far as I know there should only ever be one
> check constraint which is being enforced, and
> sp_adjustpublisheridentityrange removes the old one,
> replacing it with a new check which enforces the new
> range. This constraint is on any data added to the table,
> regardless of the source.
> Your couurent setup seems OK. The value 180026 is in your
> 180000 to 240000 range and if 6000 is a large enough
> range that'll never need incrementing then fine. If not,
> I'd reconfigure with something much larger then you can
> forget about it all. If it is OK, then I'd also remove
> the unnecessary check constraints that are orphans from
> earlier on.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||The merge agent kicks off sp_adjustpublisheridentityrange
but I'd make sure this isn't used and have large ranges.
It seems to me that you have a large range allocated as
you're in the 180000 to 240000 bracket. the old check
constraint that implies a small range can be ignored and
deleted.
To see more details about the identities selected, you
cal look at MSrepl_identity_range. However I am not aware
of a mechanism to determine the next range to be
allocated - as far as I know this is internally
calculated. To follow the logic you could check out the
system stored procedure referred to above.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)