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.
>

Help! Merge Replication and identity values

Hi,
I have a Merge replication set up between a SQL Server and many (around
150-200) SQL CE databases. I am using identity as primary keys in 2 of the
replicated tables which have a identity seed (set to 1) and range set (set
to 1000 with 10000 as publisher range and 80% threshold). All was going well
until there was a database schema change and I had to rebuild the
publication and re-initialize all subscribers (which was ok).
But after rebuilding the publication and re-initialization all subscribers,
the agent started giving out identity ranges that conflicted with current
values in the database. As some of you have faced similar situation and have
found workarounds. I used a script to manually change the "next seed" value
of the MSrepl_identity_range table to set the "next seed" identity value to
be the max value of the table...basically used UPDATE
distribution..MSrepl_identity_range SET next_seed = max value + range...so
on and so forth. This worked as far as giving each re-initialized
subscribers a new identity range. I have 2 questions regarding this:
* How can I set the publishers range? I manually updated the
MSrepl_identity_range to a higher value than the max value used for updating
the distribution..MSrepl_identity_range using the sql script. What effects
would this have?
* In Check Constraint tab of the replicated table (generating identity
values at subscriber), there is a check constraint value (value is:
[Table1_Col_Id] > 255452 and [Table1_Col_Id] < 400000) which is "Enforced
for Insert and Update" with constraint name like
"repl_identity_range_pub_1CC33D46_49FA_4A34_9722_7 F8D53C0B20A". I had to
uncheck them for the merge agent to be able to add new rows to the server.
Where can I get more info on how this constraint value is generated and how
is it used? Also what is the harm leaving the enforcement of constraint
uncheck?
Can anyone point me to a website where someone has successfully dealt with
this issue without manually setting the ranges?
Please help.
Thanks.
wow! thats a lot of SQL CE databases.
1) to set the identity range on the publisher the correct way to do this is
through the articles property, select the identity range tab. Sounds like
you have already being there. After setting the range on the publisher the
ranges should be parceled out to the Subscribers. As Subscribers come online
they'll get a range assigned to them.
There are instances where the range won't be incremented correctly. For
instance if you have a range size on the publisher of 100 and you update
more than the threshold or range size on the publisher in a batch, the range
adjustment won't be done until the batch is complete. If the batch is more
than 100 records you blow the range and get a constraint error.
In cases like this you have to automatically adjust the identity ranges or
do it manually by adjusting the range table and the corresponding
constraint.
Because of these "limitations" many DBA's elect to use the set it and forget
it approach, where they assign a range to the publisher and subscriber
manually which will not be exceeded in the lifetime of the project/solution.
The dangers of manually making the adjustment is you have to use consistent
values everywhere and you have to adjust the constraint correctly. Other
than that its pretty safe.
The constraint is created when you create the snapshot and adjusted with the
proc sp_MSreseed. This proc is completely undocumented. If you disable the
constraint you may run into problems depending on what is updating your
table. Disableing it for inserts and updates will be harmless if only
replication is making the changes, otherwise you may have problems, if the
identity range is blown and another subscriber/publisher uses it.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Deepak Ramakumar" <dramakumar@.strongtie.com> wrote in message
news:evu$7CnUEHA.2028@.TK2MSFTNGP11.phx.gbl...
> Hi,
>
> I have a Merge replication set up between a SQL Server and many (around
> 150-200) SQL CE databases. I am using identity as primary keys in 2 of the
> replicated tables which have a identity seed (set to 1) and range set (set
> to 1000 with 10000 as publisher range and 80% threshold). All was going
well
> until there was a database schema change and I had to rebuild the
> publication and re-initialize all subscribers (which was ok).
>
> But after rebuilding the publication and re-initialization all
subscribers,
> the agent started giving out identity ranges that conflicted with current
> values in the database. As some of you have faced similar situation and
have
> found workarounds. I used a script to manually change the "next seed"
value
> of the MSrepl_identity_range table to set the "next seed" identity value
to
> be the max value of the table...basically used UPDATE
> distribution..MSrepl_identity_range SET next_seed = max value + range...so
> on and so forth. This worked as far as giving each re-initialized
> subscribers a new identity range. I have 2 questions regarding this:
>
> * How can I set the publishers range? I manually updated the
> MSrepl_identity_range to a higher value than the max value used for
updating
> the distribution..MSrepl_identity_range using the sql script. What effects
> would this have?
> * In Check Constraint tab of the replicated table (generating identity
> values at subscriber), there is a check constraint value (value is:
> [Table1_Col_Id] > 255452 and [Table1_Col_Id] < 400000) which is "Enforced
> for Insert and Update" with constraint name like
> "repl_identity_range_pub_1CC33D46_49FA_4A34_9722_7 F8D53C0B20A". I had to
> uncheck them for the merge agent to be able to add new rows to the server.
> Where can I get more info on how this constraint value is generated and
how
> is it used? Also what is the harm leaving the enforcement of constraint
> uncheck?
>
> Can anyone point me to a website where someone has successfully dealt with
> this issue without manually setting the ranges?
>
> Please help.
> Thanks.
>
>
|||Hilary,
Thanks a lot for your response. Googling on sp_MSreseed gives only one result by Fiach Reid who details the stored procedure itself. I will not be doing any batch inserts so I guess sql should handle the identity ranges automatically, but if it doesn't then I will start seeing conflicts on the server again and may be by that time I could have gathered more info on sp_MSreseed.
Thanks,
Deepak.
Hilary Cotter" <hilaryk@.att.net> wrote in message news:u$aeWOoUEHA.3420@.TK2MSFTNGP12.phx.gbl...

> wow! thats a lot of SQL CE databases.
> 1) to set the identity range on the publisher the correct way to do this is
> through the articles property, select the identity range tab. Sounds like
> you have already being there. After setting the range on the publisher the
> ranges should be parceled out to the Subscribers. As Subscribers come online
> they'll get a range assigned to them.
> There are instances where the range won't be incremented correctly. For
> instance if you have a range size on the publisher of 100 and you update
> more than the threshold or range size on the publisher in a batch, the range
> adjustment won't be done until the batch is complete. If the batch is more
> than 100 records you blow the range and get a constraint error.
> In cases like this you have to automatically adjust the identity ranges or
> do it manually by adjusting the range table and the corresponding
> constraint.
> Because of these "limitations" many DBA's elect to use the set it and forget
> it approach, where they assign a range to the publisher and subscriber
> manually which will not be exceeded in the lifetime of the project/solution.
> The dangers of manually making the adjustment is you have to use consistent
> values everywhere and you have to adjust the constraint correctly. Other
> than that its pretty safe.
> The constraint is created when you create the snapshot and adjusted with the
> proc sp_MSreseed. This proc is completely undocumented. If you disable the
> constraint you may run into problems depending on what is updating your
> table. Disableing it for inserts and updates will be harmless if only
> replication is making the changes, otherwise you may have problems, if the
> identity range is blown and another subscriber/publisher uses it.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Deepak Ramakumar" <dramakumar@.strongtie.com> wrote in message
> news:evu$7CnUEHA.2028@.TK2MSFTNGP11.phx.gbl...
> well
> subscribers,
> have
> value
> to
> updating
> how
>

Help! Cannot Insert Null

Hello Everyone,
This is my first time here.
I have been trying to turn off the "null" property for my id column.
And then I put yes for identity.
when i go to save it i get the error message,
"Cannot insert the value NULL into column 'ID'"
Can someone please help me out with this, its probably something simple, but i just cant figure it out.

Thanks,
PJIdentity values are create automatically by the server. They cannot be NULL.|||for some reason, the property in the ID column showed as "null"
but it should NOT be
when i uncheck the box for null and set the identity for yes, i get that error.
i have a username/password thing on the website, and when someone registers, it DOES insert all the information from the form, but under the ID column, instead, where it USED to give it a number, it says <null>.
i can go in manually and enter a number, and then it works, but i need the ID to be automatically added.|||A check in the "Allow Nulls" column indicates that the field will accept Null values, not that it will exclude them.

You cannot set the Identity property on a field that allows Nulls. If you try, it will automatically set the field to disallow null values.|||I happened to figure this out
What happened:
There were already null "id's" created/inserted into the table
you have to delete those that were created by null, and THEN you can uncheck the "allow nulls" and set the identity to yes
My whole problem was trying to NOT allow nulls, but once nulls are created in the table, you cannot uncheck the box

Thank you so much for your help though.

-PJ