Back in Sept 2007 I blogged about 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:
[548] 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
begin
— 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
begin
if IDENT_CURRENT(‘[dbo].[TableName]’) = @range_end
begin
DBCC CHECKIDENT (‘[dbo].[TableName]’, RESEED, @next_range_begin) with no_infomsgs
end
else if IDENT_CURRENT(‘[dbo].[TableName]’) >= @next_range_end
begin
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
goto FAILURE
end
end
end
…
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.
What the savvy DBA can do:
The template for all of the MSmerge_ins_GUID triggers are on the model database, these triggers are created by the system stored procedure: sp_MSaddmergetriggers_internal
If you change the code on this system stored procedure any new database creation will have the proper template for creating the merge insertion triggers. For the existing databases, you might like to modify the MSmerge_ins_GUID trigger manually, for now.
At the time I’m writing this post I’m not aware if this issue is addressed on any cumulative update. There is a similar case on the Cumulative Update released on January 15, 2008
http://support.microsoft.com/kb/941989
Happy coding guys!
I’m sometimes amazed how personal people takes the bug issue. If you look at users you can sometimes think that the users think we have a complete bug list or that we have super powers to figure out what the problem are. And believing that a rotten attitude gets the problem fixed faster is so stupid.
But if you look at it from the other perspective, I often find developers taking pride in classifying something as a bug. In these days of agile and customer driven development, why taking so much pride into saying if something is a bug or a change request.
During the past two years we’ve gotten two synchronization bugs fixed in SQL Server 2005. My tips: be honest, give all information you have, understand that everyone wants to fix the bugs and don’t forget that the guys fixing and confirming the stuff are people. Often really nice people. And also remember that reporting a bug is like going to the ER: sometimes there are people who are sicker than you and they need help first.