asked on

IDENTITYKEY

Am getting the below error message when trying to do a bulk insert from the Publisher to Subscriber in SQL 2005. When I set up replication @identityrangemanagementoption is set to auto
Msg 548, Level 16, State 2, Line 603
The insert failed. It conflicted with an identity range check constraint in database 'MTTTT, replicated table 'dbo.tb_CCMe', column 'ccmb_SID'. 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 statement has been terminated.

Daniel Reynolds

When I have set up replication in the past, a range needs to be selected for the identity fields of each database. I wonder if the count of your records has exceeded your ranges, and it would now require the ranges to be adjusted.

Richardsoet

ASKER

Am using the below script but getting error

sp_adjustpublisheridentityrange 'MotivanoUKLiveWebCore',
'tb_CCMemberBenefit'

Please rewrite it for me my table name is 'tb_CCMemberBenefit'
my publication name is MotivanoUKLiveWebCore
Msg 21314, Level 16, State 1, Procedure sp_adjustpublisheridentityrange, Line 27
There must be one and only one of '@publication' and '@table_name' that is not NULL.

Richardsoet

ASKER

Please how can i adjust ranges please please write the procedure to follow please

Daniel Reynolds

Note the following in the useage deescription of the stored procedure to adjust your ranges.: The last sentence is very important. (Only supply the publication or table name, NOT BOTH) You appear to be supplying both. Give it a whirl with only one.

sp_adjustpublisheridentityrange is used in all types of replication.

For a publication which has the auto identity range enabled, the Distribution Agent or Merge Agent is responsible for automatically adjusting the identity range in a publication based on its threshold value. However, if for some reason the Distribution Agent or Merge Agent has not been run for a period of time, and identity range resource have been consumed heavily to the point of threshold, users can call sp_adjustpublisheridentityrange to allocate a new range of values for a Publisher.

When executing sp_adjustpublisheridentityrange, either publication or table_name must be specified.
If both or neither are specified an error is returned.

Richardsoet

ASKER

My problem is down to a table tb_CCMember which I was trying to do Bulk insert of 30000 records,
How do I resolve this problem

Daniel Reynolds

Here are a couple links as well that might prove useful
http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/
http://thedotnet.com/nntp/397582/showpost.aspx

Daniel Reynolds

Have you tried
sp_adjustpublisheridentityrange @table_name='tb_CCMember'

Richardsoet

ASKER

How do I check the identiy range to know am running out range on a table used for merge replication 2005

Richardsoet

ASKER

is there any performance issue on replication when increase the identity range???

ASKER CERTIFIED SOLUTION

Daniel Reynolds

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Richardsoet

ASKER

does it affect the size of the table in any how if the identity is increased to maximum

Daniel Reynolds

No, the data size of the column remains the same.

Remember though, that as you add more records your table size will grow. But the column that holds the identity remains the same.