Thursday, February 14, 2008

Identity Range not working for Merge Replication in SQL Server 2005

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
-- 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.

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

Happy coding guys!

Labels: ,


Blogger Paul said...

Hi Lizet,

After long searching the issues i came across your post. I have the same issue. I assume you raised a bug report at microsoft. Do you have any info on the issue, perhaps a link to a bug report so i can keep informed on the status

Many Thanks

7:41 AM  
Blogger Lizet Pena de Sola said...

Hi Paul,
Not that I'm glad that you have the problem, but I'm glad it is affecting someone else.
Did you have the same issue with SQL Server 2000?
I haven't been able to open a bug report on this, we already filled up the quote with some other issues. I'm opening a connection case though. Those cases are treated as very low priority though. We figured we would check the range on each table on a nightly job in the mean time. Again, some brute force applied to something that should be working...
I'll post the connection case number soon.

8:08 AM  
Blogger Lizet Pena de Sola said...

This is the bug id on the Microsoft Connect program for SQL Server 2005:

8:22 AM  
Blogger Lizet Pena de Sola said...

Vote for this bug resolution!

12:39 PM  
Blogger Paul Verhallen said...

Hi Lizet,

We have filed a official bug report at microsoft.

We got an reply that they are looking into it asap.

Ill keep you posted if i know more

4:40 AM  
Blogger Lizet Pena de Sola said...

Yes, please keep me posted. This will safe a great deal of work/maintenance on the project I'm working on now. Thanks a lot!

7:34 AM  
Blogger Karen said...

Bleah! I chimed in on the Connect bug as well. You'd have to run your applications as db_owner avoid this -- what was Microsoft thinking?

12:05 PM  

Post a Comment

<< Home