Solved

IDENTITYKEY

Posted on 2007-12-06
14
1,872 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:Richardsoet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
14 Comments
 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 20421689
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.
0
 

Author Comment

by:Richardsoet
ID: 20421782
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.
0
 

Author Comment

by:Richardsoet
ID: 20422217
Please how can i adjust ranges please please write the procedure to follow please
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 20422694
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.
0
 

Author Comment

by:Richardsoet
ID: 20422743
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
0
 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 20422776
0
 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 20422793
Have you tried
sp_adjustpublisheridentityrange  @table_name='tb_CCMember'
0
 

Author Comment

by:Richardsoet
ID: 20426435
How do I check the  identiy range to know am running out range on a table used for merge replication 2005
0
 

Author Comment

by:Richardsoet
ID: 20428362
is there any performance issue on replication when increase the  identity range???
0
 
LVL 17

Accepted Solution

by:
Daniel Reynolds earned 500 total points
ID: 20428865
The performance should not be affected at all.
Basically it just allows for each machine to save with a unique key (within the range).
Then when the merge is done, there are not any clashes between keys.

To view your key ranges, Open up MS SQL Server Mgmt Studio...Click the Replication Tree Node...Click the Local Publications Tree Node. Right Click the Publication you are interested in and select properties. In the next dialogue select Articles.. You can then select to view the specific table properties.
You should be able to look at your published articles and view the properties of each table to view the current identity range.
0
 

Author Comment

by:Richardsoet
ID: 20428980
does it affect the size of the table in any how if the identity is increased to maximum
0
 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 20429176
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.
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question