Identity Range not working for Merge Replication in SQL Server 2005
as Won't Fix
2/28/2008 7:22:11 AM
User(s) can reproduce this bug
the problem we were having with the identity range in merge replication:@@identity not working after SQL Server 2005 upgrade The problem continued until today, this post is to explain what we figured out.
The error message that describes this problem reads as follows:
 The insert failed. It conflicted with an identity range check constraint in database 'DatabaseName', replicated table 'dbo.TableName', column 'ColumnNameId'. 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.
The identity range adjustment happens after every insert in the given article. The code responsible for the identity check adjustment is on the system trigger for the published article:
MSmerge_isn_GUID where GUID is the GUID for the given article.
if is_member('db_owner') = 1
-- select the range values from the MSmerge_identity_range table
-- this can be hardcoded if performance is a problem
declare @range_begin numeric(38,0)
declare @range_end numeric(38,0)
declare @next_range_begin numeric(38,0)
declare @next_range_end numeric(38,0)
select @range_begin = range_begin,
@range_end = range_end,
@next_range_begin = next_range_begin,
@next_range_end = next_range_end
from dbo.MSmerge_identity_range where artid='BAEF9398-B1B1-4A68-90A4-602E3383F74A' and subid='0F9826DB-50FB-4F73-844D-AE3A111B4E1C' and is_pub_range=0
if @range_begin is not null and @range_end is not NULL and @next_range_begin is not null and @next_range_end is not NULL
if IDENT_CURRENT('[dbo].[TableName]') = @range_end
DBCC CHECKIDENT ('[dbo].[TableName]', RESEED, @next_range_begin) with no_infomsgs
else if IDENT_CURRENT('[dbo].[TableName]') >= @next_range_end
exec sys.sp_MSrefresh_publisher_idrange '[dbo].[TableName]', '0F9826DB-50FB-4F73-844D-AE3A111B4E1C', 'BAEF9398-B1B1-4A68-90A4-602E3383F74A', 2, 1
if @@error<>0 or @retcode<>0
As you might have noticed already, if the insertion is made by a user that is not member of the db_owner database role, the identity adjustment won't happen. I believe this is a bug, not a feature. It forces the users that are allowed to do insertions to be owners of the database, or they will have the run out of identities error quite often and a manual adjustment will be required.