.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

MERGE Replication between SQL Server 2005 to SQL Server 2008 R2

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server

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

View Complete Post

More Related Resource Links

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

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 2008 merge replication snapshot hangs on filtered articles


I have a publication on SQL Server 2008 Standard Edition using merge replication.  When I attempt to generate the initial snapshot, the snapshot agent appears to hang on the step "Setting up the publication for filtered articles."  I get a long (over 4 hour) series of messages: "The process is running and is waiting for a response from the server."  I know something is happening server-side, as SQL Server and the snapshot agent use a lot of memory and max out one core's processing capacity.

This has me confused as the publication is not doing any filtering.

Even more confusing:  I backed up the database and restored it onto my development-test system.  I created the snapshot there, and it took under 10 minutes every time.

Any suggestions for investigating and resolving this?

SQL Server 2008 Merge Replication article triggers not replicating


Currently running merge replication on SQL Server 2008 10.0.1600 and having issues with adding and modifying triggers on published articles.  I've searched through the MSDN site and googled it but have not found any definitive information on correct procecdures.

We have a replicated table that had 1 trigger on it when it was added to the publication.  That trigger was replicated.  We have since added a 2nd trigger to the table and it is not being replicated to our other server.  I've checked the Article Properties and Copy User Triggers is set to TRUE.  The only difference is the new trigger has NOT FOR REPLICATION in it, but I thought that would only keep it from being executed if a row is effected by the MERGE process.

We have rerun the snapshot and are syncing continuosly and the trigger does not replicate.  Do I have to drop the tablefrom the publication and then re-add it?  Am I missing something? 


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

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] 

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



George the DBA

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

SQL Server 2008 R2 Merge error in Replication agents checkup job


We have a customer with three publications, one distributor/publisher and on subscriber using merge replication.

looking at the history of the job replication agents checkup, every entry shows a message of:

Executed as user: NT AUTHORITY\SYSTEM. The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active. [SQLSTATE 01000] (Message 20554).  The step succeeded.


Throughout the day this error still appears as this job runs every 10 minutes but replication works fine. The issue is at some point throughout the night the replication stops with that same message. Sometimes it stops at 6:00:00am, sometimes at 6:40:00am, others at 6:50:00am. All we have to do to fix it is go into the replication monitor and stop synchronizing and then start synchronizing and replication will run all day until it stops next morning - again.

What is causing that error message? I have checked the other logs and there is nothing running at those times. in addition, no one has logged into the server that early (they don't start arriving until 7:15 or later).

What can i do to correct the issue so the message stops happening.


SQL Server 2008 / 2005 Databases Replication


Domain One - abc.com (IP Address scheme - 185.192.X.X)
Domain Two - xyz.com (IP Address scheme - 10.200.X.X)

There is a Forest level trust between those 2 domains.

Can you please help me if there is there any way that I can replicate the datas in Server to Server (which has both Ms Sql Server 2005 on it) - which will have same database in both servers. There is a chance to link those 2 servers in those 2 networks and store datas at the same time on each servers. So that if one goes down, staffs can log into another one without any down-time to access data from another server's database (which is the replica of the primary DB server). Please let me know & also if there is any good resource about that the same.


SQL Server 2008 Merge Replication - Snapshot File Location Is Incorrect


I've searched far and wide, and so far have not been able to find any reference to this issue.

There is a merge replication setup on a SQL 2008 server, setup to replicate to SQL 2008 Express edition. We have similar setups in SQL 2005 AND SQL 2008 without issues. The setup is that the current merge replication snapshot file location is \\<ServerName>\Repldata\UNC\<SERVER_PUBLICATION>\20101229112828 which represents the file location of the latest snapshot which was just generated due to an obsolete snapshot.

The issue comes in when attempting to replicate against the server. The replication says that the schema script could not be propogated to the subscriber. Under normal circumstances I would say it's due to security issues with being able to read to the above folder, however this is not the case. The additional error that comes up is stating that it could not read the file along with an OS error 3 (The system cannot find the path specified).

What is unique about this, is that the subscriber is trying to download the snapshot from an old and now non-existent snapshot folder. Here is an copy of the error, noting the file location of "20091121130613" which no longer exists and is from an old snapshot.


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,



Which types of replication for SQL Server 2008 R2 Replication To SQL Server 2005?


We have two SQL Servers, we plan to use replication to sync data from SQL 2008 R2 (one way) to SQL 2005

publisher and distributer : SQL2008R2
subscriber: SQL2005

which type of replication to apply on our case?

Snapshot, Transactional, Merge or Peer-to-Peer.


SQL server 2005 2008


i cant install sql server 2005 nor 2008 on my windows 7 os

i read about somthing like adding new account when installing sql 2008 to provision the server or so, but didnt know how,,,

can you plz help me??


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