Merge Replication Problem MSDE
Jan 24, 2007
Summary:
Failed to insert detail rows in master-detail scenario during merge replication. Not always, sometimes.
Topology:
I've got 5 servers running MSDE, one of them is central publisher/distributor for merge type of replication.
There is no row or column filtering: all subscribers have all data. Central publisher resolve conflicts with default revolvers.
Some tables have relations: master-detail (i.e. orders-ordersDetails, etc). Relations are defined in tables as FK.
My application which fills data use datasets with relations between tables defined the same way as in the sqlserver. So app first work with dataset which is unable to receive details without master record. When data updated to msde, it is done without errors, means master and details table updated correctly (tables at msde has relations too)
Applications running on 4 different locations and fill data to local db (subscriber to central publisher).
Sync occurs every 15 minutes in the following order (merge agents run at publisher/distributor):
subscriber1: every 15 minutes, starts at 00.00h
subscriber2: every 15 minutes, starts at 00.03h
subscriber3: every 15 minutes, starts at 00.06h
subscriber4: every 15 minutes, starts at 00.09h
Sync lasts for average 15 sec, never 3 min.
Problem details:
Message:
The row was inserted at 'CentPub.myDB' but could not be inserted at 'Sub2.MyDB'. INSERT statement conflicted
with COLUMN FOREIGN KEY constraint 'FK_OrdersDetails_Orders'. The conflict occurred in database 'MyDB',
table 'Orders', column 'OrderID'.
Description
CentPub is central publisher which just collecting data from subscribers. So, Order was made on one of the other subscribers different than Sub2, mean user at location3 insert order with details in local database, after a while, CentPub take this order to its database (MyDB), after that CentPub try to sync with some of the other subscribers (i.e. Sub2 == location2) and then for some unknown reason orderDeatils failed to insert in Sub2's orderDeatil table because constraint 'FK_OrdersDetails_Orders' conflict.
After that happened, thing goes like in http://support.microsoft.com/kb/307482 (generally: in next session, failed order details are deleted
from CentPub, which means deleted from all subscribers after syncs.)
The problem is that subscribers have tables with relations, so Cause isn't as describe in MS kb because my tables have relations at all servers.
After finished replications I have Orders without details records at all subscribers, so I assume that merge agents sometimes (not always) try to insert details prior to master table during the same sync session.
In resolution section of MS kb article they say 'Mark the subscriber foreign key constraints as NOT FOR REPLICATION'.
In relations definition I see 'Enforce relationship for replication) options which is enabled in my tables.
If I turn that option off, is it possible to happen that my subscribers receive details without master record? (My observation of behavior says that will not happen(that will lead to problems in my app because my datasets enforce relations too).
Deleted record I bring back to life with conflict manager in EM, but I'd like it never happen.
Is it a bug, side effect or something I do it the wrong way?
Thanks and regards
View 5 Replies
ADVERTISEMENT
Sep 4, 2006
Hello!
I have a problem with creating Merge replication between two instances of MSDE 2000. In the article http://support.microsoft.com/kb/324992/, published by Microsoft, described, that it is possible.
I am creating Merge Publication at MSDE 2000, with Distributor and Publisher configured at same machine, with options "Allow Pull Subscriptions" and "Allow Anonymous Subscriptions". There is no problem with creating of Publication. Even snapshot generation finishes successfully. Also I create a login with SQL Server Authentication and "System Administrators" database role at the publisher in order to connect to it from Subscriber.
The problem occurs, when creating anonymous pull subscription to this publication at another instance of MSDE. During initial synchronization an error occurs:
The process could not connect to Distributor '<publisher_server_name>'. Login failed for user '<login_created_at_publisher>'. Reason: Not associated with a trusted SQL Server connection. The step failed.
Although I didn't use Windows Authentication at all, so Subscriber doesn't need to connect to Distributor using trusted SQL Server connection.
What is a problem and how can I workaround?
Note: The same works correctly, if MS SQL Server used as a Publisher instead of MSDE.
Please, help!
I can provide publication and subscription creation scripts, if required.
View 1 Replies
View Related
Apr 3, 2007
Hi,
Our current environment is: SQL Server 2000 backend; XP/Windows 2000 clients with MSDE loaded; a Microsoft Access 2002 app using DSN to connect to the local MSDE database; Merge replication which is a job within the MSDE engine that we execute in VBA code using the following DMO code:
' Find the Job name to execute
lngJobCount = oServer.JobServer.Jobs.Count
lngJobIndex = 1
blFoundJob = False
While lngJobIndex <= lngJobCount And blFoundJob = False
strJobName = oServer.JobServer.Jobs.Item(lngJobIndex).Name
Me.lstJobs.AddItem strJobName, 0
If InStr(1, strJobName, "LEXData", vbTextCompare) > 0 Then
If InStr(1, strJobName, strServerName, vbTextCompare) Then
blFoundJob = True
End If
End If
lngJobIndex = lngJobIndex + 1
Wend
' Execute the job
If blFoundJib Then
Set oServer1 = New SQLDMO.SQLServer
With oServer1
.LoginSecure = True
.Connect strServerName
End With
Set oJob = oServer1.JobServer.Jobs(strJobName)
End If
We are now planning a move to a SQL Server 2005 backend and Express edition on the clients and the app would remain as Access 2002. Any pointers to documents that will be useful would be appreciated but I have a couple of specific questions as well.
1) As I understand it we can load Express side by side with MSDE so we will probably go down that path and load the data from MSDE into an Express version of the database. Once that's done we will create a DSN pointing to the new database and name it the same as the DSN which is currently used for the MSDE database. If we do this will our Access app simply work as before (ignoring replication)?
2) When we have our app happily running against a DSN that points to an Express database would we expect our existing DMO code (as above) to work, e.g. find a particular job in a list jobs and executes it? I can see that the replication job itself may have to be rejigged for Server 2005.
TIA - Peter
View 9 Replies
View Related
Feb 8, 2007
Hello,
I have the following scenario. An application using SQL Server 2000 SP3 on the server side and clients using MSDE 2000A (SP3a) on the client machine. Since the client is offline quite often merge replication is used to keep the clients in sync.
Now we try to upgrade to SQL Server 2005 SP1. The publisher and distributor upgrade (on the same box) worked fine and all clients could still synchronize. Fine :-)
Now we try to upgrade the clients to SQL Express SP1. Now the problems start :-(
1) After the upgrade the entry within the Synchronization Manager is gone (we can overcome this by using sp_MSregistersubscription or by manually disable and enable Synchronization manager on the subscription properties)
2) Initial Synchronization (takes a long time but) works fine, if I apply changes to the subscriber or force a reinitialize all following synchronization will fail. With the following messages:
Error messages:
The merge process could not clean up the subscription to 'tstvmw23':'TestBase:'TestBase'. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200965)
Get help: http://help/MSSQL_REPL-2147200965
New request is not allowed to start because it should come with valid transaction descriptor. (Source: MSSQLServer, Error number: 3989)
Get help: http://help/3989
Remark the test system I use is a nearly empty db (only with full schema and a few lookup tables) with 15MB. The same error occures if I create a new database an subscribe to the same publication.
Please help otherwise I'm forced to step back to MSDE.
Thanks in advance,
Thomas Hotz
View 3 Replies
View Related
May 26, 2015
What is the main difference between snapshot and transactional and merge replication?
View 5 Replies
View Related
Jul 6, 2006
I have a vb.net desktop application that uses msde 2000 for its databasing. I am trying to create a setup file using the output of my desktop application and the msde 2000 merge modules in order to install my own build plus an instance of msde 2000.
The setup runs properly and then asks me to restart my machine. After the restart, however, there is no such instance running on my machine. I am adding the following internal properties to my msi file using ocra:
SQLMSDESelected 1
SqlInstanceName Midas
SqlSaPwd Password1
SqlSecurityMode SQL
SqlDataDir C:Program FilesMicrosoft SQL ServerMSSQL$MidasData
SqlProgramDir C:Program FilesMicrosoft SQL Server
This should create an instance of msde on my machine but when I try to connect using enterprize manager or query analizer (I connect to <<machinename>>Midas), then I get the standard can not connect error.
I am at my witt's end and would really appreciate any help.
Thanks.
View 1 Replies
View Related
Aug 30, 2007
Hi all,
I know that adding a column using ALTER TABLE to add a column automatically allows SQLSERVER 2005 to replicate the schema changes to the subscribers, however, I would like to add a new column to an existing article that is being used for merge replication, however, I don't want this column to be replicated. Re-initialising the subscriptions is not a option. Help would be appreciated.
I am using SQLSERVER 2005 (SP1).
View 3 Replies
View Related
Oct 9, 2007
I am working on bringing our disaster recovery site to be a live site. Currently we replicate to one of out servers (server B) with merge replication (from server A). Server A also does one way transactional replication form some table to several other servers including servers at the DR site.
This setup is not going to be fast enough for what we need so I am wondering if a table is receiving merge replication will the merge updates also replicate down the transaction path??
Example...
Server B update a row and merges to Server A. With this update them replicate (via transactional) to Server C??
thanks...
View 5 Replies
View Related
Jul 20, 2005
I have a distributed inventory control database that I am going tomigrate from Access to SQL Server. I am going to use SQL ServerReplication to keep the data current.There will one SQL Server 2000 database at HeadOffice, and about 200branches will each have MSDE 2000.For tables that are the same for each branch (e.g. StockItems,Suppliers), I'm going to use Transactional replication with theHeadOffice as Publisher. There is no need for Merge replication,because only HeadOffice will be changing this data.For tables that are updated at the branches (e.g. deliveries, sales),there shouldn't be anyconflicts either, as each branch will only update data that isspecific to it. So, transactionalreplication with the Branches as Publishers seems like the answer.BUT, MSDE 2000 only handles merge replication as a publisher, so itlooks like I'm going to have to use Merge Replication if I'm am goingto use MSDE.Question: Do you think that it worth buying a full SQL Server licensesfor each branch, so that I can use Transactional replication with thebranches as publishers. Or, should I stick with MSDE 2000 and Mergereplication. Also, are there any other reasons why MSDE won't beadequate as the database at the branches.Thanks,Craig
View 1 Replies
View Related
Feb 14, 2002
Hi everbody,
I setup the Merge Replication , it is working perfectly. But i have one problem now it is updating both ways. I nedd one way. Any body tell me which parameter i have to change.
Thanks in advance
View 1 Replies
View Related
Aug 12, 2002
Hi,
My production box is running on NT4.0,SP6, SQL Server7.0,SP2. We implemented Merge replication. Working fine last 7 months. Last weekend i disabled replication, Successfully removed Distributor and Publishor. After that try add new fileds but won't allowed me. It's give the error message. I Also found Some Conflict_tables found almost 20 tables. All system Tables. Can delete these these tables, if i delete any problem my database.
I added filelds many times but this time i got errors.
Please help me anybody.
View 6 Replies
View Related
Jul 31, 2000
I have just installed replication on our production server to Merge Replicate with a Laptop server that will travel from time to time. I have now noticed that we cannot add or change any fields or attributes on the tables which are being replicated (which are all tables in the DB). This is a problem because we are changing and adding columns all of the time. Is there a way around this issue like shutting down the replication service or something? I have been unsuccessful in finding a way around this other than removing replication while we make changes.
Thank you in advance for any help!
View 1 Replies
View Related
Jan 22, 2001
I have implemented a Merger replication on our development server and I get a fillowing error when I try to update one of the table in publisher.
"Transaction cannot start while in firehose mode"
What does this mean.
Thank You,
John
View 1 Replies
View Related
Dec 19, 2000
Hi,
I read some where that replication has two types conflict resolution, 1. row based and 2. Column based...
If I am right...
Can any one point me how to find out this option and how to set it up....
Thanks,
Mohammed.
View 1 Replies
View Related
Sep 16, 1999
Hi all,
I have a merge replication going between 4 servers. The problem is when ever I do some BCP transfer to one of the tables in one of the servers. It puts the data in that table. But that Data does not get replicated to any other server like it should.
Please Advice on what to do. Is there any option I am forgetting to set or something.
Thank you for all your time in advance.
Aziz
View 3 Replies
View Related
Sep 12, 2003
I have successfully tried merge replication on single server with 2 databases.
now i want to do the same with different servers,
when i create pull subcription on server 2 which user account should I use?
it is giving log in failure
i tried using windows admin account and also the 'sa' account.
please help me out
thanks
View 1 Replies
View Related
Feb 23, 2004
Hi All,
I have posted this earlier and I am re-posting it simplifying what I had said.
The scenario is:
I have two sql server database instances with the same database schema and all. However, both of them have different data. I have not set them for replication at all. Now, I want to do merge replication between them such that the data between them could be syncronized.
When I do pull merge subscription I have two choices -
1.Bringing schema and data to subscriber from publisher
2.Not bringing the schema and data from publisher to subscriber.
Obviously, I chose the second choice. But upon syncronizing I dont see any data from publisher coming to subscriber and vice-versa. If I add new data to publisher and do syncronization, I can see ONLY the new data created after the replication setup in subscriber. If I add new data to subscriber and do syncronization then the new data is removed from subscriber and not propagated to publisher.
Is there any way I can make this work??
Niben
View 10 Replies
View Related
Jan 2, 2007
Hi,
I have just set up Merge replication, I have two servers, server A and server B, the merge replication worked successfully but I don't quite sure which databases should or should not replicated? If not, what other methods should I use?
I would really appreciated any comments or advice out there!
-whitebelt
View 14 Replies
View Related
Mar 21, 2002
I had set up merge replication. I got these error messages where replications starts "Column names in each table must be unique. Column name 'PubID' in table 'bonflict_DBName_PHP_Data_Publications' is specified more than once "
PHP_Data_Publications table defind as:
CREATE TABLE [dbo].[PHP_Data_Publications] (
[PHP_Pub_ID] [uniqueidentifier] NOT NULL ,
[PHP_Data_ID] [uniqueidentifier] NOT NULL ,
[PubID] [uniqueidentifier] NOT NULL ,
[UserID] [uniqueidentifier] NOT NULL ,
[Username] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Publication] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Pub_Year] [datetime] NULL ,
[Pub_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PHP_Data_Publications] WITH NOCHECK ADD
CONSTRAINT [DF__PHP_Data___rowgu__01E91FA0] DEFAULT (newid()) FOR [rowguid]
GO
CREATE UNIQUE INDEX [index_1466488303] ON [dbo].[PHP_Data_Publications]([rowguid]) ON [PRIMARY]
GO
Thanks,
Chanthol
View 3 Replies
View Related
Oct 11, 2001
Hi
Is it possible to modify or add new fields in sql 2000 when the instance
became publisher ? I tried it several times but it failed. I access microsoft website but i did not help.
Thanks
Pheckz
View 3 Replies
View Related
Oct 12, 2001
Hi everbody,
Anybody tell me about implementation of oneway merge replication.
Thanks
View 2 Replies
View Related
Feb 5, 2001
Hi everyone
SQL Server BOL says merge replication can be done in only one direction. But my understanding is Merge replication happens in both ways between publisher and subscriber.
How can i allow data movement from only wince sql ce subscribers and not from publisher running sql 2000 and win 2k.
Please reply immediately bcoz i require a solution which has to be implemented very soon.
Thanks in advance
Satheesh
View 1 Replies
View Related
Jun 29, 2004
hi,
in merge replication,i make a request subscriber with a priority 25.00,when i update the same date,it always choose the update from the publishing server,why????how does it work???
thx inadvance
View 1 Replies
View Related
Oct 5, 2004
I am using merge replication at remote connected via ISDN Dialup line. I got following error and replication fail.
publisher - PRSTGINDSQLIND
agent - PRSTGINDSQLIND-pml-pml-192.168.100.50SQLDWS-4
publication - pml
subsctription - 192.168.100.50SQLDWS:pml
error - The process could not deliver the snapshot to the Subscriber.
Agent Merge replication provider -2147201001
Agent 192.168.100.50sqldws 20037
ODBC 192.168.100.50sqldws
Agent - PRSTGINDSQLIND-pmst-pmst-192.168.100.50SQLDWS-3
error - The subscription to publication 'pmst' is invalid.
last command {call sp_MSgetreplicainfo(?,?,?,?,?,?,?)}
Thanking You
R.Mall
View 2 Replies
View Related
Jan 4, 2005
I am in a process of learning Replication in MSDE, especially Merge Replication
Server runs on MS-XP Professional
--------------------------------------
I have a sample Access project 'ReplTest' which has only table with only 2 columns.
DatabaseName:ReplTestDB
Table Name :TestTable
MSDE Instance Name:SVRMYINSTANCE
Now I would like to know how I can configure this database for merge replication
using SQL-DMO
Laptop runs on MS-XP Professional
--------------------------------------
I have another access project which is running on computer 2 and connected to the
ReplicaTest database.
MSDE Instance Name:LPTMYINSTANCE
My task is, when I am disconnected from server I would like to have a local copy of
the database to work with and then, when reconnected, need synchronization with the
server database and continue working from server database.
How to write this replication process from scratch using SQL-DMO objects in both Server computer
and Laptop computer.
I dont have enterprise manager in both computers since they use only MSDE
Can anyone help me?
Thanks in advance
JP
View 7 Replies
View Related
Jan 18, 2006
Hi,
I have a problem when doing merge replication. I need to have a identity column on both the publisher and subscriber, eg id. When the publisher is down, my app will now reference to the subscriber and insert into the subscriber. However when the publisher is up, the app will reference to publisher and start to insert into the publisher. This will cause conflict in the id. Also, i need the id to be in running order, therefore i cant use range for publisher and subscriber. Anyone have idea how to solve this problem?
Aaron
:(
View 2 Replies
View Related
Mar 7, 2006
Hello,
I am having a problem getting my merge replication to work out. I am wanting to make it where the merge happens on demand. I have tried to run replmerg through cmd prompt and I get the error 'The subscription to publication [namehere] has expired or does not exist.' I have had no problems running the merge agent within Enterprise Manager. I have thought of running windows sync manager but I do not want my people to have to type a password in everytime they need to sync. I am running the whole replication process through FTP. I am in desperate need of help. Anything would be great!
Thanks
View 4 Replies
View Related
Sep 14, 2006
Is it possible to setup a server as both a Publisher and Subscriber of the same database in a Merge Replication setup?
View 2 Replies
View Related
Jan 3, 2004
Hi Everybody
I have a problem with Merge Replication with this error message'The subscription to publication 'GsAdmin' is invalid.'.
Pleaese help me because I don't know what to do.
Thankyou
View 1 Replies
View Related
Mar 30, 2004
Can you merge replicate data from SQL Server 7.0 to SQL Server 2000?
View 1 Replies
View Related
Apr 1, 2004
I have merge replication setup between 2 servers and the subscriber had been offline for about 24 hours due to a network outage. What would be the best and easiest way to resynch the data. Would my best bet be a reinitialization or should I start a new snapshop and reapply. Also can I start a snapshot while users are using the publishing database?
Thanks,
Mike
View 5 Replies
View Related
Apr 14, 2008
I've tried to configure merge replication between SQL2005 and SQL Express use Web synchronize but the status of subcription is always uninitialized although I reinitialized it.
Could you tell me the way to start it ?
Thank so much.
View 7 Replies
View Related
Mar 20, 2006
Hi I am looking at SQL Replication and merge replication seems like the solution as I have 30 Remote offices who will all add data to their data bases and sync that data back to a central office. The central office will then use the "whole" data for reporting etc.
I am trying to figure out if I need static or dynamic filtering. I only want to filter horizontally on values found in a column called 'office' This value will be an integer that represents the different remote offices. But after reading through documentation I am still unclear as to whether I can do this with static filters.
Any help/advice appreciated.
Regards and TIA
ICW
View 4 Replies
View Related