Automatic Identity management

Automatic Identity management

Post by Mark Vincen » Sat, 28 Jul 2001 07:51:37



I am having a problem using the SQL Server 2000 automated
identity value management facility. I have 2 SQL Server
2000 dbs and using merge replication. For articles with
tables having identity I use automatic allocation with 100
value blocks and an 80 threshold (the default values). The
problem is that on the publisher, the next block of values
is never allocated. Even when the threshold is crossed and
AFTER the merge process runs and replicates the new rows,
the next value does not jump to the next block and all the
remaining values are used. Then of course, this happens:

The identity range managed by replication is full and must
be updated by a replication agent. The INSERT conflict
occurred in database <table names etc. deleted> .
Sp_adjustpublisheridentityrange can be called to get a new
identity range.

Calling the stored procedure fixes the problem but this
hardly makes it automated. Also, this is NOT a problem on
the subscriber. When it allocates identity values past the
threshold, it moves to the next block after the merge runs.

I have installed SP1 on both of these servers.

Has anyone else experienced this? Does anyone have a
solution?

Thanks in advance...

-- Mark

 
 
 

Automatic Identity management

Post by TSVK » Sun, 29 Jul 2001 02:30:06


Weird.  When merge is run do you see the procs to adjust identity being
called (with profiler?)

Quote:> I am having a problem using the SQL Server 2000 automated
> identity value management facility. I have 2 SQL Server
> 2000 dbs and using merge replication. For articles with
> tables having identity I use automatic allocation with 100
> value blocks and an 80 threshold (the default values). The
> problem is that on the publisher, the next block of values
> is never allocated. Even when the threshold is crossed and
> AFTER the merge process runs and replicates the new rows,
> the next value does not jump to the next block and all the
> remaining values are used. Then of course, this happens:

> The identity range managed by replication is full and must
> be updated by a replication agent. The INSERT conflict
> occurred in database <table names etc. deleted> .
> Sp_adjustpublisheridentityrange can be called to get a new
> identity range.

> Calling the stored procedure fixes the problem but this
> hardly makes it automated. Also, this is NOT a problem on
> the subscriber. When it allocates identity values past the
> threshold, it moves to the next block after the merge runs.

> I have installed SP1 on both of these servers.

> Has anyone else experienced this? Does anyone have a
> solution?

> Thanks in advance...

> -- Mark


 
 
 

Automatic Identity management

Post by Mark Vincen » Sun, 29 Jul 2001 04:57:41


Actually I do not. The merge agent on the publisher is
running sp_MScheckidentityrange for all tables with
identity management turned on but only on the subscriber.
That procedure never runs on the publisher.

>-----Original Message-----
>Weird.  When merge is run do you see the procs to adjust
identity being
>called (with profiler?)


>> I am having a problem using the SQL Server 2000
automated
>> identity value management facility. I have 2 SQL Server
>> 2000 dbs and using merge replication. For articles with
>> tables having identity I use automatic allocation with
100
>> value blocks and an 80 threshold (the default values).
The
>> problem is that on the publisher, the next block of
values
>> is never allocated. Even when the threshold is crossed
and
>> AFTER the merge process runs and replicates the new
rows,
>> the next value does not jump to the next block and all
the
>> remaining values are used. Then of course, this happens:

>> The identity range managed by replication is full and
must
>> be updated by a replication agent. The INSERT conflict
>> occurred in database <table names etc. deleted> .
>> Sp_adjustpublisheridentityrange can be called to get a
new
>> identity range.

>> Calling the stored procedure fixes the problem but this
>> hardly makes it automated. Also, this is NOT a problem
on
>> the subscriber. When it allocates identity values past
the
>> threshold, it moves to the next block after the merge
runs.

>> I have installed SP1 on both of these servers.

>> Has anyone else experienced this? Does anyone have a
>> solution?

>> Thanks in advance...

>> -- Mark

>.

 
 
 

Automatic Identity management

Post by Mark Vincen » Wed, 01 Aug 2001 06:59:07


A quick update to the status of this problem:

Using SQL Profiler I could see that the check procedure
for identity ranges was being called on the subscriber
but not on the publisher. The publisher would run out of
identity values and the users could no longer enter data
where an identity would be needed. My workaround is to
create a job that runs every minute and calls
sp_adjustpublisheridentityrange. This procedure will do
nothing if no re-allocation of identity is needed and will
assign the next block of values to the publisher if the
threshold has been crossed. This is what should happen
when the merge agent runs anyway.

This appears to resolve the issue but the bug still
exists.

Another bug I noticed during testing is the generate SQL
script for merge replication does not work properly. I set
up the merge agent to run continuously yet the script
generated a creation of this job that runs once every hour.
I had to manually modify the job and select "Run
whenever SQL Server Agent runs" to correct the problem.

>-----Original Message-----
>Actually I do not. The merge agent on the publisher is
>running sp_MScheckidentityrange for all tables with
>identity management turned on but only on the subscriber.
>That procedure never runs on the publisher.

>>-----Original Message-----
>>Weird.  When merge is run do you see the procs to adjust
>identity being
>>called (with profiler?)


>>> I am having a problem using the SQL Server 2000
>automated
>>> identity value management facility. I have 2 SQL Server
>>> 2000 dbs and using merge replication. For articles with
>>> tables having identity I use automatic allocation with
>100
>>> value blocks and an 80 threshold (the default values).
>The
>>> problem is that on the publisher, the next block of
>values
>>> is never allocated. Even when the threshold is crossed
>and
>>> AFTER the merge process runs and replicates the new
>rows,
>>> the next value does not jump to the next block and all
>the
>>> remaining values are used. Then of course, this
happens:

>>> The identity range managed by replication is full and
>must
>>> be updated by a replication agent. The INSERT conflict
>>> occurred in database <table names etc. deleted> .
>>> Sp_adjustpublisheridentityrange can be called to get a
>new
>>> identity range.

>>> Calling the stored procedure fixes the problem but this
>>> hardly makes it automated. Also, this is NOT a problem
>on
>>> the subscriber. When it allocates identity values past
>the
>>> threshold, it moves to the next block after the merge
>runs.

>>> I have installed SP1 on both of these servers.

>>> Has anyone else experienced this? Does anyone have a
>>> solution?

>>> Thanks in advance...

>>> -- Mark

>>.

>.

 
 
 

1. Automatic Identity Range Management

From sql 2k bol, under managing identity values.

You enable automatic identity range handling:

In SQL Server Enterprise Manager, in the Publication Properties dialog box.

I can't find this in EM.  I have set up transactional replication with
immediate updating subscribers and my replicated db has identity values.

Does anyone know where I can find it?

thanks

corey

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----==  Over 80,000 Newsgroups - 16 Different Servers! =-----

2. Log file - how to turn off?

3. Oracle9i Automatic Space Management - a "feature"?

4. :::Help with search and "find next":::

5. Automatic Segment Space Management

6. views and stored procedures - A CRY FOR HELP!

7. Automatic Identity Range Handling problem

8. Why we need to go for SQL 2000 from SQL 7.0

9. Automatic Identity Range Handling

10. Identity field increment range not automatic?

11. SQL Server CE - Automatic Managed Identity Ranges Supported?

12. Automatic Identity Range Handling