.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Shivakumaran
Pankaj Mishra
alex morco
Imran Ghani
Meenakshi
Post New Web Links

Merge Replication - Publisher Identity Range Assignment with Disconnected Subscriber

Posted By:      Posted Date: November 20, 2010    Points: 0   Category :Sql Server
 

Hello everyone,

I have a merge replication topology setup as follows...  Publisher - SQL 2008 SP1, Distributor - SQL 2008 R2 (RTM), Subscriber - SQL 2008 SP1.

I recently had my subscriber disconnected for a couple days.  During this time, I was getting the following error on my publisher:

The insert failed. It conflicted with an identity range check constraint in database 'production', replicated table 'dbo.CustomerOrder', column 'CustomerOrderId'. 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.

I understand why I would normally get this error, but if the publisher and distributor are both online, why didn't the publisher attain a new range from the distributor after it hit its threshold?  Is this expected behavior whenever a subscriber is disconnected?

Thanks in advance.

Jarret




View Complete Post


More Related Resource Links

Identity Range Value (Max Limit Finished) SQL Server 2005 (Merge Replication Problme)

  

Hi ,

I am using SQL Server 2005 enterprise edition, I Confirured the Merge Replication to my database). I have a replication of 21 database from various locations.

Problem is: When is trying to remove one subriction from the publisher and add a fresh subriciton to it. it is poping any error saying the Identity Range value exceed its Max Limit try to change from int to bigint. is there any solution for it not changing from int to bigint.

Thank you.

krishna


Merge Replication Atomicity - at what point does ALL subscriber data become visible at the publisher

  

Hello 

I have a Merge Replication installation going on with about 35 connected devices. When one of the subscribers synchronizes, do the inserted rows appear sporadically on the publication database at different times? Or do they appear all at once?

Can all of these be isolated inside a transaction - this would seem like it should be the default behaviour, in case of a sync being cancelled or failing due to loss of connectivity etc. 

The reason for this question is that another part of the system seems to have queried the publication database during and after a subscriber synchronization with differing results. 

If this is normal, what would be the best solution to ensure data integrity? 

Thank you in advance. 

James


@@IDENTITY issues created in MS Access after a Merge Replication

  
Hi all, We have 2 SQL Servers that update each other using Merge Replication. We then connect to the tables via an ODBC link within Access and this worked without any problems until we upgraded our SQL Server from 2000 to 2008 R2. Since the upgrade we are having problems within Access. When we come to add a new record, the record returned is different to the one we added. After much research we discovered its down to the triggers within SQL Server (from versions 2005 onwards) where the trigger updates the global variable @@IDENTITY. We've also found out that MS Access uses this variable to return the record that was supposedly last added but since it gets altered as part of a merge trigger it makes Access problematic. With this being a very big issue with MS Access and SQL Server Merge Replication triggers I can assume that others would have managed to work around this and come up with a solution. can anyone help?

SQL SErver 2008 Merge Replication: Alter Trigger cause syntax error on subscriber site

  

I have 2 clustered instances running on SQL Server 2008 SE-64 patch level 10.0.2531.0. These is one DB on these 2 instances (compatibility_level=80)under merge replication. now I need to change one trigger to add "NOT FOR REPLICATION". One publisher site all is ok but on subscriber site it causes Error 102 Severity 15 State 1 Incorrect Syntax near 'dbo'.

After tracing the error in profiler, I captured the incorrect syntax as below:

exec('ALTER TRIGGER [dbo].[trgBusinessEntityAllocationUpdate] on [dbo].[BusinessEntityAllocation] 
 [dbo].[BusinessEntityAllocation] 
FOR 
 UPDATE
 NOT FOR REPLICATION
AS
 
SET NOCOUNT ON
BEGIN
......
END
')

obviously, there is an duplicated part of object name. but the script was generated by replication engine. How could it happened? can anyone help?

Thanks

George 


George the DBA

After Merge Replication Identity column was not generated Properly

  

HI All,

 

    I has been implemented Merge replication in sql server 2008, in that some tables Id locums are add 2000 and 20000 no add automatically incremented, why it was happens how to resolved this issues? if i want to get continues number what will it do, please help me in this ..... this is very urgent for me.

     

Prasad

 


sql 2005 1 publisher and 2 subscribers for merge replication

  

Hi,

We have 3 servers using sql 2005 having merge replication setup. If we use 1 publisher and 2 subscribers approach, can this replication topology handle single point of failure(i.e. when publisher server is down or not available, can the remaining 2 subscribers synchronize with each others?). If this approach cannot handle single point of failure, is there any other replication setup that can handle single point of failure and able to resolve conflicts for 3 servers using sql 2005?

Thanks and regards,

Wallace

 


merge replication system tables fragmentation issue

  
I have Merge replication going on in my environment. The problem is that system tables like MSmerge_content, MS merge _tombstone and MSmerge_genhistory are getting fragmented. We are rebuilding indexes time to time daily. But we want some good fix. Just send some good idea guys?

Steps by Step document for configuration Merge Replication in 2005

  
Hi , Can anyone provide link to configure Merge Replication in 2005 with print screen.Also let me know the difference between Transcation level and Merge Replication. regards Vijay  

SQL Server 2005 Merge Replication

  
We did the following: We setup a database with all of standard tables, views, etc. We then put in place merge replication for a few of the tables within this database, including a tables called "Areas". We can execute SQL statements to insert rows into this table fine. We then applied an update script for this database to change a few tables by adding fields and changing indexes, etc.  We didn't change the "Areas" table though. After the update to the database structure, we get an error when trying to insert into this "Areas" table. The error message was the following: The insert failed. It conflicted with an identity range check constraint in database 'AETest', replicated table 'dbo.Areas', column 'AREPrimaryId'. 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.   What would cause such a problem and how do we resolve it?  The structure of the "Areas" table before and after the update is the following: USE   [AETest] GO /****** Object: Table [dbo].[Areas] Script Date: 08/24/2010 17:42:36 ******/ SET   ANSI_NULLS ON GO SET   QUOTED_IDENTIFIER ON GO SET   ANSI_PADDING ON

Merge Replication: How to give read access on snapshot share to a sql account

  
Hello All, I want to give read access to an sql account(not windows account). Can it be given or not. Someone please tell. Thanks saandii777

How to prevent Propagation from subscriber to Publisher in some occations

  
We are using merge application. We need sometimes delete some tables at subscriber and that delete command or any other command  should not propagate to Publisher and other subscribers. And sometimes it should be refilled from Publisher. Is it possibe? Second question : My publisher crashed two times. I could attach the database after restoring the OS. But at subscriber I am unable to modify some table and it gives the error invalid object name 'dbo.msmerge.......'. Replication is working fine. What is the reason for this error and how I can get rid of it?

Replication and auto Identity Increment

  
We want to have two SQL server working on the same data at the same time (SQL Server 2008). I've been told that the auto ID's will stop working (the identity option). I'm not sure how true this is or what is the best replication to use for this.   Thanks

fragmentation in merge replication

  
I have Merge replication going on in my environment. The problem is that system tables like MSmerge_content, MS merge _tombstone and MSmerge_genhistory are getting fragmented. We are rebuilding indexes time to time daily. But we want some good fix. Just send some good idea guys?

Merge Replication: Fails using Replication.SaveProperties when changing Subscription Info

  
I'm trying to use SQL Server/Compact Framework 3 Merge Replication functionality and am running into a problem when calling Replication.SaveProperties (using c#). I was hoping to get some clarification on whether its my interpretation on how things should work vs a coding problem. Basically, the Merge Replication seems to work as I can sync information between my handheld and server databases.  However, I've created a form on my handheld so that the user can change some synchronication settings (e.g. Publication or Publisher Name) and then save it.  What I've noticed is : 1) You can NOT call Replication.SaveProperties until after a synchronization is performed (otherwise you get an error message).  Assuming this is so that it can create the "__sysMergeSubscriptions" table. Does this sound right? 2) If I then try to change the saved Subscriber information via another call to Replication.SaveProperties (e.g. change the Publication info), I then get an error message that says: The subscription was not found. Publisher, PublisherDatabase, or Publication  could be incorrect. Is this normal behavior?  Does this mean that you can you NOT change subscriber information; only add or drop it? Thanks,  Ed

configuring merge replication

  
Hi ALL,              I am configuring merge replication... For example Publisher A sql server 2008 ( databse adventureworks) subscriber B sql server 2000 ( database adventureworks) I already have database created at subscriber....both databases at publisher and subscriber are identical in structure but changes in data( at subscriber there some inserts and updates) now before i create new snapshot and applied to subscriber i want to upload changes which are at subscriber... how can i apply changes to publisher database before i run merge agent... TIA

SQL Server 2005 as Distributor, Subscriber - SQL Server 2008 as Publisher

  
Hi,           I am creating a Distributor on SQL Server 2005 the server name is Server1 and Server1 is the Subscriber as well, we are using Push Subscription           Publisher is on SQL Server 2008, Server2. When i try to create a Publications, it throws the following error.          Server "Server1" is an Older Version of SQL Server and cannot be the Distributor for Server  'Server2'  Server1 - Distributor, Subscriber  Server2 - Publisher Is there any way to overcome this issue..? Regards, Fazlu.

Merge Replication, Push Subscription : The snapshot takes centuries to apply

  
Well, not centuries, except that the users are storming the gate. I'm trying to find how to get the snapshot moved to the subscriber and applied in a reasonable time. Last time was successful, but took 2 1/2 days to build the subscriber database from the snapshot. Hillary responded: Something is very wrong here. You should be able to generate your snapshot, copy it manually over to the subscriber - using the altsnapshotfolder parameter and then apply it there. So now I have my snapshot, a folder with lots of .cft .bcp .dri .prc .sch  and .trg files. Getting this to the subscriber computer shouldn't take long. Once I get it there, how do I use it to get the subscriber set up?  You can't be successful at this unless you're at least 1/2 a bubble off level.
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend