What is probably incredibly easy to do, I am having problems with. All I want to do is to pick up whether or not a date is in a specific range, and if it is then output the end of the month date.
here is the code I started:
if {ar_invoices.arih_invoice_date} >= Date(01/01/2005) and {ar_invoices.arih_invoice_date} <= date(01/31/2005)
then
"01/31/2005"
else
if {ar_invoices.arih_invoice_date} >= date(02/01/2005) and {ar_invoices.arih_invoice_date} <= date(01/28/2005)
then
"02/28/2005"
else
if {ar_invoices.arih_invoice_date} >= date(03/01/2005) and {ar_invoices.arih_invoice_date} <= date(03/31/2005)
then
"03/30/2005"
else
if {ar_invoices.arih_invoice_date} >= date(04/01/2005)
then
"Has not been invoiced yet"
I will eventually continue the code, but want to make sure it works first
Let me know if you need more clarification!
Thank you so much!Looks like the date format was incorrect.
for those who may be stuck on the same thing here's the code:
if {ar_invoices.arih_invoice_date} >= Date(2005,1,1) and {ar_invoices.arih_invoice_date} <= date(2005,1,31)
then
"01/31/2005"
else
if {ar_invoices.arih_invoice_date} >= date(2005,2,1) and {ar_invoices.arih_invoice_date} <= date(2005,2,28)
then
"02/28/2005"
else
if {ar_invoices.arih_invoice_date} >= date(2005,3,1) and {ar_invoices.arih_invoice_date} <= date(2005,3,30)
then
"03/30/2005"
else
"Has not been invoiced yet"
Showing posts with label range. Show all posts
Showing posts with label range. Show all posts
Monday, March 26, 2012
Monday, March 19, 2012
Identity reseed error with merge
Are you using automatic range management? If so, the
process of synchronization will reseed if you achieve a
set % of available values. I'd advise against relying on
this behaving correctly in all circumstances and force
the system to never reseed by allocating a huge range to
each subscriber. An integer datatype can span -2billion
to 2billion or so, so there is not really any need to
ever have such a small range that reseeding occurs (IMHO).
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thanks but these is not the problem. Perhaps I didn't express correctly.
I know the merge replication automatically reseed when it reaches the
imposed limit, but what happened to me is that this seed has returned to
an anterior value, so with the IDs I get concurrence troubles
replicating. The replication and range automatic asignation works
correctly, but I don't know what I did to make the seed backward to a
previous value. The seed didn't reached its limit.
I changed manually the values of the seed in
Distribution.MSrepl_identity_range, but I want to know in what
circurstances those values are being reinitialized to a previous value.
Thanks
Juan
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||are they reseeded on the publisher, subscriber or both?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Juan a" <juan@.mailinator.com> wrote in message
news:uNotHeU$EHA.4004@.tk2msftngp13.phx.gbl...
>
> Thanks but these is not the problem. Perhaps I didn't express correctly.
> I know the merge replication automatically reseed when it reaches the
> imposed limit, but what happened to me is that this seed has returned to
> an anterior value, so with the IDs I get concurrence troubles
> replicating. The replication and range automatic asignation works
> correctly, but I don't know what I did to make the seed backward to a
> previous value. The seed didn't reached its limit.
> I changed manually the values of the seed in
> Distribution.MSrepl_identity_range, but I want to know in what
> circurstances those values are being reinitialized to a previous value.
> Thanks
> Juan
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||They are reinitialized on publisher, but because of automatic range
administration, when the SQLCe synchronize it gets the range asigned
from the publisher, an old range that was used before, and that already
have registers in publisher.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
process of synchronization will reseed if you achieve a
set % of available values. I'd advise against relying on
this behaving correctly in all circumstances and force
the system to never reseed by allocating a huge range to
each subscriber. An integer datatype can span -2billion
to 2billion or so, so there is not really any need to
ever have such a small range that reseeding occurs (IMHO).
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thanks but these is not the problem. Perhaps I didn't express correctly.
I know the merge replication automatically reseed when it reaches the
imposed limit, but what happened to me is that this seed has returned to
an anterior value, so with the IDs I get concurrence troubles
replicating. The replication and range automatic asignation works
correctly, but I don't know what I did to make the seed backward to a
previous value. The seed didn't reached its limit.
I changed manually the values of the seed in
Distribution.MSrepl_identity_range, but I want to know in what
circurstances those values are being reinitialized to a previous value.
Thanks
Juan
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||are they reseeded on the publisher, subscriber or both?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Juan a" <juan@.mailinator.com> wrote in message
news:uNotHeU$EHA.4004@.tk2msftngp13.phx.gbl...
>
> Thanks but these is not the problem. Perhaps I didn't express correctly.
> I know the merge replication automatically reseed when it reaches the
> imposed limit, but what happened to me is that this seed has returned to
> an anterior value, so with the IDs I get concurrence troubles
> replicating. The replication and range automatic asignation works
> correctly, but I don't know what I did to make the seed backward to a
> previous value. The seed didn't reached its limit.
> I changed manually the values of the seed in
> Distribution.MSrepl_identity_range, but I want to know in what
> circurstances those values are being reinitialized to a previous value.
> Thanks
> Juan
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||They are reinitialized on publisher, but because of automatic range
administration, when the SQLCe synchronize it gets the range asigned
from the publisher, an old range that was used before, and that already
have registers in publisher.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Identity ranges different for different pubs
Silly question that came to mind as I was driving back from a poker game:
What would happen if one publication sets an identity range at say, 100,000
and another publication (in the same database) has the identity range set
for 50,000 on the same table?
Earl,
when you create the second publication, the identity range for the table is
inherited from the first one.
If you're having these sort to thoughts, does it mean you won or lost
?
Rgds,
Paul Ibison, SQL MVP
|||Hehe. I won... now if I can just cobble together enough wins to get back to
the WSOP this year ...
On a more serious note, I'm in the final stages of rolling out an app and
when the mind is freewheeling, I tend to speculate on what might go wrong
long-term. It seems like some issues of replication could lead to walking
into a dead-end if it were not anticipated early on.
Thanks Paul.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eGNf8mBcFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Earl,
> when you create the second publication, the identity range for the table
> is inherited from the first one.
> If you're having these sort to thoughts, does it mean you won or lost?
> Rgds,
> Paul Ibison, SQL MVP
>
What would happen if one publication sets an identity range at say, 100,000
and another publication (in the same database) has the identity range set
for 50,000 on the same table?
Earl,
when you create the second publication, the identity range for the table is
inherited from the first one.
If you're having these sort to thoughts, does it mean you won or lost
?Rgds,
Paul Ibison, SQL MVP
|||Hehe. I won... now if I can just cobble together enough wins to get back to
the WSOP this year ...
On a more serious note, I'm in the final stages of rolling out an app and
when the mind is freewheeling, I tend to speculate on what might go wrong
long-term. It seems like some issues of replication could lead to walking
into a dead-end if it were not anticipated early on.
Thanks Paul.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eGNf8mBcFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Earl,
> when you create the second publication, the identity range for the table
> is inherited from the first one.
> If you're having these sort to thoughts, does it mean you won or lost
?> Rgds,
> Paul Ibison, SQL MVP
>
Identity ranges
Using merge replication, I've decided to change over to identity ranges
(versus uniqueidentifiers). At the publisher, I'm setting a range on the
tables that would be consistent with the records expected not to be exceeded
in the database. Since these values are for an entire state of consumers, I
set the publisher range to 500,000 for that consumer table.
Three questions:
1. Does the publisher range sound excessive and would I be better off using
the auto-range to up that value or should I err on the high side initially?
2. On the subscriber side (all PPC), the normal range of records that would
be pushed would probably be 3000-5000 and the actual changes made would
probably only amount to about 500 per day per PPC. What would be a safe
range on the subscriber side?
3. Does it really matter how WIDE the range is set?
You really need to consider how long the PPC can go without a sync. Can
they stay disconnected for 5 days, 10, longer? Do some calculating and make
sure that not only the range is wide enough, but that the threshold is low
enough so they receive a new range to cover the next worst case senario.
One thing you can guarantee is that the user are not forced to sync
everyday...they probably will not.
"Earl" wrote:
> Using merge replication, I've decided to change over to identity ranges
> (versus uniqueidentifiers). At the publisher, I'm setting a range on the
> tables that would be consistent with the records expected not to be exceeded
> in the database. Since these values are for an entire state of consumers, I
> set the publisher range to 500,000 for that consumer table.
> Three questions:
> 1. Does the publisher range sound excessive and would I be better off using
> the auto-range to up that value or should I err on the high side initially?
> 2. On the subscriber side (all PPC), the normal range of records that would
> be pushed would probably be 3000-5000 and the actual changes made would
> probably only amount to about 500 per day per PPC. What would be a safe
> range on the subscriber side?
> 3. Does it really matter how WIDE the range is set?
>
>
|||Thanks Brian, those are good thoughts. A couple of followup questions: What
would the criteria be to ensure that the "range is wide enough"? I didn't
really understand about the threshold being "low enough".
"Brian Reuter" <BrianReuter@.discussions.microsoft.com> wrote in message
news:885F4F00-0793-478F-B363-7FCCF97C97C9@.microsoft.com...[vbcol=seagreen]
> You really need to consider how long the PPC can go without a sync. Can
> they stay disconnected for 5 days, 10, longer? Do some calculating and
> make
> sure that not only the range is wide enough, but that the threshold is low
> enough so they receive a new range to cover the next worst case senario.
> One thing you can guarantee is that the user are not forced to sync
> everyday...they probably will not.
>
>
> "Earl" wrote:
|||IMHO best way is to give range of 10.000.000 or even hundred million
and forget about ranges, tresholds etc. forever.
Max identity is something like 9223372036854775807, so why to be
worried about ranges.
Is there any drawback with this approach?
Pagus
On Fri, 12 Nov 2004 10:13:23 -0500, "Earl"
<brikshoe@.newsgroups.nospam> wrote:
[vbcol=seagreen]
>Thanks Brian, those are good thoughts. A couple of followup questions: What
>would the criteria be to ensure that the "range is wide enough"? I didn't
>really understand about the threshold being "low enough".
>"Brian Reuter" <BrianReuter@.discussions.microsoft.com> wrote in message
>news:885F4F00-0793-478F-B363-7FCCF97C97C9@.microsoft.com...
|||FAO Earl - Pagus is talking about BigInts (8 bytes) and
not Ints (4 bytes), where the range is +/-2billion or so.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I was thinking that range wouldn't work :=)
I want that replication book, can I get that as an ebook too (I didnt' see
anything on the site)?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:5e3f01c4c999$7e3be410$a601280a@.phx.gbl...
> FAO Earl - Pagus is talking about BigInts (8 bytes) and
> not Ints (4 bytes), where the range is +/-2billion or so.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Earl,
the book is now available - I've read a draft and it looks v.good.
Hilary mentioned something about internet updates, but I don't recall
hearing about it as an ebook. Hopefully he'll see this thread and reply to
you.
Rgds,
Paul Ibison, SQL Server MVP
|||No, ebook. The merge volume may be released as an ebook, but its future is
yet undecided.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:Ot8sppbyEHA.2568@.TK2MSFTNGP11.phx.gbl...
> Earl,
> the book is now available - I've read a draft and it looks v.good.
> Hilary mentioned something about internet updates, but I don't recall
> hearing about it as an ebook. Hopefully he'll see this thread and reply to
> you.
> Rgds,
> Paul Ibison, SQL Server MVP
>
(versus uniqueidentifiers). At the publisher, I'm setting a range on the
tables that would be consistent with the records expected not to be exceeded
in the database. Since these values are for an entire state of consumers, I
set the publisher range to 500,000 for that consumer table.
Three questions:
1. Does the publisher range sound excessive and would I be better off using
the auto-range to up that value or should I err on the high side initially?
2. On the subscriber side (all PPC), the normal range of records that would
be pushed would probably be 3000-5000 and the actual changes made would
probably only amount to about 500 per day per PPC. What would be a safe
range on the subscriber side?
3. Does it really matter how WIDE the range is set?
You really need to consider how long the PPC can go without a sync. Can
they stay disconnected for 5 days, 10, longer? Do some calculating and make
sure that not only the range is wide enough, but that the threshold is low
enough so they receive a new range to cover the next worst case senario.
One thing you can guarantee is that the user are not forced to sync
everyday...they probably will not.
"Earl" wrote:
> Using merge replication, I've decided to change over to identity ranges
> (versus uniqueidentifiers). At the publisher, I'm setting a range on the
> tables that would be consistent with the records expected not to be exceeded
> in the database. Since these values are for an entire state of consumers, I
> set the publisher range to 500,000 for that consumer table.
> Three questions:
> 1. Does the publisher range sound excessive and would I be better off using
> the auto-range to up that value or should I err on the high side initially?
> 2. On the subscriber side (all PPC), the normal range of records that would
> be pushed would probably be 3000-5000 and the actual changes made would
> probably only amount to about 500 per day per PPC. What would be a safe
> range on the subscriber side?
> 3. Does it really matter how WIDE the range is set?
>
>
|||Thanks Brian, those are good thoughts. A couple of followup questions: What
would the criteria be to ensure that the "range is wide enough"? I didn't
really understand about the threshold being "low enough".
"Brian Reuter" <BrianReuter@.discussions.microsoft.com> wrote in message
news:885F4F00-0793-478F-B363-7FCCF97C97C9@.microsoft.com...[vbcol=seagreen]
> You really need to consider how long the PPC can go without a sync. Can
> they stay disconnected for 5 days, 10, longer? Do some calculating and
> make
> sure that not only the range is wide enough, but that the threshold is low
> enough so they receive a new range to cover the next worst case senario.
> One thing you can guarantee is that the user are not forced to sync
> everyday...they probably will not.
>
>
> "Earl" wrote:
|||IMHO best way is to give range of 10.000.000 or even hundred million
and forget about ranges, tresholds etc. forever.
Max identity is something like 9223372036854775807, so why to be
worried about ranges.
Is there any drawback with this approach?
Pagus
On Fri, 12 Nov 2004 10:13:23 -0500, "Earl"
<brikshoe@.newsgroups.nospam> wrote:
[vbcol=seagreen]
>Thanks Brian, those are good thoughts. A couple of followup questions: What
>would the criteria be to ensure that the "range is wide enough"? I didn't
>really understand about the threshold being "low enough".
>"Brian Reuter" <BrianReuter@.discussions.microsoft.com> wrote in message
>news:885F4F00-0793-478F-B363-7FCCF97C97C9@.microsoft.com...
|||FAO Earl - Pagus is talking about BigInts (8 bytes) and
not Ints (4 bytes), where the range is +/-2billion or so.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I was thinking that range wouldn't work :=)
I want that replication book, can I get that as an ebook too (I didnt' see
anything on the site)?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:5e3f01c4c999$7e3be410$a601280a@.phx.gbl...
> FAO Earl - Pagus is talking about BigInts (8 bytes) and
> not Ints (4 bytes), where the range is +/-2billion or so.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Earl,
the book is now available - I've read a draft and it looks v.good.
Hilary mentioned something about internet updates, but I don't recall
hearing about it as an ebook. Hopefully he'll see this thread and reply to
you.
Rgds,
Paul Ibison, SQL Server MVP
|||No, ebook. The merge volume may be released as an ebook, but its future is
yet undecided.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:Ot8sppbyEHA.2568@.TK2MSFTNGP11.phx.gbl...
> Earl,
> the book is now available - I've read a draft and it looks v.good.
> Hilary mentioned something about internet updates, but I don't recall
> hearing about it as an ebook. Hopefully he'll see this thread and reply to
> you.
> Rgds,
> Paul Ibison, SQL Server MVP
>
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
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
Identity Range reused by subscriber
We are running Windows 2000 server SP3a for the publisher/distributor and
MSDE with SP3a for the subscriber.
We have several tables that have identities for primary keys, and our
identity range handling has been working great, until now.
One of the tables decided to REUSE its existing range of 1000 (threshold is
set to 80%), so all the transactions are backing up at the subscriber since
the range
has already been used once before.
I tried to use the sp_adjustpublisheridentityrange, and it says it ran
successfully, but it is still doing the same thing.
Looking at the next available identity range on the table, it is showing
correctly for the next available block of 1000.
What could have happened, and how do I recover?
Thanks,
RS
It is hard to say what has caused this to happen. Obviously automatic identity range management has failed you for some reason.
I'd call up PSS on this problem.
Some people have had success my manually adjusting the constraint on the subscriber/publisher tables, and manually adjusting the values of MSrepl_identity_range in the distribution database.
And Mspub_identity_range.
Most DBA's will use the set it and forget approach to automatic identity range management. They will set their ranges once with what they feel are representative ranges for the lifecycle of the project. They don't have to worry about batch updates blowing
the range, or problems with automatic identity range management this way.
Of course if you are continually adding new susbcribers this might now work for you.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
MSDE with SP3a for the subscriber.
We have several tables that have identities for primary keys, and our
identity range handling has been working great, until now.
One of the tables decided to REUSE its existing range of 1000 (threshold is
set to 80%), so all the transactions are backing up at the subscriber since
the range
has already been used once before.
I tried to use the sp_adjustpublisheridentityrange, and it says it ran
successfully, but it is still doing the same thing.
Looking at the next available identity range on the table, it is showing
correctly for the next available block of 1000.
What could have happened, and how do I recover?
Thanks,
RS
It is hard to say what has caused this to happen. Obviously automatic identity range management has failed you for some reason.
I'd call up PSS on this problem.
Some people have had success my manually adjusting the constraint on the subscriber/publisher tables, and manually adjusting the values of MSrepl_identity_range in the distribution database.
And Mspub_identity_range.
Most DBA's will use the set it and forget approach to automatic identity range management. They will set their ranges once with what they feel are representative ranges for the lifecycle of the project. They don't have to worry about batch updates blowing
the range, or problems with automatic identity range management this way.
Of course if you are continually adding new susbcribers this might now work for you.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Identity Range Problem
Hi guys - A client just encountered a problem where he could not insert
into a table because of pk constraints. The database is a replica of a merge
publication with automatic identity range with settings:
Range at Pub : 1000
Range at Sub : 1000
Thresh.: 80
When I checked what the next identity was going to be, it returned '12'
but it should really be over 1000. As a matter of fact, it seems that all
tables seem to be using the Publisher's range.
Does anybody know what could have caused this?
What are the side-effects if I reseed the tables?
Thanks - Maer
You probably have a check constraint in place which is limiting the range of
values which can be inserted. Automatic identity range management had a
nasty habit of lingering after the subscription was dropped you might be
running into this - if so I would delete this constraint or adjust it.
I don't really understand what you mean by replica? Do you mean its a
subscriber, or you someone made a replica of the publication database
(through a backup perhaps).
You might also want to review this article -
http://www.simple-talk.com/2005/07/05/replication/
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Maer" <maer@.auditleverage.com> wrote in message
news:%23VIoj$%23NGHA.420@.tk2msftngp13.phx.gbl...
> Hi guys - A client just encountered a problem where he could not
> insert into a table because of pk constraints. The database is a replica
> of a merge publication with automatic identity range with settings:
> Range at Pub : 1000
> Range at Sub : 1000
> Thresh.: 80
> When I checked what the next identity was going to be, it returned '12'
> but it should really be over 1000. As a matter of fact, it seems that all
> tables seem to be using the Publisher's range.
> Does anybody know what could have caused this?
> What are the side-effects if I reseed the tables?
> Thanks - Maer
>
|||Hi Hilary - Thanks for your response. I should have said subscriber
instead of replica.
It turned out that the client was still in SP 1 and I heard there were
some issues with identity ranges prior to SP 3. So we applied the latest SP
and dropped all subscriptions and re-subscribed. So far it seems to be
working.
It is good to know the issue with triggers so that this is one more
thing to check if that happens again.
Thanks - Maer
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uCviJ1BOGHA.2628@.TK2MSFTNGP15.phx.gbl...
> You probably have a check constraint in place which is limiting the range
> of values which can be inserted. Automatic identity range management had a
> nasty habit of lingering after the subscription was dropped you might be
> running into this - if so I would delete this constraint or adjust it.
> I don't really understand what you mean by replica? Do you mean its a
> subscriber, or you someone made a replica of the publication database
> (through a backup perhaps).
> You might also want to review this article -
> http://www.simple-talk.com/2005/07/05/replication/
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Maer" <maer@.auditleverage.com> wrote in message
> news:%23VIoj$%23NGHA.420@.tk2msftngp13.phx.gbl...
>
into a table because of pk constraints. The database is a replica of a merge
publication with automatic identity range with settings:
Range at Pub : 1000
Range at Sub : 1000
Thresh.: 80
When I checked what the next identity was going to be, it returned '12'
but it should really be over 1000. As a matter of fact, it seems that all
tables seem to be using the Publisher's range.
Does anybody know what could have caused this?
What are the side-effects if I reseed the tables?
Thanks - Maer
You probably have a check constraint in place which is limiting the range of
values which can be inserted. Automatic identity range management had a
nasty habit of lingering after the subscription was dropped you might be
running into this - if so I would delete this constraint or adjust it.
I don't really understand what you mean by replica? Do you mean its a
subscriber, or you someone made a replica of the publication database
(through a backup perhaps).
You might also want to review this article -
http://www.simple-talk.com/2005/07/05/replication/
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Maer" <maer@.auditleverage.com> wrote in message
news:%23VIoj$%23NGHA.420@.tk2msftngp13.phx.gbl...
> Hi guys - A client just encountered a problem where he could not
> insert into a table because of pk constraints. The database is a replica
> of a merge publication with automatic identity range with settings:
> Range at Pub : 1000
> Range at Sub : 1000
> Thresh.: 80
> When I checked what the next identity was going to be, it returned '12'
> but it should really be over 1000. As a matter of fact, it seems that all
> tables seem to be using the Publisher's range.
> Does anybody know what could have caused this?
> What are the side-effects if I reseed the tables?
> Thanks - Maer
>
|||Hi Hilary - Thanks for your response. I should have said subscriber
instead of replica.
It turned out that the client was still in SP 1 and I heard there were
some issues with identity ranges prior to SP 3. So we applied the latest SP
and dropped all subscriptions and re-subscribed. So far it seems to be
working.
It is good to know the issue with triggers so that this is one more
thing to check if that happens again.
Thanks - Maer
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uCviJ1BOGHA.2628@.TK2MSFTNGP15.phx.gbl...
> You probably have a check constraint in place which is limiting the range
> of values which can be inserted. Automatic identity range management had a
> nasty habit of lingering after the subscription was dropped you might be
> running into this - if so I would delete this constraint or adjust it.
> I don't really understand what you mean by replica? Do you mean its a
> subscriber, or you someone made a replica of the publication database
> (through a backup perhaps).
> You might also want to review this article -
> http://www.simple-talk.com/2005/07/05/replication/
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Maer" <maer@.auditleverage.com> wrote in message
> news:%23VIoj$%23NGHA.420@.tk2msftngp13.phx.gbl...
>
Identity Range not working for master
I have set up a publisher database and a subscriber database which is a replica of the publisher. For the identity fields I set it up so that they would have a range of 10 numbers with an 80% margin. I was testing this on the subscriber and replica at t
he same time. I disconnected the subscriber, used up 8 id's, then connected and sure enough it gave me a new range. However whilst using the publisher it used up all 10 in the range and then gave me an error message! Surely it should have automatically
given me a new range once I'd hit 80% of the previous range.
Any help?
Thanks
Adrian
that depends on how large the batch is. So if you update 20 records in a
batch, it won't get updated and you blow the range.
The idea is to pick ranges that are much larger than representative batches.
So if I were you, I'd try ranges in the 1000's or set ranges that will not
be exceeded in the life time of your replication solution.
Hilary
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:855D2D7D-63EF-40D2-9457-65C97E67A33F@.microsoft.com...
> I have set up a publisher database and a subscriber database which is a
replica of the publisher. For the identity fields I set it up so that they
would have a range of 10 numbers with an 80% margin. I was testing this on
the subscriber and replica at the same time. I disconnected the subscriber,
used up 8 id's, then connected and sure enough it gave me a new range.
However whilst using the publisher it used up all 10 in the range and then
gave me an error message! Surely it should have automatically given me a
new range once I'd hit 80% of the previous range.
> Any help?
> Thanks
> Adrian
|||Adrian,
as well as Hilary's reply, you could also consider manual range management
and use an algorithm that ensures no overlap in the ranges:
http://www.mssqlserver.com/replicati...h_identity.asp
HTH,
Paul Ibison
|||Hilary
I do intend to use much larger ranges, however I was just testing out the process on a smaller range to see it in action. It worked for the subscriber but not the publisher?
Any thoughts?
Regards
Adrian
"Hilary Cotter" wrote:
> that depends on how large the batch is. So if you update 20 records in a
> batch, it won't get updated and you blow the range.
> The idea is to pick ranges that are much larger than representative batches.
> So if I were you, I'd try ranges in the 1000's or set ranges that will not
> be exceeded in the life time of your replication solution.
> Hilary
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Adrian" <Adrian@.discussions.microsoft.com> wrote in message
> news:855D2D7D-63EF-40D2-9457-65C97E67A33F@.microsoft.com...
> replica of the publisher. For the identity fields I set it up so that they
> would have a range of 10 numbers with an 80% margin. I was testing this on
> the subscriber and replica at the same time. I disconnected the subscriber,
> used up 8 id's, then connected and sure enough it gave me a new range.
> However whilst using the publisher it used up all 10 in the range and then
> gave me an error message! Surely it should have automatically given me a
> new range once I'd hit 80% of the previous range.
>
>
|||There are a couple of issues here
1) are you running your agent continuously? Running is on a schedule,
every 5-10 minutes, or even less can help the adjustment, otherwise you
might want to manually execute the increment procedure
(sp_adjustpublisheridentityrange) to adjust everything
2) its not clear to me that the number of rows in the batch was close
enough to the threshold to kick off the indentity range adjustment. Was it?
3) The allottment of ranges is not always intuitive. For instance if I
set a range on the Publisher of 100, the Publisher may "own" 0-200, where
the Subscriber "owns" 200-300. You have to look at the check constaints on
the indentity range tables to figure this out.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:308EF6B0-3714-4D95-8BE6-4EBD352B1929@.microsoft.com...
> Hilary
> I do intend to use much larger ranges, however I was just testing out the
process on a smaller range to see it in action. It worked for the
subscriber but not the publisher?[vbcol=seagreen]
> Any thoughts?
> Regards
> Adrian
> "Hilary Cotter" wrote:
batches.[vbcol=seagreen]
not[vbcol=seagreen]
a[vbcol=seagreen]
they[vbcol=seagreen]
on[vbcol=seagreen]
subscriber,[vbcol=seagreen]
then[vbcol=seagreen]
a[vbcol=seagreen]
he same time. I disconnected the subscriber, used up 8 id's, then connected and sure enough it gave me a new range. However whilst using the publisher it used up all 10 in the range and then gave me an error message! Surely it should have automatically
given me a new range once I'd hit 80% of the previous range.
Any help?
Thanks
Adrian
that depends on how large the batch is. So if you update 20 records in a
batch, it won't get updated and you blow the range.
The idea is to pick ranges that are much larger than representative batches.
So if I were you, I'd try ranges in the 1000's or set ranges that will not
be exceeded in the life time of your replication solution.
Hilary
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:855D2D7D-63EF-40D2-9457-65C97E67A33F@.microsoft.com...
> I have set up a publisher database and a subscriber database which is a
replica of the publisher. For the identity fields I set it up so that they
would have a range of 10 numbers with an 80% margin. I was testing this on
the subscriber and replica at the same time. I disconnected the subscriber,
used up 8 id's, then connected and sure enough it gave me a new range.
However whilst using the publisher it used up all 10 in the range and then
gave me an error message! Surely it should have automatically given me a
new range once I'd hit 80% of the previous range.
> Any help?
> Thanks
> Adrian
|||Adrian,
as well as Hilary's reply, you could also consider manual range management
and use an algorithm that ensures no overlap in the ranges:
http://www.mssqlserver.com/replicati...h_identity.asp
HTH,
Paul Ibison
|||Hilary
I do intend to use much larger ranges, however I was just testing out the process on a smaller range to see it in action. It worked for the subscriber but not the publisher?
Any thoughts?
Regards
Adrian
"Hilary Cotter" wrote:
> that depends on how large the batch is. So if you update 20 records in a
> batch, it won't get updated and you blow the range.
> The idea is to pick ranges that are much larger than representative batches.
> So if I were you, I'd try ranges in the 1000's or set ranges that will not
> be exceeded in the life time of your replication solution.
> Hilary
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Adrian" <Adrian@.discussions.microsoft.com> wrote in message
> news:855D2D7D-63EF-40D2-9457-65C97E67A33F@.microsoft.com...
> replica of the publisher. For the identity fields I set it up so that they
> would have a range of 10 numbers with an 80% margin. I was testing this on
> the subscriber and replica at the same time. I disconnected the subscriber,
> used up 8 id's, then connected and sure enough it gave me a new range.
> However whilst using the publisher it used up all 10 in the range and then
> gave me an error message! Surely it should have automatically given me a
> new range once I'd hit 80% of the previous range.
>
>
|||There are a couple of issues here
1) are you running your agent continuously? Running is on a schedule,
every 5-10 minutes, or even less can help the adjustment, otherwise you
might want to manually execute the increment procedure
(sp_adjustpublisheridentityrange) to adjust everything
2) its not clear to me that the number of rows in the batch was close
enough to the threshold to kick off the indentity range adjustment. Was it?
3) The allottment of ranges is not always intuitive. For instance if I
set a range on the Publisher of 100, the Publisher may "own" 0-200, where
the Subscriber "owns" 200-300. You have to look at the check constaints on
the indentity range tables to figure this out.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:308EF6B0-3714-4D95-8BE6-4EBD352B1929@.microsoft.com...
> Hilary
> I do intend to use much larger ranges, however I was just testing out the
process on a smaller range to see it in action. It worked for the
subscriber but not the publisher?[vbcol=seagreen]
> Any thoughts?
> Regards
> Adrian
> "Hilary Cotter" wrote:
batches.[vbcol=seagreen]
not[vbcol=seagreen]
a[vbcol=seagreen]
they[vbcol=seagreen]
on[vbcol=seagreen]
subscriber,[vbcol=seagreen]
then[vbcol=seagreen]
a[vbcol=seagreen]
Identity Range not being updated
I have an SQL server 2000 environment where 1 server is the
publisher/database server and several clients using desktop edition sql
Server 2000. This environment is using merge replication. Several of my
tables utilize identity columns so I have my articles defined to
automatically manage identity ranges and I kept the default refresh rate at
80%. For users that are local for this application they use a web
application that connects to the database. Remote users use a windows
desktop application to connect to their subscriber database. When a remote
user returns to the office environment they are allowed to synchronize.
The problem I am encountering is that as users are connecting locally using
the web application to the server database, I had an error occur that stated
the identity range was full. The error told me what table/column that was
having a problem and that it had to be corrected by a replication agent. The
error also reported that I could us sp_adjustpublisheridentityrange to
correct the problem.
My question is why didn't the identity range automatically get corrected if
it was set at 80%?
Also, what is the best practice for how to handle this problem. My concern
is that this is a very heavily used system and how should I keep this error
from occurring?
i think sp_adjustpublisheridentityrange it's being called automatically
only when a subscriber connects and starts a merge job. if your
subscribers stay disconneted for a long time,
sp_adjustpublisheridentityrange won't be called, and you'll have to do
it manually
Guy Thornton ha scritto:
> I have an SQL server 2000 environment where 1 server is the
> publisher/database server and several clients using desktop edition sql
> Server 2000. This environment is using merge replication. Several of my
> tables utilize identity columns so I have my articles defined to
> automatically manage identity ranges and I kept the default refresh rate at
> 80%. For users that are local for this application they use a web
> application that connects to the database. Remote users use a windows
> desktop application to connect to their subscriber database. When a remote
> user returns to the office environment they are allowed to synchronize.
> The problem I am encountering is that as users are connecting locally using
> the web application to the server database, I had an error occur that stated
> the identity range was full. The error told me what table/column that was
> having a problem and that it had to be corrected by a replication agent. The
> error also reported that I could us sp_adjustpublisheridentityrange to
> correct the problem.
> My question is why didn't the identity range automatically get corrected if
> it was set at 80%?
> Also, what is the best practice for how to handle this problem. My concern
> is that this is a very heavily used system and how should I keep this error
> from occurring?
|||It only adjust the range if you sync within the threshold. You need to pick
representative thresholds which will allow the maximum number of expected
inserts within syncs.
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
"Guy Thornton" <wdonotspamthornton@.incresearch.com> wrote in message
news:327C95D4-2582-45E5-8E25-3EBACCF3C0FB@.microsoft.com...
>I have an SQL server 2000 environment where 1 server is the
> publisher/database server and several clients using desktop edition sql
> Server 2000. This environment is using merge replication. Several of my
> tables utilize identity columns so I have my articles defined to
> automatically manage identity ranges and I kept the default refresh rate
> at
> 80%. For users that are local for this application they use a web
> application that connects to the database. Remote users use a windows
> desktop application to connect to their subscriber database. When a
> remote
> user returns to the office environment they are allowed to synchronize.
> The problem I am encountering is that as users are connecting locally
> using
> the web application to the server database, I had an error occur that
> stated
> the identity range was full. The error told me what table/column that was
> having a problem and that it had to be corrected by a replication agent.
> The
> error also reported that I could us sp_adjustpublisheridentityrange to
> correct the problem.
> My question is why didn't the identity range automatically get corrected
> if
> it was set at 80%?
> Also, what is the best practice for how to handle this problem. My
> concern
> is that this is a very heavily used system and how should I keep this
> error
> from occurring?
|||Hilary,
Thanks for your reply. So is this to say that a user must synchronize
within the 80% threshold in order for it to be updated? If true that will be
hard for me to predict and set the threshold appropriately.
I wonder if it would be better for us to schedule a job to run periodically
to update the identity ranges? any ideas?
Thanks.
"Hilary Cotter" wrote:
> It only adjust the range if you sync within the threshold. You need to pick
> representative thresholds which will allow the maximum number of expected
> inserts within syncs.
> --
> 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
>
> "Guy Thornton" <wdonotspamthornton@.incresearch.com> wrote in message
> news:327C95D4-2582-45E5-8E25-3EBACCF3C0FB@.microsoft.com...
>
>
|||No, the user must sync between 80% of the threshold and 100% of the
threshold. Should he/she sync less than 80% of the threshold the range would
not be incremented.
The best approach is to have them sync within this range or set large
ranges.
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
"Guy Thornton" <wdonotspamthornton@.incresearch.com> wrote in message
news:CFADF7BC-E713-46AD-B45B-FA91AFDA41A1@.microsoft.com...[vbcol=seagreen]
> Hilary,
> Thanks for your reply. So is this to say that a user must synchronize
> within the 80% threshold in order for it to be updated? If true that will
> be
> hard for me to predict and set the threshold appropriately.
> I wonder if it would be better for us to schedule a job to run
> periodically
> to update the identity ranges? any ideas?
> Thanks.
> "Hilary Cotter" wrote:
|||The problem I am running into is that my identity ranges are filling up at
the publication server. Even if the user synchronizes within the threshold.
If my users are are not in the db_admin role then would that cause the ranges
not to get updated? If so, how can I work around that? My users cannot be
in the db_admin role.
"Hilary Cotter" wrote:
> No, the user must sync between 80% of the threshold and 100% of the
> threshold. Should he/she sync less than 80% of the threshold the range would
> not be incremented.
> The best approach is to have them sync within this range or set large
> ranges.
> --
> 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
>
> "Guy Thornton" <wdonotspamthornton@.incresearch.com> wrote in message
> news:CFADF7BC-E713-46AD-B45B-FA91AFDA41A1@.microsoft.com...
>
>
|||Hi Guy
I have the same problem with the identity range on the publication server
filling up. Have you found a soloution to this yet?
The subscribers get updated when they sync but not the server. How should I
get the server to update?
Thank you
/ Henrik
"Guy Thornton" wrote:
[vbcol=seagreen]
> The problem I am running into is that my identity ranges are filling up at
> the publication server. Even if the user synchronizes within the threshold.
> If my users are are not in the db_admin role then would that cause the ranges
> not to get updated? If so, how can I work around that? My users cannot be
> in the db_admin role.
> "Hilary Cotter" wrote:
|||Hi Henrik,
To correct the problem, we had to adjust the size of our identity ranges to
account for how often our remote users would synchronize with the server.
Adjust the range so that you have enough at the server to accomodate the
number of inserts expected in between when users synchronize with the server.
We also had to add the users to the sysadmin fixed server role to
automatically update the ranges. Otherwise we would have had to setup an
automated job to run periodically to adjust the ranges.
My concern now is what will happen when my ranges run out. How do I
allocate more ranges over time to ensure continued operation of my
application.
"Henrik" wrote:
[vbcol=seagreen]
> Hi Guy
> I have the same problem with the identity range on the publication server
> filling up. Have you found a soloution to this yet?
> The subscribers get updated when they sync but not the server. How should I
> get the server to update?
> Thank you
> / Henrik
> "Guy Thornton" wrote:
publisher/database server and several clients using desktop edition sql
Server 2000. This environment is using merge replication. Several of my
tables utilize identity columns so I have my articles defined to
automatically manage identity ranges and I kept the default refresh rate at
80%. For users that are local for this application they use a web
application that connects to the database. Remote users use a windows
desktop application to connect to their subscriber database. When a remote
user returns to the office environment they are allowed to synchronize.
The problem I am encountering is that as users are connecting locally using
the web application to the server database, I had an error occur that stated
the identity range was full. The error told me what table/column that was
having a problem and that it had to be corrected by a replication agent. The
error also reported that I could us sp_adjustpublisheridentityrange to
correct the problem.
My question is why didn't the identity range automatically get corrected if
it was set at 80%?
Also, what is the best practice for how to handle this problem. My concern
is that this is a very heavily used system and how should I keep this error
from occurring?
i think sp_adjustpublisheridentityrange it's being called automatically
only when a subscriber connects and starts a merge job. if your
subscribers stay disconneted for a long time,
sp_adjustpublisheridentityrange won't be called, and you'll have to do
it manually
Guy Thornton ha scritto:
> I have an SQL server 2000 environment where 1 server is the
> publisher/database server and several clients using desktop edition sql
> Server 2000. This environment is using merge replication. Several of my
> tables utilize identity columns so I have my articles defined to
> automatically manage identity ranges and I kept the default refresh rate at
> 80%. For users that are local for this application they use a web
> application that connects to the database. Remote users use a windows
> desktop application to connect to their subscriber database. When a remote
> user returns to the office environment they are allowed to synchronize.
> The problem I am encountering is that as users are connecting locally using
> the web application to the server database, I had an error occur that stated
> the identity range was full. The error told me what table/column that was
> having a problem and that it had to be corrected by a replication agent. The
> error also reported that I could us sp_adjustpublisheridentityrange to
> correct the problem.
> My question is why didn't the identity range automatically get corrected if
> it was set at 80%?
> Also, what is the best practice for how to handle this problem. My concern
> is that this is a very heavily used system and how should I keep this error
> from occurring?
|||It only adjust the range if you sync within the threshold. You need to pick
representative thresholds which will allow the maximum number of expected
inserts within syncs.
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
"Guy Thornton" <wdonotspamthornton@.incresearch.com> wrote in message
news:327C95D4-2582-45E5-8E25-3EBACCF3C0FB@.microsoft.com...
>I have an SQL server 2000 environment where 1 server is the
> publisher/database server and several clients using desktop edition sql
> Server 2000. This environment is using merge replication. Several of my
> tables utilize identity columns so I have my articles defined to
> automatically manage identity ranges and I kept the default refresh rate
> at
> 80%. For users that are local for this application they use a web
> application that connects to the database. Remote users use a windows
> desktop application to connect to their subscriber database. When a
> remote
> user returns to the office environment they are allowed to synchronize.
> The problem I am encountering is that as users are connecting locally
> using
> the web application to the server database, I had an error occur that
> stated
> the identity range was full. The error told me what table/column that was
> having a problem and that it had to be corrected by a replication agent.
> The
> error also reported that I could us sp_adjustpublisheridentityrange to
> correct the problem.
> My question is why didn't the identity range automatically get corrected
> if
> it was set at 80%?
> Also, what is the best practice for how to handle this problem. My
> concern
> is that this is a very heavily used system and how should I keep this
> error
> from occurring?
|||Hilary,
Thanks for your reply. So is this to say that a user must synchronize
within the 80% threshold in order for it to be updated? If true that will be
hard for me to predict and set the threshold appropriately.
I wonder if it would be better for us to schedule a job to run periodically
to update the identity ranges? any ideas?
Thanks.
"Hilary Cotter" wrote:
> It only adjust the range if you sync within the threshold. You need to pick
> representative thresholds which will allow the maximum number of expected
> inserts within syncs.
> --
> 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
>
> "Guy Thornton" <wdonotspamthornton@.incresearch.com> wrote in message
> news:327C95D4-2582-45E5-8E25-3EBACCF3C0FB@.microsoft.com...
>
>
|||No, the user must sync between 80% of the threshold and 100% of the
threshold. Should he/she sync less than 80% of the threshold the range would
not be incremented.
The best approach is to have them sync within this range or set large
ranges.
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
"Guy Thornton" <wdonotspamthornton@.incresearch.com> wrote in message
news:CFADF7BC-E713-46AD-B45B-FA91AFDA41A1@.microsoft.com...[vbcol=seagreen]
> Hilary,
> Thanks for your reply. So is this to say that a user must synchronize
> within the 80% threshold in order for it to be updated? If true that will
> be
> hard for me to predict and set the threshold appropriately.
> I wonder if it would be better for us to schedule a job to run
> periodically
> to update the identity ranges? any ideas?
> Thanks.
> "Hilary Cotter" wrote:
|||The problem I am running into is that my identity ranges are filling up at
the publication server. Even if the user synchronizes within the threshold.
If my users are are not in the db_admin role then would that cause the ranges
not to get updated? If so, how can I work around that? My users cannot be
in the db_admin role.
"Hilary Cotter" wrote:
> No, the user must sync between 80% of the threshold and 100% of the
> threshold. Should he/she sync less than 80% of the threshold the range would
> not be incremented.
> The best approach is to have them sync within this range or set large
> ranges.
> --
> 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
>
> "Guy Thornton" <wdonotspamthornton@.incresearch.com> wrote in message
> news:CFADF7BC-E713-46AD-B45B-FA91AFDA41A1@.microsoft.com...
>
>
|||Hi Guy
I have the same problem with the identity range on the publication server
filling up. Have you found a soloution to this yet?
The subscribers get updated when they sync but not the server. How should I
get the server to update?
Thank you
/ Henrik
"Guy Thornton" wrote:
[vbcol=seagreen]
> The problem I am running into is that my identity ranges are filling up at
> the publication server. Even if the user synchronizes within the threshold.
> If my users are are not in the db_admin role then would that cause the ranges
> not to get updated? If so, how can I work around that? My users cannot be
> in the db_admin role.
> "Hilary Cotter" wrote:
|||Hi Henrik,
To correct the problem, we had to adjust the size of our identity ranges to
account for how often our remote users would synchronize with the server.
Adjust the range so that you have enough at the server to accomodate the
number of inserts expected in between when users synchronize with the server.
We also had to add the users to the sysadmin fixed server role to
automatically update the ranges. Otherwise we would have had to setup an
automated job to run periodically to adjust the ranges.
My concern now is what will happen when my ranges run out. How do I
allocate more ranges over time to ensure continued operation of my
application.
"Henrik" wrote:
[vbcol=seagreen]
> Hi Guy
> I have the same problem with the identity range on the publication server
> filling up. Have you found a soloution to this yet?
> The subscribers get updated when they sync but not the server. How should I
> get the server to update?
> Thank you
> / Henrik
> "Guy Thornton" wrote:
Identity range management with web synchronization
I am using SQL Server 2005 replication with anonymous subscriptions and web
synchronization. The server is SQL 2005 Standard and the subscribers are SQL
2005 Express. Every subscriber syncs with the publisher once an hour. There
are no re-publishers.
The subscription databases each run a client program that works on the local
subset of data (partitioning is done by SUSER_SNAME).
My problem is that periodically the client program will fail with an
Identity Range Check Constraint. The error can be fixed by replicating with
the server.
The way I understand SQL2005 replication with SQL2005 subscribers, each
subscriber should be getting a primary and a secondary range of identity
values. When the primary range runs out they should switch to the secondary
range and refresh the primary range (and vice-versa when the secondary range
runs out).
Alternatively, non-SQL2005 subscribers should request a new identity range
when their identity range threshold is exceeded. I have a subscriber identity
range of 10000 and a threshold of 80%. None of the subscribers have inserted
2000 rows between replications (they typically insert no more than 100 rows).
Please help.
<bump>
Let me try again...
I am using web synchronization for merge replication between SQL 2005 Std
and SQL 2005 Express. Occasionally the subscriber databases use their entire
identity range without requesting a new identity range from the publisher.
They request a new range only after the old range has been completely used.
Does anyone have any ideas how to fix this?
Thanks.
"John Van Vliet" wrote:
> I am using SQL Server 2005 replication with anonymous subscriptions and web
> synchronization. The server is SQL 2005 Standard and the subscribers are SQL
> 2005 Express. Every subscriber syncs with the publisher once an hour. There
> are no re-publishers.
> The subscription databases each run a client program that works on the local
> subset of data (partitioning is done by SUSER_SNAME).
> My problem is that periodically the client program will fail with an
> Identity Range Check Constraint. The error can be fixed by replicating with
> the server.
> The way I understand SQL2005 replication with SQL2005 subscribers, each
> subscriber should be getting a primary and a secondary range of identity
> values. When the primary range runs out they should switch to the secondary
> range and refresh the primary range (and vice-versa when the secondary range
> runs out).
> Alternatively, non-SQL2005 subscribers should request a new identity range
> when their identity range threshold is exceeded. I have a subscriber identity
> range of 10000 and a threshold of 80%. None of the subscribers have inserted
> 2000 rows between replications (they typically insert no more than 100 rows).
> Please help.
synchronization. The server is SQL 2005 Standard and the subscribers are SQL
2005 Express. Every subscriber syncs with the publisher once an hour. There
are no re-publishers.
The subscription databases each run a client program that works on the local
subset of data (partitioning is done by SUSER_SNAME).
My problem is that periodically the client program will fail with an
Identity Range Check Constraint. The error can be fixed by replicating with
the server.
The way I understand SQL2005 replication with SQL2005 subscribers, each
subscriber should be getting a primary and a secondary range of identity
values. When the primary range runs out they should switch to the secondary
range and refresh the primary range (and vice-versa when the secondary range
runs out).
Alternatively, non-SQL2005 subscribers should request a new identity range
when their identity range threshold is exceeded. I have a subscriber identity
range of 10000 and a threshold of 80%. None of the subscribers have inserted
2000 rows between replications (they typically insert no more than 100 rows).
Please help.
<bump>
Let me try again...
I am using web synchronization for merge replication between SQL 2005 Std
and SQL 2005 Express. Occasionally the subscriber databases use their entire
identity range without requesting a new identity range from the publisher.
They request a new range only after the old range has been completely used.
Does anyone have any ideas how to fix this?
Thanks.
"John Van Vliet" wrote:
> I am using SQL Server 2005 replication with anonymous subscriptions and web
> synchronization. The server is SQL 2005 Standard and the subscribers are SQL
> 2005 Express. Every subscriber syncs with the publisher once an hour. There
> are no re-publishers.
> The subscription databases each run a client program that works on the local
> subset of data (partitioning is done by SUSER_SNAME).
> My problem is that periodically the client program will fail with an
> Identity Range Check Constraint. The error can be fixed by replicating with
> the server.
> The way I understand SQL2005 replication with SQL2005 subscribers, each
> subscriber should be getting a primary and a secondary range of identity
> values. When the primary range runs out they should switch to the secondary
> range and refresh the primary range (and vice-versa when the secondary range
> runs out).
> Alternatively, non-SQL2005 subscribers should request a new identity range
> when their identity range threshold is exceeded. I have a subscriber identity
> range of 10000 and a threshold of 80%. None of the subscribers have inserted
> 2000 rows between replications (they typically insert no more than 100 rows).
> Please help.
Identity Range Management
Hello all,
I am just beginning to get into replication and have a couple questions for the experts.
When I set up replication (transactional with queued updates), the default identity range was set to 10,000 for the publisher and 1,000 for the subscriber.I understand that if I try to insert 1,100 records at the subscriber, I will get an error saying that the identity range has been exhausted and I need to run sp_adjustpublisheridentityrange on the publisher and the Distribution Agent for the subscriber.
My questions are:
Why wouldn’t someone increase the identity range to a very large number, just in case something happens and the publisher/subscriber link is broken for an extended period of time?What are the ramifications to a larger identity range?
Since identity range management is done by the distributor, what happens if the distributor goes down?Do the publisher and subscribers receive errors when they fill their current ranges?
Thanks,
Jarret
1. If you set it too large, it's possible to exhaust all possible values. i.e. if you have smallint column with publisher having range of 10,000 and two subscribers with range of say 11,000, and one subscriber hits the threshold, that subscriber cannot get new range since you hit datatype limit even though the 2nd subscriber may not have used any of of their range of identities.
2. If distributor goes down, subscriber inserts will run out of identity values and will most likely fail.
You can read more about this in BOL topic "Replicating Identity Columns", http://msdn2.microsoft.com/en-us/library/ms152543.aspx.
Identity range management
I apologize for the repeated post, but my previous posts were not answered
and I'm getting desperate.
I have a SQL2005 Standard publisher with 10 SQL2005 Express subscribers
using web synchronization (anonymous subscriptions). The identity ranges on
the subscribers are not properly updating in at least some cases. The
identity range is 10000 with an 80% threshold but the subscribers run out of
identity values even though they synchronize every hour and never have more
than 150 inserts per sync. Re-syncing after the identity range is full (and
the client sees error messages) fixes the problem.
Is there any reason why an anonymous subscription on SQL2005 Express would
not get a new identity range before filling the old range? Thanks.
No, there is no reason. I think you should open a support incident with PSS
on this one.
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
"John Van Vliet" <JohnVanVliet@.discussions.microsoft.com> wrote in message
news:815687F4-989A-4CAD-A43C-45A942B55037@.microsoft.com...
>I apologize for the repeated post, but my previous posts were not answered
> and I'm getting desperate.
> I have a SQL2005 Standard publisher with 10 SQL2005 Express subscribers
> using web synchronization (anonymous subscriptions). The identity ranges
> on
> the subscribers are not properly updating in at least some cases. The
> identity range is 10000 with an 80% threshold but the subscribers run out
> of
> identity values even though they synchronize every hour and never have
> more
> than 150 inserts per sync. Re-syncing after the identity range is full
> (and
> the client sees error messages) fixes the problem.
> Is there any reason why an anonymous subscription on SQL2005 Express would
> not get a new identity range before filling the old range? Thanks.
>
|||My problem is similar, but in this case the publisher is complaining about
the insertions, so the publication database is the one running out of
identities.
I had seen this happening when the subscriber hasn't replicated for a while
and makes more insertions than the available range. However I am getting the
errors in the publication database, when we insert data from another system:
The insert failed. It conflicted with an identity range check constraint in
database 'Appraisal', replicated table 'dbo.ApprRenExistingVAC', column
'ApprRenExistingVACId'. If the identity column is automatically managed by
replication, update the range as follows: for the Publisher, execute
sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution
Agent or the Merge Agent.
In this case our settings are:
@.pub_identity_range = 10000, @.identity_range = 1000, @.threshold = 80
we have 15 subscribers.
thanks in advance for any input.
"Hilary Cotter" wrote:
> No, there is no reason. I think you should open a support incident with PSS
> on this one.
> --
> 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
>
> "John Van Vliet" <JohnVanVliet@.discussions.microsoft.com> wrote in message
> news:815687F4-989A-4CAD-A43C-45A942B55037@.microsoft.com...
>
>
and I'm getting desperate.
I have a SQL2005 Standard publisher with 10 SQL2005 Express subscribers
using web synchronization (anonymous subscriptions). The identity ranges on
the subscribers are not properly updating in at least some cases. The
identity range is 10000 with an 80% threshold but the subscribers run out of
identity values even though they synchronize every hour and never have more
than 150 inserts per sync. Re-syncing after the identity range is full (and
the client sees error messages) fixes the problem.
Is there any reason why an anonymous subscription on SQL2005 Express would
not get a new identity range before filling the old range? Thanks.
No, there is no reason. I think you should open a support incident with PSS
on this one.
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
"John Van Vliet" <JohnVanVliet@.discussions.microsoft.com> wrote in message
news:815687F4-989A-4CAD-A43C-45A942B55037@.microsoft.com...
>I apologize for the repeated post, but my previous posts were not answered
> and I'm getting desperate.
> I have a SQL2005 Standard publisher with 10 SQL2005 Express subscribers
> using web synchronization (anonymous subscriptions). The identity ranges
> on
> the subscribers are not properly updating in at least some cases. The
> identity range is 10000 with an 80% threshold but the subscribers run out
> of
> identity values even though they synchronize every hour and never have
> more
> than 150 inserts per sync. Re-syncing after the identity range is full
> (and
> the client sees error messages) fixes the problem.
> Is there any reason why an anonymous subscription on SQL2005 Express would
> not get a new identity range before filling the old range? Thanks.
>
|||My problem is similar, but in this case the publisher is complaining about
the insertions, so the publication database is the one running out of
identities.
I had seen this happening when the subscriber hasn't replicated for a while
and makes more insertions than the available range. However I am getting the
errors in the publication database, when we insert data from another system:
The insert failed. It conflicted with an identity range check constraint in
database 'Appraisal', replicated table 'dbo.ApprRenExistingVAC', column
'ApprRenExistingVACId'. If the identity column is automatically managed by
replication, update the range as follows: for the Publisher, execute
sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution
Agent or the Merge Agent.
In this case our settings are:
@.pub_identity_range = 10000, @.identity_range = 1000, @.threshold = 80
we have 15 subscribers.
thanks in advance for any input.
"Hilary Cotter" wrote:
> No, there is no reason. I think you should open a support incident with PSS
> on this one.
> --
> 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
>
> "John Van Vliet" <JohnVanVliet@.discussions.microsoft.com> wrote in message
> news:815687F4-989A-4CAD-A43C-45A942B55037@.microsoft.com...
>
>
identity range management
hi all, got a major problem, having done some mods to my replicated
database, the subscriber, no has primary key violations. im using sql server
identity range management so this should be fine,
i have performed an table upgrade in this way before.
scrpit out the repliation,
remove the replaction from the publisher by deleting the publication and
then using sp_removeddbrepliation.
makeing my changes
then running the script to rebuild the repliaction.
however this time, in serveral tables at the subscriber the id ranges seem
to have gone back to range that have been used.
is there anyway i can get the ranges updated bearing in mind that im using
the auto identity range management.
Thanks Andrew
Andrew,
you could use dbcc checkident to reseed manually, edit the check constraints
accordingly, and change the values in MSrepl_identity_range on the
subscriber. This table is used to check if the subscriber has used up its
range or reached the threshold. The new range you set would be obtained from
MSrepl_identity_range on the distributor, which is the master table and is
used to generate new values. The values in this table (MSrepl_identity_range
on the distributor) would need to be changed to avoid a future potential
conflict.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||After dropping the publication you will need to connect to the subscribers
and drop the replication check constraints on the tables. This is a
"problem" I have reported to Microsoft.
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
"andrew bourne" <andrewbourne@.vardells.com> wrote in message
news:eMFNzaihFHA.1948@.TK2MSFTNGP12.phx.gbl...
> hi all, got a major problem, having done some mods to my replicated
> database, the subscriber, no has primary key violations. im using sql
server
> identity range management so this should be fine,
> i have performed an table upgrade in this way before.
> scrpit out the repliation,
> remove the replaction from the publisher by deleting the publication and
> then using sp_removeddbrepliation.
> makeing my changes
> then running the script to rebuild the repliaction.
> however this time, in serveral tables at the subscriber the id ranges seem
> to have gone back to range that have been used.
> is there anyway i can get the ranges updated bearing in mind that im using
> the auto identity range management.
> Thanks Andrew
>
|||hi all
i have stopped client connecteding to the subcriber
ok so if at the subscriber i change the next_seed value to the current_max
value in the msrepl_identity_range.
at the distributer i change the next_seed value to a range that is out of
the way.
If i then run the merge agent, will that pick up that the subcribers ranges
need changeing and change them to the value in the next_seed specified in
the distributor.
Thanks In Advance Andrew
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eij4KLjhFHA.576@.tk2msftngp13.phx.gbl...
> Andrew,
> you could use dbcc checkident to reseed manually, edit the check
> constraints accordingly, and change the values in MSrepl_identity_range on
> the subscriber. This table is used to check if the subscriber has used up
> its range or reached the threshold. The new range you set would be
> obtained from MSrepl_identity_range on the distributor, which is the
> master table and is used to generate new values. The values in this table
> (MSrepl_identity_range on the distributor) would need to be changed to
> avoid a future potential conflict.
> HTH
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Andrew,
I'm suggesting you bypass the automatic management and assign a range
yourself. Setting the values in msrepl_identity_range on the subscriber for
the range you want, msrepl_identity_range on the distributor to make sure it
is greater than the subscriber range, issuing a dbcc checkident, and
changing the check constraints will allow things to proceed as per normal,
and running the merge agent will register that anything has been changed
manually.
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
database, the subscriber, no has primary key violations. im using sql server
identity range management so this should be fine,
i have performed an table upgrade in this way before.
scrpit out the repliation,
remove the replaction from the publisher by deleting the publication and
then using sp_removeddbrepliation.
makeing my changes
then running the script to rebuild the repliaction.
however this time, in serveral tables at the subscriber the id ranges seem
to have gone back to range that have been used.
is there anyway i can get the ranges updated bearing in mind that im using
the auto identity range management.
Thanks Andrew
Andrew,
you could use dbcc checkident to reseed manually, edit the check constraints
accordingly, and change the values in MSrepl_identity_range on the
subscriber. This table is used to check if the subscriber has used up its
range or reached the threshold. The new range you set would be obtained from
MSrepl_identity_range on the distributor, which is the master table and is
used to generate new values. The values in this table (MSrepl_identity_range
on the distributor) would need to be changed to avoid a future potential
conflict.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||After dropping the publication you will need to connect to the subscribers
and drop the replication check constraints on the tables. This is a
"problem" I have reported to Microsoft.
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
"andrew bourne" <andrewbourne@.vardells.com> wrote in message
news:eMFNzaihFHA.1948@.TK2MSFTNGP12.phx.gbl...
> hi all, got a major problem, having done some mods to my replicated
> database, the subscriber, no has primary key violations. im using sql
server
> identity range management so this should be fine,
> i have performed an table upgrade in this way before.
> scrpit out the repliation,
> remove the replaction from the publisher by deleting the publication and
> then using sp_removeddbrepliation.
> makeing my changes
> then running the script to rebuild the repliaction.
> however this time, in serveral tables at the subscriber the id ranges seem
> to have gone back to range that have been used.
> is there anyway i can get the ranges updated bearing in mind that im using
> the auto identity range management.
> Thanks Andrew
>
|||hi all
i have stopped client connecteding to the subcriber
ok so if at the subscriber i change the next_seed value to the current_max
value in the msrepl_identity_range.
at the distributer i change the next_seed value to a range that is out of
the way.
If i then run the merge agent, will that pick up that the subcribers ranges
need changeing and change them to the value in the next_seed specified in
the distributor.
Thanks In Advance Andrew
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eij4KLjhFHA.576@.tk2msftngp13.phx.gbl...
> Andrew,
> you could use dbcc checkident to reseed manually, edit the check
> constraints accordingly, and change the values in MSrepl_identity_range on
> the subscriber. This table is used to check if the subscriber has used up
> its range or reached the threshold. The new range you set would be
> obtained from MSrepl_identity_range on the distributor, which is the
> master table and is used to generate new values. The values in this table
> (MSrepl_identity_range on the distributor) would need to be changed to
> avoid a future potential conflict.
> HTH
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Andrew,
I'm suggesting you bypass the automatic management and assign a range
yourself. Setting the values in msrepl_identity_range on the subscriber for
the range you want, msrepl_identity_range on the distributor to make sure it
is greater than the subscriber range, issuing a dbcc checkident, and
changing the check constraints will allow things to proceed as per normal,
and running the merge agent will register that anything has been changed
manually.
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Identity Range Management
Good morning All,
In the context of a merge replica:
Does this range serve as surrogate key (Primary Key) but after the sync, the
server overlooks it when inserting new rows in the published table and
generate a sequential one?
Here's what I am trying to achieve:
My disconnected users, have to issue unique File# to their customers.
Once a file number is given to a customer, the customer will use this file
number to reference his/her case for ever.
I want this number to be the unique identifier of his/her record in my table.
If I use this range model, then that would solve the problem provided that
the server will use this number as the primary key for the table where
customer cases are stored.
I read a few articles about the range model but it never read that this
number is kept or used as a primary key in a table.
What am I missing here?
Also
How can we reduce the large skipped numbers not used between sync?
Thanks
YOW
Yes. So the identity column which may or may not be the primary key or may
be one of the columns involved in the primary key could appear on two nodes
simultaneously if Automatic Identity Range Management is not working
correctly. This will lead to a conflict when the merge agent runs and can
cause one of the inserts to be rolled back and replaced by the winning
insert.
If it is working correctly or if there are other columns in the primary key,
or if the identity value is the only key in a primary key or unique index
and you are perfectly partitioned it should work.
Automatic identity range management promotes efficient use of the identity
ranges. If you notice that the ranges are not being used efficiently on the
publisher lower the publisher range. If it is not being used efficiently on
all subscribers, lower that range. If it is not being used efficiently on
one or the subscribers, but is on the other you are out of luck.
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
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message news:C2F1728A-0241-412E-AF9D-CAB3BE2CB04C@.microsoft.com...
> Good morning All,
> In the context of a merge replica:
> Does this range serve as surrogate key (Primary Key) but after the sync,
the
> server overlooks it when inserting new rows in the published table and
> generate a sequential one?
> Here's what I am trying to achieve:
> My disconnected users, have to issue unique File# to their customers.
> Once a file number is given to a customer, the customer will use this file
> number to reference his/her case for ever.
> I want this number to be the unique identifier of his/her record in my
table.
> If I use this range model, then that would solve the problem provided that
> the server will use this number as the primary key for the table where
> customer cases are stored.
> I read a few articles about the range model but it never read that this
> number is kept or used as a primary key in a table.
> What am I missing here?
> Also
> How can we reduce the large skipped numbers not used between sync?
> Thanks
> YOW
|||What do you think if i go for ROWGUID instead?
"Hilary Cotter" wrote:
> Yes. So the identity column which may or may not be the primary key or may
> be one of the columns involved in the primary key could appear on two nodes
> simultaneously if Automatic Identity Range Management is not working
> correctly. This will lead to a conflict when the merge agent runs and can
> cause one of the inserts to be rolled back and replaced by the winning
> insert.
> If it is working correctly or if there are other columns in the primary key,
> or if the identity value is the only key in a primary key or unique index
> and you are perfectly partitioned it should work.
> Automatic identity range management promotes efficient use of the identity
> ranges. If you notice that the ranges are not being used efficiently on the
> publisher lower the publisher range. If it is not being used efficiently on
> all subscribers, lower that range. If it is not being used efficiently on
> one or the subscribers, but is on the other you are out of luck.
> --
> 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
> "Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
> message news:C2F1728A-0241-412E-AF9D-CAB3BE2CB04C@.microsoft.com...
> the
> table.
>
>
|||They tend not to make good PK's. Have a look at this for more info on why
not.
http://www.aspfaq.com/show.asp?id=2504
Its in the bottom section.
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
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message news:8587BCD9-84AB-4719-BFFA-8A7BD607A847@.microsoft.com...[vbcol=seagreen]
> What do you think if i go for ROWGUID instead?
> "Hilary Cotter" wrote:
may[vbcol=seagreen]
nodes[vbcol=seagreen]
can[vbcol=seagreen]
key,[vbcol=seagreen]
index[vbcol=seagreen]
identity[vbcol=seagreen]
the[vbcol=seagreen]
on[vbcol=seagreen]
on[vbcol=seagreen]
in[vbcol=seagreen]
sync,[vbcol=seagreen]
file[vbcol=seagreen]
that[vbcol=seagreen]
this[vbcol=seagreen]
|||From your first answer:
if Automatic Identity Range Management is not working correctly. This will
lead to a conflict when the merge agent runs and can cause one of the
inserts to be rolled back and replaced by the winning insert"
What could make Automated IRM not work properly?
Thanks
"Hilary Cotter" wrote:
> They tend not to make good PK's. Have a look at this for more info on why
> not.
> http://www.aspfaq.com/show.asp?id=2504
> Its in the bottom section.
> --
> 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
> "Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
> message news:8587BCD9-84AB-4719-BFFA-8A7BD607A847@.microsoft.com...
> may
> nodes
> can
> key,
> index
> identity
> the
> on
> on
> in
> sync,
> file
> that
> this
>
>
|||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
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message news:37219273-4565-47B0-AB13-1D01132A0658@.microsoft.com...
> From your first answer:
> if Automatic Identity Range Management is not working correctly. This
will[vbcol=seagreen]
> lead to a conflict when the merge agent runs and can cause one of the
> inserts to be rolled back and replaced by the winning insert"
> What could make Automated IRM not work properly?
>
> Thanks
> "Hilary Cotter" wrote:
why[vbcol=seagreen]
in[vbcol=seagreen]
or[vbcol=seagreen]
two[vbcol=seagreen]
and[vbcol=seagreen]
winning[vbcol=seagreen]
primary[vbcol=seagreen]
on[vbcol=seagreen]
efficiently[vbcol=seagreen]
efficiently[vbcol=seagreen]
wrote[vbcol=seagreen]
and[vbcol=seagreen]
customers.[vbcol=seagreen]
this[vbcol=seagreen]
in my[vbcol=seagreen]
provided[vbcol=seagreen]
where[vbcol=seagreen]
|||I'd like to say bugs, but I am not really convinced that there are bugs with
this. Properly sized it does seem to work well - or at least it has worked
well for us on several installations.
If you don't size your data type or your ranges correctly or run the agent
in continuous mode, it might not update the ranges in time. This seems to be
the biggest problem with it.
It also seems that if you are monkeying around with the metadata that the
merge agent will not detect that an article is under automatic range
management when it runs and then check the local and remote ranges. It
normally does this check when it first runs. I have seen cases where the
detection proc never runs, but when I try to repro it on a clean database I
am unable to do so. So I think its something i have messed up along the way.
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
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message news:37219273-4565-47B0-AB13-1D01132A0658@.microsoft.com...
> From your first answer:
> if Automatic Identity Range Management is not working correctly. This
will[vbcol=seagreen]
> lead to a conflict when the merge agent runs and can cause one of the
> inserts to be rolled back and replaced by the winning insert"
> What could make Automated IRM not work properly?
>
> Thanks
> "Hilary Cotter" wrote:
why[vbcol=seagreen]
in[vbcol=seagreen]
or[vbcol=seagreen]
two[vbcol=seagreen]
and[vbcol=seagreen]
winning[vbcol=seagreen]
primary[vbcol=seagreen]
on[vbcol=seagreen]
efficiently[vbcol=seagreen]
efficiently[vbcol=seagreen]
wrote[vbcol=seagreen]
and[vbcol=seagreen]
customers.[vbcol=seagreen]
this[vbcol=seagreen]
in my[vbcol=seagreen]
provided[vbcol=seagreen]
where[vbcol=seagreen]
In the context of a merge replica:
Does this range serve as surrogate key (Primary Key) but after the sync, the
server overlooks it when inserting new rows in the published table and
generate a sequential one?
Here's what I am trying to achieve:
My disconnected users, have to issue unique File# to their customers.
Once a file number is given to a customer, the customer will use this file
number to reference his/her case for ever.
I want this number to be the unique identifier of his/her record in my table.
If I use this range model, then that would solve the problem provided that
the server will use this number as the primary key for the table where
customer cases are stored.
I read a few articles about the range model but it never read that this
number is kept or used as a primary key in a table.
What am I missing here?
Also
How can we reduce the large skipped numbers not used between sync?
Thanks
YOW
Yes. So the identity column which may or may not be the primary key or may
be one of the columns involved in the primary key could appear on two nodes
simultaneously if Automatic Identity Range Management is not working
correctly. This will lead to a conflict when the merge agent runs and can
cause one of the inserts to be rolled back and replaced by the winning
insert.
If it is working correctly or if there are other columns in the primary key,
or if the identity value is the only key in a primary key or unique index
and you are perfectly partitioned it should work.
Automatic identity range management promotes efficient use of the identity
ranges. If you notice that the ranges are not being used efficiently on the
publisher lower the publisher range. If it is not being used efficiently on
all subscribers, lower that range. If it is not being used efficiently on
one or the subscribers, but is on the other you are out of luck.
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
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message news:C2F1728A-0241-412E-AF9D-CAB3BE2CB04C@.microsoft.com...
> Good morning All,
> In the context of a merge replica:
> Does this range serve as surrogate key (Primary Key) but after the sync,
the
> server overlooks it when inserting new rows in the published table and
> generate a sequential one?
> Here's what I am trying to achieve:
> My disconnected users, have to issue unique File# to their customers.
> Once a file number is given to a customer, the customer will use this file
> number to reference his/her case for ever.
> I want this number to be the unique identifier of his/her record in my
table.
> If I use this range model, then that would solve the problem provided that
> the server will use this number as the primary key for the table where
> customer cases are stored.
> I read a few articles about the range model but it never read that this
> number is kept or used as a primary key in a table.
> What am I missing here?
> Also
> How can we reduce the large skipped numbers not used between sync?
> Thanks
> YOW
|||What do you think if i go for ROWGUID instead?
"Hilary Cotter" wrote:
> Yes. So the identity column which may or may not be the primary key or may
> be one of the columns involved in the primary key could appear on two nodes
> simultaneously if Automatic Identity Range Management is not working
> correctly. This will lead to a conflict when the merge agent runs and can
> cause one of the inserts to be rolled back and replaced by the winning
> insert.
> If it is working correctly or if there are other columns in the primary key,
> or if the identity value is the only key in a primary key or unique index
> and you are perfectly partitioned it should work.
> Automatic identity range management promotes efficient use of the identity
> ranges. If you notice that the ranges are not being used efficiently on the
> publisher lower the publisher range. If it is not being used efficiently on
> all subscribers, lower that range. If it is not being used efficiently on
> one or the subscribers, but is on the other you are out of luck.
> --
> 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
> "Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
> message news:C2F1728A-0241-412E-AF9D-CAB3BE2CB04C@.microsoft.com...
> the
> table.
>
>
|||They tend not to make good PK's. Have a look at this for more info on why
not.
http://www.aspfaq.com/show.asp?id=2504
Its in the bottom section.
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
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message news:8587BCD9-84AB-4719-BFFA-8A7BD607A847@.microsoft.com...[vbcol=seagreen]
> What do you think if i go for ROWGUID instead?
> "Hilary Cotter" wrote:
may[vbcol=seagreen]
nodes[vbcol=seagreen]
can[vbcol=seagreen]
key,[vbcol=seagreen]
index[vbcol=seagreen]
identity[vbcol=seagreen]
the[vbcol=seagreen]
on[vbcol=seagreen]
on[vbcol=seagreen]
in[vbcol=seagreen]
sync,[vbcol=seagreen]
file[vbcol=seagreen]
that[vbcol=seagreen]
this[vbcol=seagreen]
|||From your first answer:
if Automatic Identity Range Management is not working correctly. This will
lead to a conflict when the merge agent runs and can cause one of the
inserts to be rolled back and replaced by the winning insert"
What could make Automated IRM not work properly?
Thanks
"Hilary Cotter" wrote:
> They tend not to make good PK's. Have a look at this for more info on why
> not.
> http://www.aspfaq.com/show.asp?id=2504
> Its in the bottom section.
> --
> 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
> "Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
> message news:8587BCD9-84AB-4719-BFFA-8A7BD607A847@.microsoft.com...
> may
> nodes
> can
> key,
> index
> identity
> the
> on
> on
> in
> sync,
> file
> that
> this
>
>
|||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
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message news:37219273-4565-47B0-AB13-1D01132A0658@.microsoft.com...
> From your first answer:
> if Automatic Identity Range Management is not working correctly. This
will[vbcol=seagreen]
> lead to a conflict when the merge agent runs and can cause one of the
> inserts to be rolled back and replaced by the winning insert"
> What could make Automated IRM not work properly?
>
> Thanks
> "Hilary Cotter" wrote:
why[vbcol=seagreen]
in[vbcol=seagreen]
or[vbcol=seagreen]
two[vbcol=seagreen]
and[vbcol=seagreen]
winning[vbcol=seagreen]
primary[vbcol=seagreen]
on[vbcol=seagreen]
efficiently[vbcol=seagreen]
efficiently[vbcol=seagreen]
wrote[vbcol=seagreen]
and[vbcol=seagreen]
customers.[vbcol=seagreen]
this[vbcol=seagreen]
in my[vbcol=seagreen]
provided[vbcol=seagreen]
where[vbcol=seagreen]
|||I'd like to say bugs, but I am not really convinced that there are bugs with
this. Properly sized it does seem to work well - or at least it has worked
well for us on several installations.
If you don't size your data type or your ranges correctly or run the agent
in continuous mode, it might not update the ranges in time. This seems to be
the biggest problem with it.
It also seems that if you are monkeying around with the metadata that the
merge agent will not detect that an article is under automatic range
management when it runs and then check the local and remote ranges. It
normally does this check when it first runs. I have seen cases where the
detection proc never runs, but when I try to repro it on a clean database I
am unable to do so. So I think its something i have messed up along the way.
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
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message news:37219273-4565-47B0-AB13-1D01132A0658@.microsoft.com...
> From your first answer:
> if Automatic Identity Range Management is not working correctly. This
will[vbcol=seagreen]
> lead to a conflict when the merge agent runs and can cause one of the
> inserts to be rolled back and replaced by the winning insert"
> What could make Automated IRM not work properly?
>
> Thanks
> "Hilary Cotter" wrote:
why[vbcol=seagreen]
in[vbcol=seagreen]
or[vbcol=seagreen]
two[vbcol=seagreen]
and[vbcol=seagreen]
winning[vbcol=seagreen]
primary[vbcol=seagreen]
on[vbcol=seagreen]
efficiently[vbcol=seagreen]
efficiently[vbcol=seagreen]
wrote[vbcol=seagreen]
and[vbcol=seagreen]
customers.[vbcol=seagreen]
this[vbcol=seagreen]
in my[vbcol=seagreen]
provided[vbcol=seagreen]
where[vbcol=seagreen]
Identity range managed by replication is full and must be updated by a replication agent.
Hello,
You have better luck in microsoft.public.sqlsever.replication. Myself,
I have very little experience of replication.
I'm getting the following error message when I try add a row using a
Stored Procedure.
"The identity range managed by replication is full and must be updated
by a replication agent".
I read up on the subject and have tried the following solutions
according to MSDN without any luck.(http://support.Microsoft.com/kb/
304706 )
sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/
library/aa239401(SQL.80).aspx ) has no effect
For Testing:
I've reloaded everything from scratch, created the pulications from by
running the sql scripts generated,created replication snapshots and
started the agents.
I've checked the current Identity values in the Agent Table:
DBCC CHECKIDENT ('Agent', NORESEED)
Checking identity information: current identity value '18606', current
column value '18606'.
I check the Table to make sure there will be no conflicts with the
primary key:
SELECT AgentID FROM Agent ORDER BY AgentID DESC
18603 is the largest AgentID in the table.
Using the Table Article Properties in the Publications Properties
Dialog, I can see values of:
Range Size at Publisher: 100,000
Range Size at Subscribers: 100
New range @. percentage: 80
In my mind this means that the Publisher will assign a new range when
the Current Indentity value goes over 80,000?
The Identity range for this table cannot be exhausted! I'm not sure
what to try next.
Please! any insight will be of great help!
Regards,
Bm(miller.brettm@.gmail.com) writes:
Quote:
Originally Posted by
I'm getting the following error message when I try add a row using a
Stored Procedure.
>
"The identity range managed by replication is full and must be updated
by a replication agent".
>
I read up on the subject and have tried the following solutions
according to MSDN without any luck.(http://support.Microsoft.com/kb/
304706 )
>
sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/
library/aa239401(SQL.80).aspx ) has no effect
You have better luck in microsoft.public.sqlsever.replication. Myself,
I have very little experience of replication.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Identity Range Issue
Hi - I have a peculiar problem. I have been able to successfully add
merge articles to a publication (both in script and through EM) with identiy
range in a instance of sql server. Whenever I try to do the same thing in a
default installation of sql server, I get a syntax error.
I have been able to find out that the error occurs because the identity
column has spaces in its name (e.g. [ID for Category] - the error says there
is a syntax error near the key word 'for', but it can be any other word
after the space). If I don't use identity range, I can add the article fine.
So here is my situation:
1. I need to keep the column names the same for legacy reasons.
2. I need to use identity range.
3. I need to be able to do this both in an instance and default
installation of sql server.
What puzzles me the most is why this is not an issue in a instance of
sql server.
I hadn't found out about this problem until recently because I had
always worked with an instance in this application.
So I have two questions:
Why does this only happen in a default installation?
What can I do to make it work in a default installation?
Any help will be greatly appreciated,
Maer
I'll try to reproduce it tomorrow, but my first thought is that the service
pack level might be different between the default and named instances - can
you check that for us.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
The service pack level was indeed different. As a matter of fact, I had
forgotten to apply any service pack to the default installation. After
applying the latest pack, it worked.
Thanks,
Maer
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:urYbMjcdFHA.3492@.TK2MSFTNGP14.phx.gbl...
> I'll try to reproduce it tomorrow, but my first thought is that the
> service pack level might be different between the default and named
> instances - can you check that for us.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
merge articles to a publication (both in script and through EM) with identiy
range in a instance of sql server. Whenever I try to do the same thing in a
default installation of sql server, I get a syntax error.
I have been able to find out that the error occurs because the identity
column has spaces in its name (e.g. [ID for Category] - the error says there
is a syntax error near the key word 'for', but it can be any other word
after the space). If I don't use identity range, I can add the article fine.
So here is my situation:
1. I need to keep the column names the same for legacy reasons.
2. I need to use identity range.
3. I need to be able to do this both in an instance and default
installation of sql server.
What puzzles me the most is why this is not an issue in a instance of
sql server.
I hadn't found out about this problem until recently because I had
always worked with an instance in this application.
So I have two questions:
Why does this only happen in a default installation?
What can I do to make it work in a default installation?
Any help will be greatly appreciated,
Maer
I'll try to reproduce it tomorrow, but my first thought is that the service
pack level might be different between the default and named instances - can
you check that for us.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
The service pack level was indeed different. As a matter of fact, I had
forgotten to apply any service pack to the default installation. After
applying the latest pack, it worked.
Thanks,
Maer
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:urYbMjcdFHA.3492@.TK2MSFTNGP14.phx.gbl...
> I'll try to reproduce it tomorrow, but my first thought is that the
> service pack level might be different between the default and named
> instances - can you check that for us.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
identity range check constraint
SQL 2005 merge replication.
I took the defaults on an article for publication - auto identity
management and the default ranges. If I attempt to insert >2000 rows
(the size of the 2 ranges assigned), I recieve the following error:
The insert failed. It conflicted with an identity range check
constraint in database 'DHD_73', replicated table
'dbo.tblAlaska_Facility_Manager', column 'facilityID'. If the identity
column is automatically managed by replication, update the range as
follows: for the Publisher, execute sp_adjustpublisheridentityrange;
for the Subscriber, run the Distribution Agent or the Merge Agent.
Does this mean I cannot do an insert of more than 2000 rows at a time
to this particular article?
TIA,
john g.
Sorry, I forgot to add, I am doing the inserts on the publisher
jg
jgmein...@.gmail.com wrote:
> SQL 2005 merge replication.
> I took the defaults on an article for publication - auto identity
> management and the default ranges. If I attempt to insert >2000 rows
> (the size of the 2 ranges assigned), I recieve the following error:
> The insert failed. It conflicted with an identity range check
> constraint in database 'DHD_73', replicated table
> 'dbo.tblAlaska_Facility_Manager', column 'facilityID'. If the identity
> column is automatically managed by replication, update the range as
> follows: for the Publisher, execute sp_adjustpublisheridentityrange;
> for the Subscriber, run the Distribution Agent or the Merge Agent.
> Does this mean I cannot do an insert of more than 2000 rows at a time
> to this particular article?
> TIA,
> john g.
|||You can batch up the inserts, or increase the size of the assigned rabge,
otherwise you're stuck.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Check the constraint to see what the range is. You can insert up to this
value and then run a sync. This should update the range.
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
<jgmeinder@.gmail.com> wrote in message
news:1167937361.253647.109410@.51g2000cwl.googlegro ups.com...
> SQL 2005 merge replication.
> I took the defaults on an article for publication - auto identity
> management and the default ranges. If I attempt to insert >2000 rows
> (the size of the 2 ranges assigned), I recieve the following error:
> The insert failed. It conflicted with an identity range check
> constraint in database 'DHD_73', replicated table
> 'dbo.tblAlaska_Facility_Manager', column 'facilityID'. If the identity
> column is automatically managed by replication, update the range as
> follows: for the Publisher, execute sp_adjustpublisheridentityrange;
> for the Subscriber, run the Distribution Agent or the Merge Agent.
> Does this mean I cannot do an insert of more than 2000 rows at a time
> to this particular article?
> TIA,
> john g.
>
I took the defaults on an article for publication - auto identity
management and the default ranges. If I attempt to insert >2000 rows
(the size of the 2 ranges assigned), I recieve the following error:
The insert failed. It conflicted with an identity range check
constraint in database 'DHD_73', replicated table
'dbo.tblAlaska_Facility_Manager', column 'facilityID'. If the identity
column is automatically managed by replication, update the range as
follows: for the Publisher, execute sp_adjustpublisheridentityrange;
for the Subscriber, run the Distribution Agent or the Merge Agent.
Does this mean I cannot do an insert of more than 2000 rows at a time
to this particular article?
TIA,
john g.
Sorry, I forgot to add, I am doing the inserts on the publisher
jg
jgmein...@.gmail.com wrote:
> SQL 2005 merge replication.
> I took the defaults on an article for publication - auto identity
> management and the default ranges. If I attempt to insert >2000 rows
> (the size of the 2 ranges assigned), I recieve the following error:
> The insert failed. It conflicted with an identity range check
> constraint in database 'DHD_73', replicated table
> 'dbo.tblAlaska_Facility_Manager', column 'facilityID'. If the identity
> column is automatically managed by replication, update the range as
> follows: for the Publisher, execute sp_adjustpublisheridentityrange;
> for the Subscriber, run the Distribution Agent or the Merge Agent.
> Does this mean I cannot do an insert of more than 2000 rows at a time
> to this particular article?
> TIA,
> john g.
|||You can batch up the inserts, or increase the size of the assigned rabge,
otherwise you're stuck.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Check the constraint to see what the range is. You can insert up to this
value and then run a sync. This should update the range.
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
<jgmeinder@.gmail.com> wrote in message
news:1167937361.253647.109410@.51g2000cwl.googlegro ups.com...
> SQL 2005 merge replication.
> I took the defaults on an article for publication - auto identity
> management and the default ranges. If I attempt to insert >2000 rows
> (the size of the 2 ranges assigned), I recieve the following error:
> The insert failed. It conflicted with an identity range check
> constraint in database 'DHD_73', replicated table
> 'dbo.tblAlaska_Facility_Manager', column 'facilityID'. If the identity
> column is automatically managed by replication, update the range as
> follows: for the Publisher, execute sp_adjustpublisheridentityrange;
> for the Subscriber, run the Distribution Agent or the Merge Agent.
> Does this mean I cannot do an insert of more than 2000 rows at a time
> to this particular article?
> TIA,
> john g.
>
Identity Range
Hi,
It has been a while that I have been struggling with an issue of Identity
Range. I have manually assigned Identity Ranges to the table. Inspite of
assigning a good range of 1000 records after adding only 100 records the
table says that it is out of identity range. Why is that?
Please help.
Thanks
issue a dbcc checkident('MytableName') and see what it says. Chances are
that you have blown the range somehow.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Microsoft Newsgroup" <joebloggs@.abc.org> wrote in message
news:%23wEyU369GHA.4268@.TK2MSFTNGP02.phx.gbl...
> Hi,
> It has been a while that I have been struggling with an issue of Identity
> Range. I have manually assigned Identity Ranges to the table. Inspite of
> assigning a good range of 1000 records after adding only 100 records the
> table says that it is out of identity range. Why is that?
> Please help.
> Thanks
>
|||I am wondering how did a user blow up the range without entering any data...
here is the result from the dbcc
Checking identity information: current identity value '230030', current
column value '310054'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OJCF3K79GHA.4468@.TK2MSFTNGP05.phx.gbl...
> issue a dbcc checkident('MytableName') and see what it says. Chances are
> that you have blown the range somehow.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Microsoft Newsgroup" <joebloggs@.abc.org> wrote in message
> news:%23wEyU369GHA.4268@.TK2MSFTNGP02.phx.gbl...
>
It has been a while that I have been struggling with an issue of Identity
Range. I have manually assigned Identity Ranges to the table. Inspite of
assigning a good range of 1000 records after adding only 100 records the
table says that it is out of identity range. Why is that?
Please help.
Thanks
issue a dbcc checkident('MytableName') and see what it says. Chances are
that you have blown the range somehow.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Microsoft Newsgroup" <joebloggs@.abc.org> wrote in message
news:%23wEyU369GHA.4268@.TK2MSFTNGP02.phx.gbl...
> Hi,
> It has been a while that I have been struggling with an issue of Identity
> Range. I have manually assigned Identity Ranges to the table. Inspite of
> assigning a good range of 1000 records after adding only 100 records the
> table says that it is out of identity range. Why is that?
> Please help.
> Thanks
>
|||I am wondering how did a user blow up the range without entering any data...
here is the result from the dbcc
Checking identity information: current identity value '230030', current
column value '310054'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OJCF3K79GHA.4468@.TK2MSFTNGP05.phx.gbl...
> issue a dbcc checkident('MytableName') and see what it says. Chances are
> that you have blown the range somehow.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Microsoft Newsgroup" <joebloggs@.abc.org> wrote in message
> news:%23wEyU369GHA.4268@.TK2MSFTNGP02.phx.gbl...
>
Monday, March 12, 2012
Identity problem. (Need generate on subscriber new identity values)
If you change the publication to queued updating, use
automatic identity range management and don't run the
queue reader, you should achieve what you require.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
But where in that case subcribers updates will be stored? On distributor?
Is it known bug with incorrect handling identity values in replications? Or
it is my own issue?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:050b01c50853$c6e94010$a601280a@.phx.gbl...
> If you change the publication to queued updating, use
> automatic identity range management and don't run the
> queue reader, you should achieve what you require.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||The subscriber updates will be held on the queue table at
the subscriber, or you could remove the subscriber
triggers. It is assumed that subscribers are read only in
normal circumstances, so any issues regarding data
changes at a subscriber and problems with identities on
the subscriber are not really catered for. Identities are
only coded for if the subscriber uses immediate updating
or queued updating.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
automatic identity range management and don't run the
queue reader, you should achieve what you require.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
But where in that case subcribers updates will be stored? On distributor?
Is it known bug with incorrect handling identity values in replications? Or
it is my own issue?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:050b01c50853$c6e94010$a601280a@.phx.gbl...
> If you change the publication to queued updating, use
> automatic identity range management and don't run the
> queue reader, you should achieve what you require.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||The subscriber updates will be held on the queue table at
the subscriber, or you could remove the subscriber
triggers. It is assumed that subscribers are read only in
normal circumstances, so any issues regarding data
changes at a subscriber and problems with identities on
the subscriber are not really catered for. Identities are
only coded for if the subscriber uses immediate updating
or queued updating.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Identity Management
Hi,
I have a merge replication going on. Say, I have a subscriber with an
identity range from 1000 to 1200 and I have only used 100 identities of that
range. For some what reason I have to reinitialise the subscription. What
publisher does is it allocates a new set of range to the table i.e. 1300 -
1400 to the article on subscription. As you can see I have simply wasted 100
identities i.e. from 1100 to 1200. Please note identity allocation is
happening automatically.
Is there a way that I can avoid the wastage?
How can I deduce using system stored procedure that what identities are in
use for each article on each subscriber?
Thanks
Vivek
This is normally not a concern.
Basically replication does not track which identity ranges are in place by
subscribers. All it does is track the last deployed identity range and the
range in use on the publication database.
While I don't recommend it, you can go to the subscriber and do a dbcc
checkident('TableName',reseed,101) and then run a sp_help TableName identify
the replication constraint which limits the identity values which can be
used in the subscriber, and modify it for the range you wish to use.
Again I don't recommend doing this.
You might want to check this link for more on this feature.
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
"Vivek Sharma" <vivek_nz76@.hotmail.com> wrote in message
news:e97gXp$3FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a merge replication going on. Say, I have a subscriber with an
> identity range from 1000 to 1200 and I have only used 100 identities of
that
> range. For some what reason I have to reinitialise the subscription. What
> publisher does is it allocates a new set of range to the table i.e. 1300 -
> 1400 to the article on subscription. As you can see I have simply wasted
100
> identities i.e. from 1100 to 1200. Please note identity allocation is
> happening automatically.
> Is there a way that I can avoid the wastage?
> How can I deduce using system stored procedure that what identities are in
> use for each article on each subscriber?
> Thanks
> Vivek
>
I have a merge replication going on. Say, I have a subscriber with an
identity range from 1000 to 1200 and I have only used 100 identities of that
range. For some what reason I have to reinitialise the subscription. What
publisher does is it allocates a new set of range to the table i.e. 1300 -
1400 to the article on subscription. As you can see I have simply wasted 100
identities i.e. from 1100 to 1200. Please note identity allocation is
happening automatically.
Is there a way that I can avoid the wastage?
How can I deduce using system stored procedure that what identities are in
use for each article on each subscriber?
Thanks
Vivek
This is normally not a concern.
Basically replication does not track which identity ranges are in place by
subscribers. All it does is track the last deployed identity range and the
range in use on the publication database.
While I don't recommend it, you can go to the subscriber and do a dbcc
checkident('TableName',reseed,101) and then run a sp_help TableName identify
the replication constraint which limits the identity values which can be
used in the subscriber, and modify it for the range you wish to use.
Again I don't recommend doing this.
You might want to check this link for more on this feature.
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
"Vivek Sharma" <vivek_nz76@.hotmail.com> wrote in message
news:e97gXp$3FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a merge replication going on. Say, I have a subscriber with an
> identity range from 1000 to 1200 and I have only used 100 identities of
that
> range. For some what reason I have to reinitialise the subscription. What
> publisher does is it allocates a new set of range to the table i.e. 1300 -
> 1400 to the article on subscription. As you can see I have simply wasted
100
> identities i.e. from 1100 to 1200. Please note identity allocation is
> happening automatically.
> Is there a way that I can avoid the wastage?
> How can I deduce using system stored procedure that what identities are in
> use for each article on each subscriber?
> Thanks
> Vivek
>
Subscribe to:
Posts (Atom)