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

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

Replication on SQL 2005

Posted By:      Posted Date: September 09, 2010    Points: 0   Category :Sql Server
I am trying to configure replication on an sql server 2005. It gives me an error message that I should enable the server to be a distributor first. The wizard doesn't go forward. It ends there and I can't continue

View Complete Post

More Related Resource Links

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

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.

Issue with transactional replication on SQL 2005 SP3 using row filters with bit columns

We recently upgraded from SQL 2000 to SQL 2005 SP3 and I am noticing something that I think is a bug with replication from a publication that has an article in it with row filters.  It seems to be that if the row filter has columns that are bit datatype and that column is updated on the publisher to cause the row filter to exclude it, it is not removed from the subscriber.  If you update a column that is part of the row filter that is not a bit datatype it works as expected.  I can reproduce this in a test environment with a small base table.  In production this is resulting in an occasional 20598 error "The row was not found at the Subscriber when applying the replicated command". Is anyone aware of a hotfix that addresses this? I can think of a few work arounds but the table is rather large and I really would like to avoid having to pull replication, re-snap or changing datatypes on the base table etc. Any suggestions are greatly appreciated.  Thanks!

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?

Replication option SQL 2005 Standard Edition

Hi, We are using SQL 2005 Standard Edition.  We would like to use Replication for one of our database. I would like to know answers for the questions. 1. Can we use Replication features with SQL 2005 Standard Edition 2. Can we use DR site with replication for the reporting purpose?  Is there any risks associated with option? 3. Do we still need to take transaction log backup on the production server if we use replication.   Thanks.      

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

SQL 2005 Standard - problem with ORACLE replication


Hello All

Windows 2003 SP2  x64 + SQL 2005 Standard 32bit + Oracle ODBC Driver 10.2.01

Replication between SQL servers works FINE, bit I'm not able to replicate betwen MS SQL and ORACLE server.

Any ideas ?


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 bidirectional replication problems


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

SQL 2005 Transactional Replication without Primary Key...


Hi All,

I am newbie to SQL 2005 and doing my first project  (I am an exchange/AD Consultant). I am trying to do a transactional replcation across a VPN link. I have humdreds and hundreds of tables to be replicated. I have around 100 tables which are without any primary key. I am using http://blogs.techrepublic.com.com/howdoi/?p=123 for configuration.

I am able to replicate the tables which are with Primary keys, can't replicate any tables which are without primary keys. I am sure there must be a work around or some solution for the tables without primary keys. 

Please help.




Can't replicate few tables in Snapshot replication SQL 2005 in two different domain


I am not able to replicate few tables of a database using snapshot replication.Both publisher and subscriber are in different network and domain.Although I am able to replicate 950 tables but facing problem in some 30 tables.I am using push replication and I can telnet 1433 port from A server to B server but vice versa not happening.When I am replicating few records of that few tables it is replicating properly but not able to replicate complete table. I tried Verbose history too but I didn't get complete error.

Please help me out.....

How to view merge replication error (sql 2005)


We're using 2005 merge replication and we found that some records deletion cannot synchronize from publisher table to other subscriber table. (This table have very small size(<1000 records). We want to view those replication system tables or any logs for diagnostics this serious problem. Can you suggest any replication system table or logs that can indicate the reasons or hints of records deletion outsync?

Thanks and regards,

2005 Merge Replication Conflicts



We have a republishing merge replication setup of SQL Server 2005 Head Office (HO) publishing to SQL Server 2005 Regional Offices (RO) re-publishing to SQL Server 2005 Express Stores (ST). The problem we are having is that of strange conflicts. They can be grouped into the following types:

1) Update conflicts where the data is the same on both sides and one side hasn't made a change since the last replication event.

2) Delete/Update conflict where the delete is real but the update isn't. Like #1 the update never occured.

3) Delete/Delete confluct where one of the deletes are real but the other isn't which then create a Unique identifer error in the conflict resolver.

We have looked over the SQL Server CU's both nothing describes our problem as we are running SP3. We've also search for other reported problems and can confirm that we don't have filters on DateTime fields only GUID fields and the join filter table hierarchy isn't deep. We've also tried a re-initialisation and verified that the collation is the same over every SQL instance.

Has anyone also come across and found a solution for this?

SQL 2005 Merge Replication Issue


Ihave merge replication set up between two sites. Everything worked fine up until the subscriber wanted to update the publisher. I get the following error:

The merge process is retrying a failed operation made to article 'X' - Reason: 'The Merge Agent was unable to synchronize the row due to one or more unanticipated errors in the batch of changes. When troubleshooting, increase the -OutputVerboseLevel setting, restart the agent, and check for and resolve any errors generated by the database engine. '.

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 ?

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