"Earl" <brikshoe@newsgroups.nospam> wrote in message
news:ev8q4pUZFHA.1384@TK2MSFTNGP09.phx.gbl...
> Man, I am seriously losing faith in this entire Microsoft replication
> scheme. It seems that every time I turn around, it's another damn weirdo
> wacko issue -- that cannot be easily resolved. The documentation on how
> replication works looks like it makes sense, but in the end, it's a $@#*
> joke -- nothing REALLY works as advertised and if you get into any sort of
> problem, you feel like a band member on the Titanic.
>
> Now that I've got the rant out of the way ...
>
> Very basic, very simple, I just want to use an identity range that doesn't
> give me that moronic error message (love that "Msg 548, Level 16, State 2,
> Line 1" nonsense too).
>
> I went back into my publication, and reset the identity range to
1,000,000.
> I'm trying to insert 1,000 records for chrissakes. I'm seeded at 1, with
an
> increment of 1 (since I've deleted the existing records, it shows the next
> identity as being 1430). Still get the "identity range is full" message.
>
> Tried to run sp_adjustpublisheridentityrange and the sp runs with no
> messages, but I still cannot insert 1000 records.
>
> Totally stumped here.
>
>

"Hilary Cotter" <hilary.cotter@gmail.com> wrote in message
news:ejfdqzXZFHA.4008@tk2msftngp13.phx.gbl...
> Its your check constraint on your table.
>
> Basically what happens is that automatic identity range management is
> accomplished by two mechanisms, the first is the identity seed and
> increment. DBCC checkident('tablename',RESEED,value) allows you to set
> this,
> although you should use the proc sp_adjustpublisheridentityrange to set
> this. If it fails you might want to set it yourself.
>
> The second part missed by most is the check constraint. Issue a sp_help
> tablename in your publication database and you will see a check constraint
> that is named something like this.
>
> repl_identity_range_pub_84EB4FA5_28B9_4AD2_82CC_27B1CE554D0D
>
> This check constraint will look like this
>
> ([job_id] > 300 and [job_id] < 400)
>
> Ideally you will have picked a publisher or subscriber range where
> threshold * publisher_range > maximum number of inserts per merge agent
> run
> or
> threshold * subscriber_range > maximum number of inserts per merge agent
> run.
>
>
> Key to understanding what has happened in your case is realizing that if
> your check constraint kicks in your identity value is still incremented.
>
> So consider a check constraint of >1 and <10 and an identity seed of 0 and
> an increment of 1
>
> the first two inserts are kicked back by the check constraint, but the
> identity value is incremented.
> the next 9 are inserted, and the identity values are incremented.
> The next 1 is kicked back by the check constraints, and the identity value
> is incremented again - it is now 10.
>
> Subsequent inserts are all kicked back by the check constraint and the
> identity value is continually incremented. If your identity range is not
> adjusted this will be a continual vicious cycle.
>
> So to fix your problem adjust the check constraint (perhaps to your
> range),
> run the dbcc checkident with the reseed to reset it, and then make sure
> that
> the next value in the distributor.dbo.MSrepl_identity_range is higher than
> the range you pick.
>
> --
> 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
> "Earl" <brikshoe@newsgroups.nospam> wrote in message
> news:ev8q4pUZFHA.1384@TK2MSFTNGP09.phx.gbl...
>> Man, I am seriously losing faith in this entire Microsoft replication
>> scheme. It seems that every time I turn around, it's another damn weirdo
>> wacko issue -- that cannot be easily resolved. The documentation on how
>> replication works looks like it makes sense, but in the end, it's a $@#*
>> joke -- nothing REALLY works as advertised and if you get into any sort
>> of
>> problem, you feel like a band member on the Titanic.
>>
>> Now that I've got the rant out of the way ...
>>
>> Very basic, very simple, I just want to use an identity range that
>> doesn't
>> give me that moronic error message (love that "Msg 548, Level 16, State
>> 2,
>> Line 1" nonsense too).
>>
>> I went back into my publication, and reset the identity range to
> 1,000,000.
>> I'm trying to insert 1,000 records for chrissakes. I'm seeded at 1, with
> an
>> increment of 1 (since I've deleted the existing records, it shows the
>> next
>> identity as being 1430). Still get the "identity range is full" message.
>>
>> Tried to run sp_adjustpublisheridentityrange and the sp runs with no
>> messages, but I still cannot insert 1000 records.
>>
>> Totally stumped here.
>>
>>
>
>