NEED HELP - Merge Replication With SQL Server 2000
Sep 20, 2005
Hi, I posted about this before, and set out on my own to get this working, and haven't been able to. I'm trying to get merge replication working with my SQL server 2000, and after 2 weeks I still have nothing. I've gone through multiple 'walkthroughs' which all brought me to the same point. I'm getting down to crunch time, and I'm either going to use this or implement my own merge algorigthm (I'd much rather use this). So here's where I've gotten to:
I am using SQL Server 2000 SP4 running on a MS Server 2003 with SP2. I am preforming Merge Replication on a continouas basis and the Merge Agent keeps stopping with the error 203 The process could not enumerate changes at the 'Publisher'. There is no additional information available with this error. Does anyone have any sugestions as to why this is happening. I can manually re-start the agent but it will fail again for particular reason.
Hi, I am trying to replicate a production database server,on sql server 2000 at a particular geographic location to a new failover database server on sql server 2000 in a different geographic location via internet. The intention here is to use the failover database during times when the production server is down or busy and the synchronization needs to be sceduled for every 10 min. It will be of huge help if any expert could give the detailed process involved and any precautions that need to be taken. I also need to keep inmind to use the ever/odd sequence number generator during the replication implementation. Also, Whether to use row-level or column-level tracking?
I do a merge replication between Sql server 2000 and SQLCE 2.0
On my SQL2000 I have 4 tables i want to merge (specific columns only ) in 1 table for Merge with my SQLCe ( the table will be use for read only)
Question 1: What is the best pratice for keep the information update? Run store procedure before the synch for re-populate the table?:confused: or Make Trigger INSERT, UPDATE, DELETE in the all 4 table?:confused: or a mixte?:confused:
Question 2: Does someone know about some web site talk about this type of trick?
I have a problem with sql server merge replication in sql server 2000. If my db owner €œdbo€? and replication setup under €œsa€? account it works with out any problem. But when I use another db owner it can not work properly. For an example I have customer table ([dbo]. [Customer]) When I setup merge replication under sa account it€™s work properly. Again I was setup merge replication using another db owner ([INV]. [Customer]) It doesn€™t work.
I am trying to migrate from my current system, where I do merge replication from Windows Mobile devices running SQL Server CE 2.0 to a central database running SQL Server 2000 sp3a. I want eventually to move to a system running SQL Server 2005 CE replicating to a SQL Server 2005 back-end. But the transition will need to be gradual, and I may have to support both systems for a while until I can convert all clients from the old system to the new. I also need to do thorough testing.
So ... I'm trying to set up a test environment giving me the maximum possible flexibility to do my testing. Ideally, I'd like to set up SQL Server 2000 and SQL Server 2005 on a side-by-side basis, in a manner that would potentially allow mobile devices running both SQL Server CE 2.0 and SQL Server 2005 CE to sync with either back-end server.
Can someone provide me with guidance as what is possible to set up here? I know that SQL Server 2000 and 2005 can be installed side-by-side on the same server. It also appears that you can set up SQL Server 2000 so that EITHER SQL Server CE 2.0 OR SQL Server 2005 CE can sync with SQL Server 2000 (see www.microsoft.com/sql/editions/sqlmobile/connectivity-tools.mspx), but I don't know if it's possible for BOTH SQL Server CE 2.0 AND SQL Server 2005 CE to sync to the same SQL Server 2000. As for SQL Server 2005 ... it appears to be possible to set up SQL Server 2005 so that BOTH SQL Server CE 2.0 devices AND SQL Server 2005 CE devices can sync to the same SQL Server 2005 (see web page cited above). However, I don't know if it's possible to set up a SQL Server 2005 server installation in this manner while at the same time having a side-by-side SQL Server 2000 installation supporting any level of mobile merge replication.
I have a merge replication publication that has been running for months. This week the Snapshot started failing, reporting that an article was not included in the publication. I checked and found 3 articles that mysteriously no longer show up as being in the publication. When I attempt to add them it reports that it can't add them because there is already an article by that name in the publication. Apparently some table has lost its rows for these articles while another table(s) still has its rows.
Has anyone had this problem? Is there a solution short of dropping the publication and starting over - a solution that would literally take weeks for us?
we had setup merge replication on 2 db servers. For some reason, the subscription started failing a month back with the error " invalid object sysmergexxxx on the subscriber. I did a reinitialize and now all the changes on the subscriber which werent synced got deleted. I have tried all 3 log recovery tools with no luck. Is there any hope of recovering data. the last backup on the subscriber was a month ago.
My question is regarding syncronization between two databases in MS SQL Server. If we wanted to do MERGE REPLICATION then we can set up pulishers and subscribers using the PS/SQL script (stored procedures) without having to use Enterprise Manager gui tools. I see that after you set all the publishers, distributors and subscribers and you want to start syncronization explicitly the only way you could do is go to Enterprise Manager and find the Publishing/Subscribing Items and right click on them and choost "Start Syncronization" (Sorry I have omitted the nitty-gritty details about how to find those Publishing/Subscribing items in Enterprise Manager).
I am wondering whether we could trigger the start of syncronization using any system stored procedures or without using Enterprise Manager.
I'm currently trying to setup a SQL 2k (SP3, build 922) merge replication publisher and a push subscription to a SQL 2005 (RTM release, no hotfixes) subscriber. The distribution database resides on a separate SQL 2k server (SP3, build 1007). I get the error below leading me to believe merge replication is not compatible between versions.
Replication-agentclassname: agent Agent_Name failed. Procedure or function sp_MSupdatesysmergearticles has too many arguments specified..
I say this b/c I've tried SQL 2005 32-bit and x64 subscribers and both give the same error. Anyone have any ideas if this is by design or just a bug that will be fixed later? Thanks.
We have developed a mobile system that uses merge replication for SQL Mobile to SQL 2005. Previously we have developed mutliple mobile systems using merge replication for SQL Ce to SQL 2000.
Based on the knowledge we had gathered over about 4 years, we applied the synchronisation parameters for the SQL 2005 solution as we would for the SQL 2000 solution.
We have found there are some differences. Not too surprising I suppose, only some of these have us a little baffled.
For instance, there was a little flag called keep_partition_changes in SQL 2000 that is supposedly superceded by the use_partition_groups flag. However, if you don't set up your filtering to conform to the standards required by the use_pre_computed_partitions flag if you want it set to true, then the use_partition_groups flag gets set to false - also the @partition_options falg gets set back to 0 (static or non-unique data) when we want it at 3 (Single Parition, One subscriber).
To top it all off, when you get the use_partition_groups flag working, there are restrictions on which columns you can update on the device. WTF? This seems ludicrous, to be unable to update data at the subscriber - particularly information that allows you to effectively "delete" data from your subscription.
Examples of the current behaviour are as follows,
On initialize for a subscriber, the subscriber will receive their own data as inserts, plus exact multiples of that data as updates. Say there are 100 rows in TableA, the subscriber gets 100 inserts, plus 6000 updates. TableB has 20 rows, the subscriber gets 20 inserts, 1200 updates.
Further to this, performance goes out the window when synchronising changes. Typically the data flow will be between 5 and 200 changes in both directions for a synchronisation. We are seeing sync times in the replication monitor of over 20 seconds per user. Surely the calculations do not take that long. The tables in the database are not very large.
This behaviour gets significantly worse as we load the system. The application has an auto sync function which is timed to operate evry 10 minutes. However, now that there is in excess of 50 or so users on the system, those synchronisation times blow out to multiple minutes and the server starts to thrash. We have looked at indexing and maintenance but to no avail.
Everything still points to the merge replication setup.
So, it seems obvious to me that we are mising some key information about how to set up merge replication in SQL 2005. We woudl be very gratefull if someone could point out the errors of our ways.
Sorry for the convoluted post. Hope someone can help us.
Publisher is 2005 x64, subscribers SS2000 (SP3) and SS2005 x64. Pull agents, no filters on subscriptions. We are seeing many seemingly random conflicts on between SS2000 subscriber and publisher. It happens on several different tables.
One table is never editted, only inserts happening everywhere and deletes happening on the SS2000 subscriber. Deletes will sometimes generate conflict. Reason is '"he row was deleted at 'CTS11.CTS' but could not be deleted at 'cts4a.cts'. Unable to synchronize the row because the row was updated by a different process outside of replication." CTS11 is SS2000 subscriber, CTS4A is publisher.
Probably unrelated bug but when looking at conflicts on this same table in SS2005 conflict viewer, get error "ID is neither a DataColumn nor a DataRelation for table summary (System.dATA)" and then "Column ID does not belong to table summary (System.Data)". ID column is rowguid, only unusual thing about table is that it has varchar(8000) field plus some other fields.
Other tables generate conflicts with this reason "The row was updated at 'CTS11.CTS' but could not be updated at 'cts4a.cts'. The merge process was unable to synchronize the row." I enabled verbose logging in the merge agent but the log file didn't contain any further explanation.
This same topology and schema worked fine when all publishers and subscribers were SS2000.
Any insight into how to fix this would be appreciated.
We have a module in our business-application, that automatically installs merge replication of the business-application's database, both at publisher and subscribers. It's intended for Sql-2000. Now we need it to be applicable also for Sql-2005, so the module requires some changes, because, as we noticed, sql-2005's replication technology differs from Sql-2000's one.
A few questions to experts, familiar with Merge Replication in SQL-2005:
1. Is it possible to create hybrid replication, with publisher running at SQL-2000 (MSDE) and subscribers running at SQL-2005 Express? Merge publication is not supported in SQL 2005 Express, but some users may require option to use the application at free-of-charge database platform.
2. How deep are changes in merge replication implementation at system level? Is it just modified a bit since SQL-2000, or changed entirely? This knowledge is needed, because the module uses some low-level features (executing system sp's, querying replication-specified tables, etc.). For example, when we tried to create subscription of existing publication in SQL 2005 using the module as is, we found out that sp_addmergepullsubscription_agent doesn't use @encrypted_password parameter anymore, and subscription creation process failed.
3. If anybody has experience using merge replication creation/deletion/detection scripts, generated by SQL 2000, in SQL 2005! Please, tell - what more problems may happen?
I have a problem Setting up pull subscription from a publishser to a subscriber , but I could set up Push subscription with out any problem between these two server .. can any one help me out.
I run SQL Server 2000 Enterprise edition.
I get the following error
"Publication 'ProdDB' does not exist. The step failed."
where ProdDB is the publication database from which my Push subscription is running.
Hi, I have two databases on MS. SQL Server 200 the first one is themain DB and the second one is a replica of it, both of them indifferent territories .I connect them using merge replication justwhen I need to merge the data.The problem is when I make import data or export data from anylocation (MS access ,MS SQL server ) to the second one (subscriber DB)or the main DB(publisher and distributor ) ,the imported data doesnot replicated to the other DB ???Normal transaction replicated smoothly.I hope you can help me.Thanks in advance.Mahmoud Khebaisa.
I have some problem about Merge Replication.i'm trying to merge Database A to Database B in local Server. So Database i want publisher contain this:
Because only Data in Table change therefore so i choosed it 100% Snapshot was generated after that ( Problem not a valid window user i already figured out )After that. I created Local Subscription in same Server ( Pull subscriptions and Subscription type: Client ),now problem is throw out."The schema script 'vwBuyADT_513.sch' could not be propagated to the subscriber."
I tried research so many time in Google but any information i found isn't useful for this problem.This problem still can ignore,Synchronization still running. But after 4-5 Hours running..This messages throw out: "The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation"
My question is: Is there anyway solve 2 probem? : 1. "The schema script 'vwBuyADT_513.sch' could not be propagated to the subscriber." 2. "The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation"
I would like to know what alternatives are avaliable to SQL Server Merge Replication. I am also looking for Third Party Tools. Can anyone name a few for me.
I am having problems with my sql merge replication. Whenever a user syncs up to my main database, most of their records are deleted instead of being merged. Or the records on the main database are inserted and it replaces the whole table with the records on the remote laptops. Is there a way to prevent this from happening? Someone please help me.
I have installed SQL Server 2005 x64 Enterprise edition with Service Pack 2 on a Windows Server 2003 x64 Standard Edition with Service Pack 2.
Now I have to configure Merge Replication that will work with SQL Server Compact Edition database on Windows Mobile devices.
Distributor and the Publisher are the same server.
IIS 6.0 is installed on the windows server. I have installed the SQL Server Compact Edition Server tools on the server. However the compact edition server tools are only available for 32bit servers and I have also found out from the article http://support.microsoft.com/default.aspx/kb/912430 that you cannot replicate data from SQL Server 2005 to SQL Server Compact Edition by using the 64-bit version of IIS. So if this is true does that mean I can not use merge replication on 64 bit server? Does that mean I have to get another 32 bit server with 32 bit IIS on it to make this work or is there another work around. Am i missing something here?
In a nutshell, I want to do a merge replication with a SQL Server and several Access databases. I haven't been able to find anything in the documentation or 3rd party books.
Hi , I would like to get your advice in an issue that I am facing regarding merge replication. The flow is like: 1) User enter his login name and password. 2) Host_name is set as the login name that the user enters. 3) Based on the HOST_NAME property after filtering user gets the records relevent only to him (Differnet subscriber may use same mobile device).
4) He make some changes in the local sdf according to the business logic. 5) The application Syncs with server on the next login session.
My issue is when I am trying to run the application in emulator exactly at the sdf updating part the application terminates. Error mess: Connection to Remote Device has been lost..... If I execute the same sql insert query it works om the local sdf and master database. Emulator used where Pocket PC 2003 SE and Windows Mobile 6 classic emulator. I have installed 1) Visual Studio 2005 SP 1 2) net compact framework SP2.
When I comment all the code related to Sync ,application works witout any issue. And the same application works in my "I mate kjam".
The code that I use is given below: ==========================================================================
public void Sync(string hostname, int newsub) // Parameters for HOSTNAME and flag to denote new user or existing //user.
I am running SQL Server 2000 and SQL Server CE on the handheld device. I have created the publication on the SQL Server.
I can subscribe to the publication successfully on the handheld and it creates the .sdf file just fine.
When I then attempt to synchronize, the process will run for a long time and eventually error out. (The database has a couple large data tables that are used for value lookups)
The error says the connection was broken.
Any suggestions on correcting this?
I am thinking of breaking the replication database into 2. One with the large lookup files and one with the updated tables. I still need to get the large database with the lookup tables onto the handheld though.
Any thoughts, advice or similar experience handling a situation as this would be appreciated.
I want to transfer data between an sql server 2005 and my pocket pc. So i've installed a sql server CE on it.
I know that i can use the merge replication but, i don't have any IIS. So, i've got to transmit data though email that have got a file attached. I Know how to email from sql server 2005. When the pocket receive that email, i don't know how to insert the data in sql ce from outlook compact. Doi have tu use SSIS , in that case how can i run the package?
I have set up merge replication with 1 publisher and 1 subscriber. Distribution is handled by a 3rd server.
I can generate a snapshot at the publisher and apply it to my subscriber. But when I insert some data (approx 30,000 rows) , the Synchronization agent gives the following error when It runs:
The merge process is retrying a failed operation made to article 'xxx' - 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. '.
I have increased the OutputVerboseLevel setting and specified a file path in the -OutputMessageFile but the File is not being populated. All the references on books online say to put the file path in the -Output parameter but when I do it says it can only hold an integer value. So I cant see the errors generated by the database engine.
I'm currently investigating the feasibility of having SQL Mobile on a number of devices running Windows CE. These devices will have to synchronize with the server (either using merge replication or RDA) over GPRS. The problem I have is that some of these devices will never be online. Is there are a way of synching these offline devices? The model I had in mind was using a USB key (or some other storage device) to download the latest updates from an online device and transferring this data to the offline device, and vice versa e.g. from offline to online and then merging. Is this feasible?
"The merge process could not retrieve identity range resource for table..."
then after solving this error we again started syncing of servers manually then we got some other error like
"the process could not enumerate changes to the subscriber".
We tried our best to solve this problem but some error appears each time like
"The process could not deliver the snapshot to the Subscriber."
"The schema script 'sync -t"Design" -o"dbo" -d"\ECOSYSTEMD$FTPMSSQLEcoSystemftpECOSYSTEM_MLEcosystem_MLEcosystem20060619204802Design_1337.bcp" -hORDER( [intDesignId] ASC)' could not be propagated to the subscriber"
again identity erroe emerges and agin we do the same process.
We have a central office with a SQL2005SP4 server (yeah, I know... old as heck) that's the main database and it has multiple subscribers in regional offices. Well... one of the regional offices server is failing, and it needs to be replace.
The original server is an ancient Win2003 86x
The Server team will build a new Win2008r2 64x, and use the same name and IP address
And I'm tasked with the SQL part.
I'll be installing the same version/patch of SQL, but 64x instead, and migrate all databases, including the system databases.
How do I handle replication? Do I need to reintialize from scratch? or can I just use the backup as a starting point?
We have a table in an SQL Server 2012 database that stores tree-like structures. Simplified for the purpose of my question, it has the following format:
Id int identity, ParentId int, GroupId int
Each record of the table represents an object identified by Id. An object may or may not have a parent in the same table, such that object.ParentId = parentObject.Id. A root object has ParentId = NULL. There are multiple root objects, so the table in fact stores multiple trees. What’s important is that the tree depth is not fixed, i.e. theoretically there can be any number of ancestor generations for an object. GroupId is a property of a root object; in theory none of the children of a root object has to have GroupId <> NULL; it can be assumed that any child has the same GroupId value as its root object.
A sample table having two roots (one grandparent and one parent), one non-root parent/child and 4 child roots:
The table is not normalised, i.e. there’s no separate {root_object : group} table. However I don’t think normalising the table would solve the problem.
Now the problem. We need to set up merge replication from the table above (Master table) to the table of the same format in another DB. We need to replicate only those rows of the Master table that have a certain fixed GroupId value, e.g. 200 in the example above. If we ensure that GroupId in all descendant objects of a root object has the same value in the table as the root object itself that would be trivial. The table would look like this:
However out of performance considerations, we would like to avoid if possible filling GroupId for the descendant objects, because as it must be clear from the above, GroupId for a descendant object is quite easily deducible via a stored procedure or UDF (just need to go up the tree until ParentId = NULL). The problem is, I don’t know how to achieve this in a merge replication filter: it would only allow WHERE conditions and joins. I’ve have not had much luck with joins for merge replication in general, but here we have more complex algorithm, because the number of tree levels can be different for every object. And merge replication would not allow using UDF…
Hi I am trying to complete sql server compact edition book online walkthrough "Creating a Mobile Application with SQL Server Compact Edition". I am successful to complete it as guided using all default settings (e.g. anonymous access, windows authenticaion etc.) and the sample run on both emulator and my pocket pc svc5000. I have following configuration..
Windows 2000 professional sp4 Sql server 2005 standard edition sp1 VS 2005 profesional edition (sp1 not installed) Sql server compact edition Sql server compact edition server tools
But the problem is that in production environment i will not be able to connect to the sql server using named instance (e.g machine_nameinstance_name). i have to use ip address in that situation. so i tried to modify the sample to use ip address in place of named instance and then the problem starts.. I have configued my sql server to allow remote connections using tcp/ip and named pipes using sql surface area config tool.
When using named instance i used following code to Synchronize with sqlserver 2005 with and it worked perfectly :
Dim filename As New String("Program FilesTestTest.sdf") Dim repl As New SqlCeReplication() repl.InternetUrl = http://machine_name/Test/sqlcesa30.dll repl.Publisher = "machine_nameinstance_name" repl.PublisherDatabase = "Test" repl.PublisherSecurityMode = SecurityType.NTAuthentication repl.Publication = "Test" repl.Subscriber = "Test" repl.SubscriberConnectionString = "Data Source='" + filename + "';Password='';Max Database Size='128';Default Lock Escalation ='100';" Try repl.AddSubscription(AddOption.CreateDatabase) repl.Synchronize() Catch err As SqlCeException MessageBox.Show(err.ToString) End Try
When i tried to do replication using ip address it shows following error:
System.Data.SqlServerCe.SqlCeException was unhandled HResult=-2147467259 Message="Failure to connect to SQL Server with provided connection information. SQL Server does not exist, access is denied because the IIS user is not a valid user on the SQL Server, or the password is incorrect." NativeError=29061 Source="Microsoft SQL Server Compact Edition" StackTrace: at System.Data.SqlServerCe.SqlCeReplication.Synchronize() at TEST.MainModule.Sync() at Test.MainModule.Main()
I am using following code to connect using ip address:
Dim repl As New SqlCeReplication() repl.InternetUrl = http://XXX.XXX.XXX.XXX/test/sqlcesa30.dll repl.PublisherNetwork = NetworkType.TcpIpSockets repl.PublisherAddress = "XXX.XXX.XXX.XXX,XXXX" repl.Publisher = "SQL2005" 'instance name of sql in sql2005 repl.PublisherSecurityMode = SecurityType.NTAuthentication repl.PublisherDatabase = "Test" repl.Publication = "Test" repl.Subscriber = "Test" repl.SubscriberConnectionString = "Data Source='" + filename + "';Password='';Max Database Size='128';Default Lock Escalation ='100';" repl.AddSubscription(AddOption.CreateDatabase) repl.Synchronize()
I have give rights to iuser_manchine_name in the public access list of publication and on database also.
I don't have any idea on where i am wrong... Please help soon.. Many thanks in advance..
We are trying to implement merge replication between 2 servers. Database size is around 11GB.
When I tried to create publication, snaphot agent is failing with following errror.
Error: 14151, Severity: 18, State: 1. Replication-Replication Snapshot Subsystem: agent NBTENTSQL1X-PayDirectWeb-PayDirectPub-2 failed. The replication agent had encountered an exception. Source: Replication Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException Exception Message: String or binary data would be truncated. Cannot find the object 'MSmerge_sel_4D388
The table where the snapshot is breaking contains around 169 columns. Can someone please help me get through this error ?
