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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SQL Server 2005 bidirectional replication problems

Posted By:      Posted Date: September 21, 2010    Points: 0   Category :Sql Server

I am having a system with two servers running SQL Server 2005 on Windows Server 2003.
Between the two SQL Server instances I am having a bidirectional replication of all my tables to always have the databases in sync.

When the replication is set up on the servers it normally runs fine for some time and then it stops working and I get errors like the following:

  Command attempted:
  if @@trancount > 0 rollback tran
  (Transaction sequence number: 0x000001B200005784000D00000000, Command ID: 1)
  Error messages:
  Violation of PRIMARY KEY constraint 'PK_MyTable'. Cannot insert duplicate key in object 'dbo.MyTable'. (Source: MSSQLServer, Error number: 2627)
  Get help: http://help/2627
  Violation of PRIMARY KEY constraint 'PK_MyTable'. Cannot insert duplicate key in object 'dbo.MyTable'. (Source: MSSQLServer, Error number: 2627)
  Get help: http://help/2627

And another error:

  Error messages:
  Agent 'SERV-A2-SYST-SERV-A1-10' is retrying after an error. 77 retries attempted. See agent job history in the Jobs folder for more details.

I do not know much about how the bidirectional replication works as I just set the replication up by some script

View Complete Post

More Related Resource Links

SQL Server 2005 RMO Merge Replication Problems


We have a Visual Studio 2005 Windows Forms Application with SQL Server 2005 on the servers (Windows 2003) and SQL Express 2005 on the client PCs (Windows XP) using Merge Replication (Pull Subscriptions).

We are planning to upgrade user PCs to Windows 7 and are performing tests, we have installed SQL Server Express 2005 SP3. But when we try to synchornize, the error message we get is

 "Could not load file or assembly 'Microsoft.SqlServer.Replication, Version=,Culture=neutral,' or one of its dependencies. An attempt was made
to load a program with an incorrect format."

What would be the best approach to fix this problem ? We are not planning on upgrading the servers now.

Is there a way to change "Microsoft.SQLServer.Replication" DLL version that we referrence in the Visual Studio 2005 project so it can work with both XP and Windows 7 users without causing any problems ?

Your help would be highly appreciated.

Thank you,



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

Replication between SQL Server 2005 and SQL Server 2008

Hi,    We were using SQL Server Transactional Replication between two SQL Server 2005 servers. Now we are going to upgrade the Publisher server to SQL Server 2008, Distributor Server remains as SQL Server 2005.     Can anyone let me know what is the challenges we need to face when configuring Replication between SQL Server 2005 and SQL Server 2008. Replication Method - Transactional Replication Distributor/Subscriber - SQL Server 2005 Publisher     -  SQL Server 208 Regards, Fazlu.

Using SQL Server Replication for SQL Server 2005 and 2008

I have an ASP.NET application that uses replication, and uses the RMO DLLs to do this. I initially built the application against the 2005 version of the DLLs and have it running successfully against SQL Server 2005. However, when I try running it against SQL Server 2008 it doesn't work. Initially I didn't deploy the Microsoft.SqlServer.* DLLs with my app - instead relying on them being in place on the server on which it was installed. However, when trying to make it work against SQL Server 2008 it was looking for the 2005 version DLLs which it wasn't able to locate. Following some other advice I read online I have now swapped out the 2005 DLLs and referenced the 2008 assemblies in my .NET project. In my MSI I now also deploy (i.e. redistribute) the SQL Server 2008 DLLs to the bin folder of my ASP.NET project. Now, when I try to run the application against SQL Server 2005 it fails to load the Microsoft.SqlServer.Replication assembly. I run depends.exe against this and find that it's dependent on SQLNCLI10.DLL which is missing on my machine with only SQL Server 2005 on it. So, what am I to do? Is this possible? How do I get around this?

SQL Server 2005 Installation problems

I have SQL Server 2005 Developer Edition 9.00 1399.06 running on Windows XP.  I have upgraded to a Windows 7 64-bit computer, and have attempted to install SQL Server on this machine.  The install procedure failed, probably because I was installing from a copy of the install disks on a USB drive rather than the original CDs.  (I am carrying out the upgrade in a different country to my office and I did not want to risk losing the original install CDs). The install failed, and I realised that I had to install from CDs.  I then uninstalled all the SQL Server programs.  However, the uninstall procedure told me the program was already uninstalled.  However, when I try to reinstall from CDs, I am told that SQL Server Database Services, Analysis Servics, Notification Services and Integration Services are already installed. Therefore I am at an impasse.  I cannot uninstall the programs and I cannot reinstall the programs.  I have tried a registry cleaner (CCleaner) to get rid of registry settings and removed all SQL Server folders, but to no avail. How do I progress without having to reformat my entire computer?  I cannot believe that an installation procedure can be so limited - why isn't a reinstall option offered even if the computer records the programs to be present? I look forward to some suggestions on how to fix this. &n

Problems installing SQL Server 2005 Integration Services for Server 2008 R2

I am trying to install Microsoft Forefont and i have got a tick on every requirement bar 1. I can't install SWL Server 2005 Integration Services Component. I am running windows Server 2008 R2. I have tried numerous steps such as going into to control panel and adding component but the Integration services is not appearing on the options. Is it actually possible to install 2005 integration services on server 2008 and if so how? Thanks

problems in sql server 2005 installation

Hello everybody   Ok the problem is that i have sql 2000 in a computer with windows 2000 professional sp4. i'm trying to install the sql server 2005 in other instance. When the installation begins i don't have any problem, but when it ends, y try to enter to the manage studio and is not in the start menu. Sql 2000 is ok but i can't work in 2005 i reinstall both versions but the problem persist. I delete all the files in the instalation but when i install it again the problem apperas again. how can i install both versions?

SQL Server 2005 Replication Alerts

Dear Friends,We had some problems related to SQL Server 2005 Transactional replication. The distribution agent wasn't working properly. I am relatively new to Replication, so please bear with me. I need to configure alerts. I decided upon the below 3 options (if any other option is there please let me know), Replication agent failure Replication Warning: Transactional replication latency (threshold expires) Replication Warning: Subscription expiration (threshold expires)To test the 1st option, I deleted a row in subscriber and updated that row in publisher. Since the row no longer exist the distribution agent failed. I even dropped the table in subscriber and tested. We received alerts. Now for the 2nd option the lateny was 30sec. So if a transaction happened in publisher it needs to be replicated to subscriber within 30 sec. If not Replication warning alert will be fired. I assumed that since the replication agent was failing in 1st option after deleting and updating the same row in subscriber, I thought that it will cause the transaction to be retained in the distributor for a long time and hence it will make the threshold to expire (2nd option) and hence the alert would fire. But it never happened. So my question is as follows:1. How to make an alert happen for option 2 and option 3 ?2. Was my assumption/intepretation correct wrt to option 1 forcing option 2 fire.- Deepak

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.


SQL server 2005 +asp.net 4.0 +html special characters problems saving special characters


Hello all

Could I please ask - how do I go about inserting into a database special characters such as the uppercase O, umlaut 


Please understand that I have been able to insert the special character from inside MMS by putting an N in front of the value

but when I run the  SQL file from inside an ASP.net 4.0 page it comes up as a diamond with a question mark in it

Does anyone have any idea how I would be able to acheive this?

SQL Server 2005 Schema Replication in Merge Replication


Dear Sir,

I am using SQL Server 2005 Developer Edition for Creating & Testing Database.

I had created Merge Publication WITHOUT FILTERING ROWS and Created Push or Pull Subscription. Now if I Add or Drop column using ALTER TABLE (T-SQL) in Table OR modify stored procedure using ALTER PROCEDURE in Published Database then while Synchronizing It replicate same in Subscriber Database and works very fine.

But when I had created Merge Publication WITH FILTERING ROWS (Parameterize or Static) and Created Push / Pull Subscription and I try to Add or Drop column using ALTER TABLE (T-SQL) in Table  OR modify stored procedure using ALTER PROCEDURE in  Published Database then while Synchronizing It DOESN'T REPLICATE in Table of Subscriber Database but tries Replicate in Stored Procedure and display error.

After Adding Column in Table of Published Database, If I Checked Article in property of Publication then it Adds column but it doesn't selects it. I also checked using sp_enumeratependingschemechenges it displays pending schema changes of stored procedure but

Merge Replication between SQL Server 2005 and SQL Server 2008



We are planning to implement Merge Replication between SQL Server 2008 "Not R2 Version" (Publisher) and SQL Server 2005 (Subscriber). Will this setup work, or there are some limitations ?

SQL Server 2005 Replication - Replicated Databse Performance Issue




I am struggling to work out a very frustrating replication issue and wondered if anyone could help me.

Here is my situation.


I have a virtual machine with 16gb RAM and 4 quad core 3Ghz processors and 250gb disk. This has Windows Server 2008 64 bit installed.

The machine has a SQL Server 2005 64 bit instance installed with SP3.

I have setup a publication on this SQL Server called pub1 , this publication publishes all tables/sp's /views and UDF's . I have set the article properties for the published tables as below.

- Copy Check Constraints = True

- Copy Clustered Index = True

- Copy Non Clustered Index = True

- Copy Default Value Spec = True

- Copy Collation = True

- Copy Insert, Update and Delete = True

- Copy unique key contraints = True

- Copy XML Indexes = True

Every other setting is set to false.

I have created a subscription to pub1 within the same SQL Server instance. The database has replicated everything I require without an issue.

If I run a query against the databse that I have published, it returns the query in less than 10 secs. If I run the same query against the subscription DB then this query takes over 30 mins to return.

I thought this was an issue with the indexes not replicating. From what I have selecte


Hi. I just upgraded my server from SQL Server 2005 Standard edition to SQL Server 2005 Enterprise edition (http://benchmarkitconsulting.com/colin-stasiuk/2009/06/03/sql-server-2005-edition-upgrade/). Then applied the service pack again. After the computer restart, when i opened the replication monitor to check then effect this upgrade had on my replications, i got below error when i open the Subscription Detail window. any suggeestions/workaround to correct this shall be very much appericiated. Thanks

TITLE: Replication Monitor

Replication Monitor could not open the Detail Window.


Specified cast is not valid. (ReplicationMonitor)



SQL Server 2008: Replication of IndexedView does not work correct anymore(did work in sql 2005)




I am encountering a problem with replication of an indexed view.

My current flow works fine on SQL Server 2005 (enterp.) But is does not work anymore on SQL Server 2008 (enterp.)



On my server(server1), i receive 2 tables via replication from another server(server0). (lets name them tTest1 and tTest2)

I create an indexed view based on those 2 tables. (lets name it idxvTest)

This indexed view(idxvTest) is then replicated to another server(server2), and materialized as a table (tTestidxv).

Any changes made to the tables on server0, are replicated via server1 to server2.


The above situation worked fine in SQL 2005.

However in SQL 2008, i don't get any error messages in the replication, and everything seems to work fine..

But as it appears, changes entered at server0 are being replicated to server1, also the view idxvTest looks fine, but the data is not updated/inserted on the table at server2(tTestidxv)!!

Note: initial setup of the replication from server1 to server2 works correctly, and the snapshot is also delivered. Only the updates after the snapshot don't come through.

SQL Server Mobile 2005 Merge replication failing over SSL, but can browse through pocket IE.


Hi All, this system is scheduled to go live in near to a day... but i have hit an issue when switching on SSL.  Any help would be greatly appreciated.


Device Env:

Windows Mobile 5.0 + Messaging and Security Feature Pack

SQL Server Mobile 2005.


Merge replication works fine without SSL.  After running the Configure Web Sync wizzard to setup SSL merge replication no longer works.  Note i checked using pocket IE by browsing to https://mycompany.com/pub/sqlcesa30.dll?diag and all worked fine.  There was no prompt saying the cert was bad etc.


The certificate is a:

Equifax secure global ebusiness CA-1 cert.

Signature algorithm md5RSA

public key RSA(1024 bits)

thumbprint algorithm sha1


Stacktrace given is:

-  ex {"A request to send data to the computer running IIS has failed. For more information, see HRESULT."} System.Data.SqlServerCe.SqlCeException
-  Errors {System.Data.SqlServerCe.SqlCeErrorCollection} System.Data.SqlServerCe.SqlCeErrorCollection
  Count 1 Integer

MERGE Replication between SQL Server 2005 to SQL Server 2008 R2


Hi, i have a publisher with 3 publications with below SQL

Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)   Nov 24 2008 13:01:59   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

I want to add a subscriber to the server with below SQL

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (VM)

Now when i am trying to add subscritpion to the 2008 R2 server on the publication server, i am getting below message

TITLE: New Subscription Wizard

For merge publications, the version of the Subscriber must not exceed the version of the Publisher.



Is merge replication not possible between the 2?? Please advice. I beliwve Microsoft's new releases should support its older versions...

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