HELP! The merge process could not retrieve identity range resource

Of course I get the error AFTER I start rolling into production!!!!
SQL Server 2000 SP4 and laptops with MSDE SP4 using MERGE with PULL.
There must be a bug in creating articles:
-- ****************** Table Contact with Int16 Identity field on primary key
PRINT 'Table Contact with Int16 Identity field on primary key'
exec sp_addmergearticle @.publication = N'_Prd', @.article = N'Contact',
@.source_owner = N'dbo',
@.source_object = N'Contact', @.type = N'table', @.description = null,
@.column_tracking = N'true',
@.pre_creation_cmd = N'drop', @.creation_script = null, @.schema_option =
0x000000000000CFF1,
@.article_resolver = N'Microsoft SQL Server DATETIME (Later Wins) Conflict
Resolver',
@.resolver_info = N'ChangeStamp',
@.subset_filterclause = null, @.vertical_partition = N'false',
@.destination_owner = N'dbo',
@.auto_identity_range = N'true',
@.pub_identity_range = 15000,
@.identity_range = 100,
@.threshold = 99,
@.verify_resolver_signature = 0,
@.allow_interactive_resolver = N'false', @.fast_multicol_updateproc = N'true',
@.check_permissions = 0
GO
Creates a constraint of:
([ContactId] > 12 and [ContactId] < 30000)
when it should be
([ContactId] > 12 and [ContactId] < 15000)
So now after a few clients I can no longer replicate cause I can only go to
32767.
So a few questions:
1) Why the bug and how do I fix it?
2) How do I fix things in production WITHOUT redoing the replication?
3) How can I tell what the current identity is? I mean, even starting at
30000, I should get lots of ranges on the laptops because they should be only
incrementing by 100. That should still be 27 laptops (100 * 2767) before I
blow the Integer data type. We've only rolled about 5-10.
The publisher range has always been twice as large as it should be. Also the
check constraint is one out. I've pointed these errors to MS developers and
apparently they're ok in SQL Server 2005 (haven't tested this yet).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||your threshold is all wrong. You need a smaller value. Basically you are
saying that at the very best you know that there will be 1 sync occurring
for every insert on Contact and you absolutely know beyond any shadow of a
doubt that this insert will never be kicked back and leave a gap in the
identity field.
For instance try this
create table identity_test
(pk int identity,charcol char(1))
go
begin tran
insert into identity_test (charcol) values ('x')
rollback tran
insert into identity_test (charcol) values ('x')
dbcc checkident('identity_test')
select * from identity_test
you get the value of 2. What happened to 1? you can't reclaim it without
doing a reseed.
This could happen when you want to bump up your threshold and then the next
insert will be kicked back by the constraint.
Pick a value for your range which represents the max amount of inserts which
could occur on your publisher between sync. Multiply by a number to give you
some wiggle room, I use 10. Then set your threshold to 80. This will mean
you get many opportunities for the range to be adjusted.
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
"Buzz" <buzz@.online.nospam> wrote in message
news:C73BE767-1CF1-4289-A875-3F271D6BA3B0@.microsoft.com...
> Of course I get the error AFTER I start rolling into production!!!!
> SQL Server 2000 SP4 and laptops with MSDE SP4 using MERGE with PULL.
> There must be a bug in creating articles:
> -- ****************** Table Contact with Int16 Identity field on primary
key
> PRINT 'Table Contact with Int16 Identity field on primary key'
> exec sp_addmergearticle @.publication = N'_Prd', @.article = N'Contact',
> @.source_owner = N'dbo',
> @.source_object = N'Contact', @.type = N'table', @.description = null,
> @.column_tracking = N'true',
> @.pre_creation_cmd = N'drop', @.creation_script = null, @.schema_option =
> 0x000000000000CFF1,
> @.article_resolver = N'Microsoft SQL Server DATETIME (Later Wins) Conflict
> Resolver',
> @.resolver_info = N'ChangeStamp',
> @.subset_filterclause = null, @.vertical_partition = N'false',
> @.destination_owner = N'dbo',
> @.auto_identity_range = N'true',
> @.pub_identity_range = 15000,
> @.identity_range = 100,
> @.threshold = 99,
> @.verify_resolver_signature = 0,
> @.allow_interactive_resolver = N'false', @.fast_multicol_updateproc =
N'true',
> @.check_permissions = 0
> GO
> Creates a constraint of:
> ([ContactId] > 12 and [ContactId] < 30000)
> when it should be
> ([ContactId] > 12 and [ContactId] < 15000)
> So now after a few clients I can no longer replicate cause I can only go
to
> 32767.
> So a few questions:
> 1) Why the bug and how do I fix it?
> 2) How do I fix things in production WITHOUT redoing the replication?
> 3) How can I tell what the current identity is? I mean, even starting at
> 30000, I should get lots of ranges on the laptops because they should be
only
> incrementing by 100. That should still be 27 laptops (100 * 2767) before I
> blow the Integer data type. We've only rolled about 5-10.
>