Merge replication, Identity not for replication, identity range

Hi all,
When setting up a (merge) publication using the wizard, you can click
the three-dotted button in the "specify articles" and then setup the
identity range for publisher and subscribers.
While this is an interesting option, I see that the default is not
checked (yes, I have the identity column setup as not for replication).
Now my database only has about 50 tables but nevertheless, not my
favorit waste of time to call up each table one by one and adjust these
values one by one (tabbing to the correct tab, checking the option,
adjusting the range-values).
So, is there any way to get this checked by default (that would be one
step forward) and preferably also adjust the values while we're at it?
Many thanks in advance,
Ferry
Ferry,
there's no way that I'm aware of. One posibility is to script out the
publication and use a find and replace in notepad to make the alteration
then drop the original and recreate the publication using this script -
admittedly not nice but would save a lot of leg-work.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||From Paul Ibison :
> Ferry,
> there's no way that I'm aware of. One posibility is to script out the
> publication and use a find and replace in notepad to make the alteration then
> drop the original and recreate the publication using this script - admittedly
> not nice but would save a lot of leg-work.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
Thanks Paul. I guess I'll have a look and see if I can do either that
or perhaps find some 'backdoor'...
Ferry
|||From Ferry :
> From Paul Ibison :
> Thanks Paul. I guess I'll have a look and see if I can do either that or
> perhaps find some 'backdoor'...
> Ferry
Having said that, I started a more detailed search on Google and came
up with this one. Going to check this later:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*
'************************************************* **
' Purpose :Add Identity range to the user defined Tables under a given
publisher
' Inputs :Publisher Name and Identity Range
' Returns :None
' Author :Mahesh M Kodli
'************************************************* ***
CREATE PROCEDURE AddMergeArticle
@.pPublisherName VARCHAR(255),
@.pIdentityRange BIGINT
As
DECLARE @.tRV INT
DECLARE @.tArticle VARCHAR(255)
SET @.tRV = 0
DECLARE Merge_Article_Cursor CURSOR FOR
--Get all the user defined Tables for which to add Identity Range
SELECT TABLE_NAME TableName FROM INFORMATION_SCHEMA.TABLES WHERE
rtrim(ltrim(table_type))='BASE TABLE'
AND TABLE_NAME Not like 'Conflict%' AND TABLE_NAME Not like
'dtproperties'
AND TABLE_NAME Not like 'sys%' AND TABLE_NAME Not like 'MS%'
OPEN Merge_Article_Cursor
FETCH NEXT FROM Merge_Article_Cursor INTO @.tArticle
WHILE @.@.FETCH_STATUS = 0
BEGIN
--Check if the Table has identity column and accordingly set the Auto
--Identity Range to TRUE or FALSE Before adding Identity Range to the
--article
IF OBJECTPROPERTY ( OBJECT_ID(@.tArticle), 'TableHasIdentity') = 1
BEGIN
IF NOT EXISTS (SELECT * FROM sysmergeextendedarticlesview WHERE name
= @.tArticle AND pubid IN (select pubid FROM sysmergepublications WHERE
name like @.pPublisherName AND UPPER(publisher)=UPPER(@.@.servername) and
publisher_db=db_name()))
BEGIN
--Use the System stored procedure add merge article to add Identity
range for -- each article
EXECUTE sp_addmergearticle @.publication = @.pPublisherName, @.article =
@.tArticle, @.source_owner = N'dbo', @.source_object = @.tArticle, @.type =
N'table', @.description = null, @.column_tracking = N'true',
@.pre_creation_cmd = N'drop', @.creation_script = null, @.schema_option =
0x000000000000CFF1, @.article_resolver = null, @.subset_filterclause =
null, @.vertical_partition = N'false', @.destination_owner = N'dbo',
@.auto_identity_range = N'true', @.pub_identity_range = @.pIdentityRange,
@.identity_range = @.pIdentityRange, @.threshold = 80,
@.verify_resolver_signature = 0, @.allow_interactive_resolver = N'false',
@.fast_multicol_updateproc = N'true', @.check_permissions =
0,@.force_invalidate_snapshot=1
END
END
ELSE
BEGIN
IF NOT EXISTS (SELECT * FROM sysmergeextendedarticlesview WHERE name
= @.tArticle AND pubid IN (select pubid FROM sysmergepublications WHERE
name like @.pPublisherName AND UPPER(publisher)=UPPER(@.@.servername) and
publisher_db=db_name()))
BEGIN
EXECUTE sp_addmergearticle @.publication = @.pPublisherName, @.article
= @.tArticle, @.source_owner = N'dbo', @.source_object = @.tArticle, @.type
= N'table', @.description = null, @.column_tracking = N'true',
@.pre_creation_cmd = N'drop', @.creation_script = null, @.schema_option =
0x000000000000CFF1, @.article_resolver = null, @.subset_filterclause =
null, @.vertical_partition = N'false', @.destination_owner = N'dbo',
@.auto_identity_range = N'False', @.pub_identity_range = NULL,
@.identity_range = NULL, @.threshold = NULL, @.verify_resolver_signature =
0, @.allow_interactive_resolver = N'false', @.fast_multicol_updateproc =
N'true', @.check_permissions = 0,@.force_invalidate_snapshot=1
END
END
FETCH NEXT FROM Merge_Article_Cursor INTO @.tArticle
END
CLOSE Merge_Article_Cursor
DEALLOCATE Merge_Article_Cursor
IF (@.@.ERROR <> 0)
BEGIN
SELECT @.tRV = -95 --UnSuccessful
GOTO XIT
END
XIT:
RETURN @.tRV
|||Ferry,
thanks for the heads up. I've found the link
http://www.devarticles.com/c/a/SQL-S...2000-Part-2/3/
and will add it onto my site. The only problem with the proc above is that
it assumes you want to replicate all tables, so there's room for another
(simpler) version which takes a tablename as a third parameter.
To have this occur without any intervention such as using Mahesh's script
you would have to edit the stored procedure sp_addmergearticle itself to
hardcode the automatic range management. This is a posibility but which
obviously invalidates support agreements - depends on how confident you are
of getting it spot on
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

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)

Merge Replication Indentity Semi-Automatic Range allocation

I have a publisher and 2 subscribers on the network and the table I am merge
replicating has an identity field, with NOT FOR REPLICATION set, to allow me
to use identiy ranges which it does very well, however I have 1 problem and 1
question.
Problem 1
When I reach my threshold (80%) it stops that server from creating new
records and an error is produced until the merge agent has run and the best
that can be is once a minute. How can I configure the system to not wait for
the next time the merge agent runs, as this system will be creating 100's of
transactions a minute.
Question 1
If the threshold is set to 80% and I have a range of 1000, then when it gets
to 800 it stops and sets the next id number to the first in the next block
meaning that the remaining 200 are redundant.
I would have expected that when the threshold is reached that until the new
numbers are received then the server would use the range after the threshold
until it receives the next range otherwise why not always set the threshold
to 100% as it just errors when it hits the threshold anyway? Looking at BOL
it says for transactional then the agent runs continously, but with merge no
such statement, and as its merge based the subscribers could be offline for
some time, so shouldn't there be a more controlled process when they reach
their threshold.
I think I am missing an option on the publication maybe
thanks in advance.
Neil.
Thanks for the kb's Paul but the threshold value seems to be completely
pointless to me
In KB 322910 it says
Before you implement ranged identity management with merge replication, you
must first consider how many inserts will be performed by users and how
frequently they will be merging their changes. The primary goal when defining
the identity options for an article is to make the ranges large enough that
the Subscriber will not run out of values before the next merge.
Which suggests to me that the subscribers should get a new block of identity
values when they merge, however in my testing, a new range is only produced
when the subscriber has reached the threshold level and an error is produced
and they are not allowed to create any new records until the merge which does
mean that the threshold value is pointless and should be set to 99% or 100%
I hope this makes sense.
Neil.
"Paul Ibison" wrote:

> Neil,
> there are some problems with what you are trying to do
> that are documented:
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;304706&Product=sql2k
> http://support.microsoft.com/?kbid=310540
> I would suggest one of 2 options:
> (a) set the range size so large that it will never need
> to reseed
> or
> (b) use manual range management and a simple algorithm eg
> in the simplest case odd and even values for the case
> where there is just a publisher and subscriber.
> Regards,
> Paul Ibison
> (The ONLY sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Neil,
if you are regularly synchronizing, a new range will be
requested as a part of the synchronization process. This
is before the check constraint that controls the upper
boundary reports an error when a subscriber who performs
an insert reports an error. So although there will be
unused identity values, this should lead to a smooth
running of the subscriber.
HTH,
Paul Ibison
(The ONLY sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I think your problem is because you are over running your threshold in the
batch. The way it is designed is that you pick a range which is
representative of the largest batch which could occur on your
publisher/subscriber and then adjust it upwards so that the threshold lies
within this batch.
Here are some examples
1) update 10,000 rows in a batch, max range is 10,000 threshold is 80% -
results range exceeded transaction rolled back
2) update 10,000 rows in a batch, max range is 20,000 threshold is 50% -
threshold exceeded, range adjusted on publisher and subscriber
3) update 10,000 rows in a batch, max range is 20,000 threshold is 80% -
threshold not exceeded, with first batch. range not adjusted on publisher.
Second batch runs, range exceeded, transaction rolled back.
4) update 10,000 rows in a batch, max range is 100,000, threshold is 80%,
threshold not exceeded until the 8th run, range adjusted on publisher and
subscriber, AND the publisher can still accept to more 10,000 batches before
blowing up.
The way most dba implement this is the set it and forget it philosophy. They
look at their publisher and subscriber and they parcel out the ranges that
they believe will not be exceeded during the lifetime of their replication
solution, and they don't have to worry about it.
One more point when you run into problems with automatic identity range
management you can use the following proc to fix things.
sp_mScheckidentityrange with the @.checkonly parameter having a value of 1
Some people have complained to me that the @.checkonly parameter is ignored
and the range is not updated. I havent' been able to repro this yet, but you
can always sp_MSadjustmergeidentity directly.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Neil" <Neil@.discussions.microsoft.com> wrote in message
news:7A350532-D333-4436-9035-87A376B935BC@.microsoft.com...
> Thanks for the kb's Paul but the threshold value seems to be completely
> pointless to me
> In KB 322910 it says
> Before you implement ranged identity management with merge replication,
you
> must first consider how many inserts will be performed by users and how
> frequently they will be merging their changes. The primary goal when
defining
> the identity options for an article is to make the ranges large enough
that
> the Subscriber will not run out of values before the next merge.
> Which suggests to me that the subscribers should get a new block of
identity
> values when they merge, however in my testing, a new range is only
produced
> when the subscriber has reached the threshold level and an error is
produced
> and they are not allowed to create any new records until the merge which
does
> mean that the threshold value is pointless and should be set to 99% or
100%[vbcol=seagreen]
> I hope this makes sense.
> Neil.
> "Paul Ibison" wrote:

merge replication identity range troubles

I have the following problem with merge replication (hoping I can put it
down in a clear, probably not concise, way).
1) We have a merge replication setup between a publisher and a
subscriber.
2) I have set up the subscriber in such a way, that at initialisation,
it al ready had all schema information and data. Not all tables are
replicated.
3) Identity ranges have been put in place.
Over time the following changes came around
1) Changed columns in published articles
2) adjusted identity ranges
To make these changes happen the following steps had been taken:
1) Drop subscription
2) Remove article from publication
3) make changes to the table
4) made changes on subscriber database
5) re-added article to publication
6) adjusted identity ranges
7) ran snapshot agent to create new snapshot
8) ftp'd new snapshot to subscriber server
9) recreated the subscription
10) had the snapshot applied (but since I already had all the schema
changes on subscriber side aswell as all the data, I did not choose to
apply those options)
11) initialisation went ok, verification as well (notification through
net sends)
And everything seemed to be working smoothly after the updates.
But now: Word got in from the office using the subscriber database
that they couldn't create anymore documents. The cause was soon found: the
identity ranges for the tables containing the documents were full: no more
records could be added.
Simply giving a kick to the merge agent would have solved that, but
alas: it did not.
Further investigation showed a discrepancy between the set identity
ranges on publisher side, and the stored identity ranges in the
MSrepl_identity_range table on the subscriber side.
subscriber:
exec sp_MShelp_identity_property @.tablename = N'MSGattributes'
output:
replicated=1,
uto_identity_support=1
identity_incremental=1
current_identity=8200264
next_starting_seed=8200000
max_identity=8400000
publisher_range=40000
subscriber_range=20000
threshold=80
in_repulishing=1
First note: there's a typo in the stored procedure.. the
threshold is incorrect and the publisher/subscriber range is incorrect. After
checking the code it became clear why the publisher_range and subscriber range
are these low values: they're simply divided by 10.
Further more: info for this particular table from the
MSrepl_identity_range table:
objid=2086298492
next_seed=8200000
pubrange=null
range=200000
max_identity=8400000
threshold=99
current_max=8399999
Publisher side:
exec sp_MShelp_identity_property @.tablename = N'MSGattributes'
replicated=1
auto_id_incr=1
id_incr=1
cur_id=7693746
next_seed=8400000
max_id=2147483647
publisher_range=200000
subscriber_range=200000
threshold=99
in_repulishing=0
After some serious digging through google, newsgroups and
various messageboards, I could not quite find a similar case and thus
solution. I did come across some interesting tips and hints, tried a couple of
those (using sp_MScheckidentityrange, sp_MSadjustmergeidentity etc.. it
proved rather difficult to find some form of documentation about these
undocumented sp's..), but to no avail.
Finally I decided to upload the latest snapshot and re-initialize the
subscription (pull subscription btw, forgot to mention this).
The merge agent gave me a nice error message: could notinsert double record
due to unique constraint on the MSrepl_identity_range table. Kinda made
sense, so I emptied the table (whether this is a foolish thing or not,
I don't quite know, documentation is hard to find on this subject), which
seemed to do the trick. The snapshot would be applied.
State before snapshot:
Identity range information for the tables in question were incorrect (range
of 20k, should have been 200k).
State after snapshot:
MSrepl_identity_range table showed correct ranges.
But the problem persists: no new records can be added to the tables in
question.
So, most likely I've been overlooking some crucial steps,
did some stupid things or am just unlucky somewhere.
One more thing: I checked the constraints on the tables on both sides:
There was some double identity range constraint set on one of the tables, I
double checked again on both sides and removed the one with the deviating
constraint name.
I am in need of a solution to this problem: how to fix the identity ranges
so that record insertion is possible again, since the merge agent does not
seem to be re-adjusting the identity ranges, even though they're all full.
Not really looking forward to re-building the subscriber database from
scratch, transfer all the data, snapshot, recreate the subscription etc..
which would be my ultimate last resort (but perhaps a bit foolish as well?).
***
While reformatting this post taken from mcse.ms to a pseudo readable
usenet mail, the thought occured to me that I could just synchronize,
verify all, disable updates (somehow...), remove the subscription, run a
snapshot, try to clear up as much as possible on the subscriber side,
re-create the subscription and hope for the best?
Gerald Raucamp - http://www.cavey.nl
> After some serious digging through google, newsgroups and
> various messageboards, I could not quite find a similar case and thus
> solution. I did come across some interesting tips and hints, tried a couple of
> those (using sp_MScheckidentityrange, sp_MSadjustmergeidentity etc.. it
> proved rather difficult to find some form of documentation about these
> undocumented sp's..), but to no avail.
>
Having written this, and just checking out the new
groups-beta.google.com, I've come across some similar problems. Still
investigating the problem, but any input/help/directions/pointers are
welcome!
Gerald Raucamp - http://www.cavey.nl
|||have you reviewed these articles?
http://support.microsoft.com/default...b;en-us;304706
http://support.microsoft.com/default...b;en-us;324361
I also make the second step of my merge agent
sp_MScheckidentityrange @.pubid=pubid, @.checkonly=0
"Gerald Raucamp" <caveyREMOVE@.THISvengeance.et.tudelft.nl> wrote in message
news:slrncsgete.o86.cavey@.vengeance.et.tudelft.nl. ..[vbcol=seagreen]
couple of
> Having written this, and just checking out the new
> groups-beta.google.com, I've come across some similar problems. Still
> investigating the problem, but any input/help/directions/pointers are
> welcome!
> --
> Gerald Raucamp - http://www.cavey.nl
|||You should let replication handle all your identity ranges automatically.
You should never have to manually adjust identity ranges.
Jim.
"Gerald Raucamp" wrote:

> I have the following problem with merge replication (hoping I can put it
> down in a clear, probably not concise, way).
> 1) We have a merge replication setup between a publisher and a
> subscriber.
> 2) I have set up the subscriber in such a way, that at initialisation,
> it al ready had all schema information and data. Not all tables are
> replicated.
> 3) Identity ranges have been put in place.
> Over time the following changes came around
> 1) Changed columns in published articles
> 2) adjusted identity ranges
> To make these changes happen the following steps had been taken:
> 1) Drop subscription
> 2) Remove article from publication
> 3) make changes to the table
> 4) made changes on subscriber database
> 5) re-added article to publication
> 6) adjusted identity ranges
> 7) ran snapshot agent to create new snapshot
> 8) ftp'd new snapshot to subscriber server
> 9) recreated the subscription
> 10) had the snapshot applied (but since I already had all the schema
> changes on subscriber side aswell as all the data, I did not choose to
> apply those options)
> 11) initialisation went ok, verification as well (notification through
> net sends)
> And everything seemed to be working smoothly after the updates.
> But now: Word got in from the office using the subscriber database
> that they couldn't create anymore documents. The cause was soon found: the
> identity ranges for the tables containing the documents were full: no more
> records could be added.
> Simply giving a kick to the merge agent would have solved that, but
> alas: it did not.
> Further investigation showed a discrepancy between the set identity
> ranges on publisher side, and the stored identity ranges in the
> MSrepl_identity_range table on the subscriber side.
> subscriber:
> exec sp_MShelp_identity_property @.tablename = N'MSGattributes'
> output:
> replicated=1,
> uto_identity_support=1
> identity_incremental=1
> current_identity=8200264
> next_starting_seed=8200000
> max_identity=8400000
> publisher_range=40000
> subscriber_range=20000
> threshold=80
> in_repulishing=1
> First note: there's a typo in the stored procedure.. the
> threshold is incorrect and the publisher/subscriber range is incorrect. After
> checking the code it became clear why the publisher_range and subscriber range
> are these low values: they're simply divided by 10.
> Further more: info for this particular table from the
> MSrepl_identity_range table:
> objid=2086298492
> next_seed=8200000
> pubrange=null
> range=200000
> max_identity=8400000
> threshold=99
> current_max=8399999
> Publisher side:
> exec sp_MShelp_identity_property @.tablename = N'MSGattributes'
> replicated=1
> auto_id_incr=1
> id_incr=1
> cur_id=7693746
> next_seed=8400000
> max_id=2147483647
> publisher_range=200000
> subscriber_range=200000
> threshold=99
> in_repulishing=0
>
> After some serious digging through google, newsgroups and
> various messageboards, I could not quite find a similar case and thus
> solution. I did come across some interesting tips and hints, tried a couple of
> those (using sp_MScheckidentityrange, sp_MSadjustmergeidentity etc.. it
> proved rather difficult to find some form of documentation about these
> undocumented sp's..), but to no avail.
> Finally I decided to upload the latest snapshot and re-initialize the
> subscription (pull subscription btw, forgot to mention this).
> The merge agent gave me a nice error message: could notinsert double record
> due to unique constraint on the MSrepl_identity_range table. Kinda made
> sense, so I emptied the table (whether this is a foolish thing or not,
> I don't quite know, documentation is hard to find on this subject), which
> seemed to do the trick. The snapshot would be applied.
> State before snapshot:
> Identity range information for the tables in question were incorrect (range
> of 20k, should have been 200k).
> State after snapshot:
> MSrepl_identity_range table showed correct ranges.
> But the problem persists: no new records can be added to the tables in
> question.
> So, most likely I've been overlooking some crucial steps,
> did some stupid things or am just unlucky somewhere.
> One more thing: I checked the constraints on the tables on both sides:
> There was some double identity range constraint set on one of the tables, I
> double checked again on both sides and removed the one with the deviating
> constraint name.
>
> I am in need of a solution to this problem: how to fix the identity ranges
> so that record insertion is possible again, since the merge agent does not
> seem to be re-adjusting the identity ranges, even though they're all full.
> Not really looking forward to re-building the subscriber database from
> scratch, transfer all the data, snapshot, recreate the subscription etc..
> which would be my ultimate last resort (but perhaps a bit foolish as well?).
>
> ***
> While reformatting this post taken from mcse.ms to a pseudo readable
> usenet mail, the thought occured to me that I could just synchronize,
> verify all, disable updates (somehow...), remove the subscription, run a
> snapshot, try to clear up as much as possible on the subscriber side,
> re-create the subscription and hope for the best?
> --
> Gerald Raucamp - http://www.cavey.nl
>
|||On 2004-12-21, Hilary Cotter <hilary.cotter@.gmail.com> wrote:
> have you reviewed these articles?
> http://support.microsoft.com/default...b;en-us;304706
> http://support.microsoft.com/default...b;en-us;324361
> I also make the second step of my merge agent
> sp_MScheckidentityrange @.pubid=pubid, @.checkonly=0
>
Thanks for the links. I have checked out the articles but unfortunately
they don't seem to apply to my situation.
The identity ranges are adjusted on the publisher without much of a
hickup using the sp_adjustpublisheridentityrange call. The problem lies
at the subscriber side. Afaik. I'll give it a try though, I might be
overlooking something.
The second link concerning the insert trigger does not seem to be an
issue either. I will check any triggers on the offending tables tomorrow
for the "not for replication" directive. We had some troubles in the
past (oversight is the correct word) with insert/update triggers firing
when the Merge agent was updating info...
I was wondering though what the "in_republishing" column means in the
sp_MShelp_identityrange call. Does that mean the table/article is being
republished on the subscriber side? If so, that would mean something is
a miss, since currently there is only one publisher and one subscriber
(more might be added in the future, depends on available IT
infrastructures at the various branche offices).
I'll also add the second step to my merge agent and will try to get more
verbosity in the agent log file.
Gerald Raucamp - cavey@.vengeance.et.tudelft.nl
http://www.cavey.nl
|||Hi Jim,
On 2004-12-21, Jim Breffni <JimBreffni@.discussions.microsoft.com> wrote:
> You should let replication handle all your identity ranges automatically.
> You should never have to manually adjust identity ranges.
>
I currently have, when it's working, merge replication set to handle the
ranges automagically for me. Somehow though, the magic isn't working.
A short recap of my long opening mail:
There is a publisher and a subscriber, range handling set in the hands
of the merge agent.
Pull subscription, merge agent running on hte subscriber.
Inserts work fine on publisher and get replicated to the subscriber.
Inserts on the subscriber fail with the nice message:
The identity range managed by replication is full and must be updated
by a replication agent. The INSERT conflict occurred in database
'ocs', table 'MSGattributes', column 'pkCode'.
Sp_adjustpublisheridentityrange can be called to get a new identity
range.
This kinda only works on the publisher side.
quote taken from this KB322910:
The only way to obtain a new range for a subscriber table is to run the
Merge Agent.
This clearly isn't working and I'm trying to find out why.
So far the only thing "different" that I can find between the subscriber
and the publisher is the "in_repulishing" column from the output of the
sp_MShelp_identity_property @.tablename='MSGattributes' call.

> Jim.
>
Hilary,
I have read through the sp_MScheckidentityrange stored procedure, I
manually went through some steps to figure out what the stored procedure
was doing. It still is a bit unclear, but it seems to be checking whether
the ranges need a bump or not. It makes a check whether the article is
being republished, it is not, so it skips some extra steps. Due to the
@.checkonly=1 option there is no reseeding done. The result was a nice 0,
no bump needed.
Juggling around with sp_help 'MSGattributes' to check the constraints and DBCC
CHECKIDENT('MSGattributes',noreseed) to check current id, it falls
within the constraint interval.
So, for some strange reason the database thinks the ranges are full, and
yet everything seems to be indicating that the ranges are quite ok and
not full at all yet.
Kind regards,
Gerald Raucamp - http://www.cavey.nl
|||Ok,
I feel like an idiot now. It seems to be working now, I have no idea
what I have done to make it work, but at least the subscriber is
accepting inserts again.
I was put off track by an error message generated by the application on
the subscriber side. Past experience told us it had to do with the
identity range being used up, after fixing the ranges the problem still
persisted so I kept on looking within the database, while in fact the
problem may now be at the application side (xsl/xml transformations).
I would like to thank everybody for their time and I do hope the
database issue has been resolved now. I'm just feeling a bit *hrmz hrmz*
right now about the situation.
Gerald Raucamp - http://www.cavey.nl
|||I've had identity range problems in the past. One thing I seen happening
once was more than one identity range constraint on the same column.
It's always worth checking that before doing anything else.
Jim.
"Gerald Raucamp" wrote:

> Ok,
> I feel like an idiot now. It seems to be working now, I have no idea
> what I have done to make it work, but at least the subscriber is
> accepting inserts again.
> I was put off track by an error message generated by the application on
> the subscriber side. Past experience told us it had to do with the
> identity range being used up, after fixing the ranges the problem still
> persisted so I kept on looking within the database, while in fact the
> problem may now be at the application side (xsl/xml transformations).
> I would like to thank everybody for their time and I do hope the
> database issue has been resolved now. I'm just feeling a bit *hrmz hrmz*
> right now about the situation.
>
> --
> Gerald Raucamp - http://www.cavey.nl
>
|||the in republishing column refers to a replcation topology where your
subscriber also servers as a publisher to other subscribers.
So you pubish the pubs database on your publisher to the pubs database on
the subscriber. This subscriber then publishes the same tables to other
subscribers.
"Gerald Raucamp" <caveyREMOVE@.THISvengeance.et.tudelft.nl> wrote in message
news:slrncshah0.o86.cavey@.vengeance.et.tudelft.nl. ..
> On 2004-12-21, Hilary Cotter <hilary.cotter@.gmail.com> wrote:
> Thanks for the links. I have checked out the articles but unfortunately
> they don't seem to apply to my situation.
> The identity ranges are adjusted on the publisher without much of a
> hickup using the sp_adjustpublisheridentityrange call. The problem lies
> at the subscriber side. Afaik. I'll give it a try though, I might be
> overlooking something.
> The second link concerning the insert trigger does not seem to be an
> issue either. I will check any triggers on the offending tables tomorrow
> for the "not for replication" directive. We had some troubles in the
> past (oversight is the correct word) with insert/update triggers firing
> when the Merge agent was updating info...
> I was wondering though what the "in_republishing" column means in the
> sp_MShelp_identityrange call. Does that mean the table/article is being
> republished on the subscriber side? If so, that would mean something is
> a miss, since currently there is only one publisher and one subscriber
> (more might be added in the future, depends on available IT
> infrastructures at the various branche offices).
> I'll also add the second step to my merge agent and will try to get more
> verbosity in the agent log file.
>
> --
> Gerald Raucamp - cavey@.vengeance.et.tudelft.nl
> http://www.cavey.nl
|||I think you incorrectly sized your ranges. You have to size your range to be
greater than the maximum batch update that your publisher/subscriber will
ever have between merge agent runs. So if you are offline for 1 hour, you
need to ensure that your range will not be exceeded by any update within
that one hour. You have to look at how many rows are modified by a single
update, and how many times an update runs.
What probably happened is a range was exceeded and you got the range
exceeded message, then the merge agent run and adjusted the range, and now
you have a new range assigned on the subscriber.
"Gerald Raucamp" <caveySPAM@.BEGONEvengeance.et.tudelft.nl> wrote in message
news:slrncsil9s.o86.cavey@.vengeance.et.tudelft.nl. ..
> Ok,
> I feel like an idiot now. It seems to be working now, I have no idea
> what I have done to make it work, but at least the subscriber is
> accepting inserts again.
> I was put off track by an error message generated by the application on
> the subscriber side. Past experience told us it had to do with the
> identity range being used up, after fixing the ranges the problem still
> persisted so I kept on looking within the database, while in fact the
> problem may now be at the application side (xsl/xml transformations).
> I would like to thank everybody for their time and I do hope the
> database issue has been resolved now. I'm just feeling a bit *hrmz hrmz*
> right now about the situation.
>
> --
> Gerald Raucamp - http://www.cavey.nl

merge replication identity range drives me crazy

Hello everybody.
I'm in trouble.
The project I am working on is stuck because Sql Server replication isn't working as it should. At least I think so! :-)
I have a publisher and three subscribers in a merge replication. I had some problems inserting rows at subscribers because identity ranges were not set. But I set them (every identity value has its range up to 50000). When I initialize the subscriber and try to insert values in it, the following error occures:
The identity range managed by replication is full and must be updated by a replication agent. The INSERT conflict occurred in database 'Proba', table 'Cena', column 'IDCena'. Sp_adjustpublisheridentityrange can be called to get a new identity range.
But, this cannot be possible because no data has ever been inserted at the subscriber and identity range has been set to a great value.
Please help me understand what's going on and to solve problem. I'm in a trouble...

Quote:

Originally posted by Pera Pisar
Hello everybody.
I'm in trouble.
The project I am working on is stuck because Sql Server replication isn't working as it should. At least I think so! :-)
I have a publisher and three subscribers in a merge replication. I had some problems inserting rows at subscribers because identity ranges were not set. But I set them (every identity value has its range up to 50000). When I initialize the subscriber and try to insert values in it, the following error occures:
The identity range managed by replication is full and must be updated by a replication agent. The INSERT conflict occurred in database 'Proba', table 'Cena', column 'IDCena'. Sp_adjustpublisheridentityrange can be called to get a new identity range.
But, this cannot be possible because no data has ever been inserted at the subscriber and identity range has been set to a great value.
Please help me understand what's going on and to solve problem. I'm in a trouble...

|||I've solved the problem.
Some old settings for an early ajusted replication remained, so there were some constraints that didn't pass.
I didn't know that settings like identity range remain after the replication is deleted. Or did I do something wrong?

Merge Replication Identity Range Clash

We are using SQL Server 2000 merge replication and have a publisher with
three remote subscribers. Everything has been working well for a year but
there is a problem with a newly published table article. The table has a
primary key with the Not For Replication option set. However, the publisher
and two of the three subscribers are using the same identity range and
causing primary key violations. We are not sure as to why this has occurred
but it could be fixed by reseeding the identity range for the table at each
subscriber. Does anyone know of a way to do this?
Dropping the article and recreating will not be possible as we do not want
to drop the existing subscriptions.
Any suggestions would be appreciated
Adam
Adam,
you could synchronize, drop the publication then reseed the identity
columns, recreate the publication with manual control of the identity ranges
and add subscribers with the nosync option.
Regards,
Paul Ibison

Merge replication and Publisher Identity range

Hi,
I have configured a merge replicaiton on sql 2000.
I have also set up auto identity range management on one of my articles.
The publisher identity range is set to 600, and the subscriber identity range is set to 100 and the threshold is %80.My subscribers are mobile users connecting with pocket pc.
I have no problem with the pocket pcs, when they sync sql server automatically adjusts the identity range for them if they have used more than %80. However I have problems with managing identity range on the publisher. When I directly insert into the table on the publisher, if I use more than 80% of my keys, it won't adjust my identity range and it will give an error. I have to manually run the system stored procedure :"sp_adjustpublisheridentityrange" to adjust the range.

Question:
How can I automate this process? I read something about LogReader agent but I don't know how to start it?

Is there any way to handle this problem on the server side? Or do I have to run the stored procedure in my application?BUG: Identity Range Not Adjusted on Publisher When Merge Agent Runs Continuously

http://support.microsoft.com/default.aspx?scid=kb;en-us;304706&Product=sql2k