Identity range when rows already exist

I've created a merge publication with automatic range management. Insert
fails because the ranges assigned have already been used. How do I specify
that I want the new identity ranges to start above those which have already
been used?
I created this publication by backing up my production database and
restoring it to my test database. Then I created the publication on my test
database by manually editing the auto-generated script for creating the
publication on the production database. I don't know if this is the reason
things don't work out as I want them to.
I think you would be best to drop this publication and its subscriptions and
recreate from start.
If you are a masochist you can do the following.
Look in your distributor for a table called MSrepl_identity_range. The
highest range is the range which is deployed to one of your subscribers. You
can bump this value up to give yourself a cushion.
For instance if the highest range is 10000, bump it up to 20000, which will
be the next value assigned.
Now go to your problem subscriber and fix the table there. Use dbcc
checkident('tablename') to determine what the current range is, and then
reseed to the value you found on your publisher's distribution database
MSrepl_identity_range table.
Now issue a sp_help 'problemTableName' to get the name of the check
constraint used to restrict the range of possible values acceptable for this
table. script out the check constraint and recreate it with a set of values
which matches the range you assigned with the checkident reseed statement.
If you are really feeling like punishing yourself you might want to read
http://www.simple-talk.com/2005/07/05/replication/
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Daniel" <daXniel_kriXstensXen_@.hotmail.com (remove the Xs)> wrote in
message news:92B2698A-DE7C-485C-9381-78295E49A335@.microsoft.com...
> I've created a merge publication with automatic range management. Insert
> fails because the ranges assigned have already been used. How do I specify
> that I want the new identity ranges to start above those which have
already
> been used?
> I created this publication by backing up my production database and
> restoring it to my test database. Then I created the publication on my
test
> database by manually editing the auto-generated script for creating the
> publication on the production database. I don't know if this is the reason
> things don't work out as I want them to.
|||"Hilary Cotter" wrote:

> I think you would be best to drop this publication and its subscriptions and
> recreate from start.
I already did that. Perhaps the problem was that I created the publication
using the script generated by EM. For each merge article it did:
exec sp_addmergearticle ... @.article = [tableName] ...
go
To solve the problem, for all merge articles for which I use automatic range
management I added:
declare @.NewID int
Select @.newID = max(ID)+1 FROM [tableName]
DBCC CHECKIDENT([tableName],RESEED,@.newID)
exec sp_addmergearticle @.article = [tableName] ...
go
That is, I reseed the identity for each table before adding it to the
publication. It seems to work.

>If you are really feeling like punishing yourself you might want to read
> http://www.simple-talk.com/2005/07/05/replication/
Thanks I did that. You got all these great articles scattered all over the
net. But your book about merge replication is due any week now, right? It
would be nice to have the information gathered in one place