Merge Replication with SQL Mobile Server 2005 / SQL Server 2005
I am using Merge Replication to Synchronize a database on a mobile PDA.
I create an empty table 1st using;
"engine.CreateDatabase()"
then sync with ;
"replicator.Synchronize() "It works great the 1st sync, but subsequent calls to "replicator.Synchronize() " dont download new table changes at the server level to the remote PDA.
I have found by calling ;
"replicator.ReinitializeSubscription(True)"I can get new updates downloaded to the handheld, but it is very flaky / glitchy when using this call and hangs / freezes / or doesn't work all the time, every second attempt seems to work....
My questions is...
1) after initial synchronization, is there steps at the server level or handheld level to request new updates to download?
- what are the proper calls / steps for subsequent updates at the server level to be download, or at the remote level, new records to be uploaded, and force merge replication ?
2) the purpose of "replicator.ReinitializeSubscription(True)", is it for maintenance or to request new updates?
3) the server has "reinitalize subscriptions" options, should these be used to force updates to the remote PDA when merge replicating or are they maintenance features?
4) Should the above all work properly with just "replicator.Synchronize() " and I am dealing with unstable / beta software ? Should I use "replicator.ReinitializeSubscription(True)".
Any advise, sample code / steps at the subscriber level would be helpfull or steps at the server level to insure new records are downloaded / merge to existing subscribers using merge replication.
Thank you in advance.
Carlos
sample code at the subscriber PDA level is below...
Dim engineAs SqlCeEngine = _
New SqlCeEngine(CONNECTIONSTRING)
If File.Exists(LOCALDATABASE)Then
tSync =
FalseElse'File.Delete(LOCALDATABASE)tSync =
Trueengine.CreateDatabase()
EndIfProgressBar1.Value = 40
Dim replicatorAsNew SqlCeReplication()
replicator.InternetUrl = INTERNETURL
replicator.Publisher = PUBLISHERSERVER
replicator.PublisherDatabase = PUBLISHERDATABASE
replicator.PublisherSecurityMode = _
PUBLISHERSECURITYMODE
replicator.Publication = Publication
replicator.Subscriber = SUBSCRIBER
replicator.SubscriberConnectionString = _
CONNECTIONSTRING
m_SyncBar.Text ="Synchronization in progress..."
If tSync =FalseThen'replicator2.ReinitializeSubscription(True)EndIfreplicator.Synchronize()
Hi Carlos,
You dont need to reinitialize the subscription to get the subsequent changes nor you need to add the subscription. You just need to synchronize again and that should propagate the subsequent changes.
You want to use reinitialize when you think the subscriber is totally out out sync and want the agent to start from scratch (i.e.apply the schema scripts and getting the changes) This is like the sync after adding the subscription for the first time. Any changes at the subscriber that are not yet synched to the publisher will be lost if you decide to reinitialize without upload.
Thanks,
--Mahesh
Hi mahesh,
I am ashish here and there is somewat a same type of problem here which i m facing i am sending you the detail of the same for ur review and kind suggestion.
Please take some time for it.....
ISSUE:
While synchronizing data between the PDA and MS SQL Server database, using Active Sync connection, the sync process fails at times and displays an empty error message box and occasionally it shows the following exception:
Error During Synchronization:
A native exception occurred.
ExceptionCode:0xc0000005
ExceptionAddress:0x01627b28
Reading: 0x1e000000
OK to terminate
CANCEL to debug
Moreover the .SDF file on the PDA gets corrupted. The ActiveSync connection drops as soon as the sync process fails. Later when the .SDF file is deleted and restored the synchronization is successful.
Note: The replication monitor in the SQL Server indicates that the synchronization was successful even when the above error occurs.
SCENARIO:
Steps to replicate:
- The PDA is placed on the cradle and a manual connection is established with the SQL Server database using the guest partnership.
- The PDA application is invoked. Some data is saved in the local .sdf file via the application.
- The User clicks on the “Sync” menu option (Sample code below **) to start the sync process
- Most times the sync process is successful. Occasionally the above issue is encountered.
** Sample code where the app crashes we encounter the issue:
Public Sub Synchronize (ByVal vShowMessage As Boolean)
Dim ce As SqlCeReplication = Nothing
Try
ce = New SqlCeReplication
'Set Internet properties.
ce.InternetUrl = vInternetURL ' "http://xx.xx.xx.xx/SQLCE/sscesa20.dll"
'Set Publisher properties.
ce.Publisher = vPublisher '"CGH-RSMS"
ce.PublisherLogin = vPublisherLogin '"sa"
ce.PublisherPassword = vPublisherPassword '"xxxxxx"
ce.PublisherSecurityMode = SecurityType.DBAuthentication
ce.PublisherNetwork = NetworkType.DefaultNetwork
ce.PublisherDatabase = vPublisherDatabase '"RSMS"
ce.Publication = vPub_Name '"PUB_RSMS"
'Set Subscriber properties.
ce.Subscriber = vSub_Name
ce.SubscriberConnectionString = "Data Source=" & DB_PATH & vDatabase_Name
If File.Exists(DB_PATH & vDatabase_Name) = False Then
ce.AddSubscription(AddOption.CreateDatabase)
End If
ce.Synchronize()
Catch ex As SqlCeException
If vShowMessage = True Then
MsgBox(ex.Message)
End If
Throw ex
Catch ex1 As Exception
If vShowMessage = True Then
MsgBox(ex1.Message)
End If
Throw ex1
Finally
ce = Nothing
End Try
End Sub
I am also facing a problem at the step where i try to synchonize the application data of PDA to the Web based desktop version:
2. On synchronizing the data from the PDA database to the Sql database on the web system
Then the following Error is popped up.
29045: Initializing SQL Server Reconciler has failed.
Please do help me with ur guidance.
Regards,
ashish.
hi
i have a scenario where in i have to display data from MS SQL Server on to the PDA.
Can any body help me as in how to proceed with this.
You can create a merge publication on the SQL Server and pull it down to the PDA as a subscription.
What version of SQL are you using?
There are detailed examples and samples in Books Online and msdn.
Here is one overview article: http://msdn2.microsoft.com/en-us/library/ms152568.aspx
I found some code for Merge Replications.
But is there any readymade tool available to synchronize between Sql Server 2000 and Sql Server CE on Handheld device.
I need such a functionality which will not just synchroniz new records both side, but also get the updated records in both side database.
If you setup replication between the SQL Server and the SQL Server CE, not only new records but any inserts, updates, deletes happening at either side are synchronized.
There are samples in BOL or msdn, which can probably help you.
Thanks Mahesh,
I have successfully created an application for SQL Server Mobile and SQL Server 2005 Sync.
Now the Client wants the same feature in SQL Server CE and SQL Server 2000.
Here the problem is that I am not able to Create Subscription as i did in SQL Server Mobile and also when i access the sqlcesa30.dll in IIS, instead of agent message it directly starts downloading.
Glad that your solution is now working.
I dont understand the problem correctly.
When you mean cannot create subscription, is it failing? What is the error message?
Also what do you mean by starts downloading? Do you mean to say without creating the subscription, the agent has started the download process?
Well Agent is also working now.
Now, as our client requires the Database in SQL Server 2000, how can I create Subscription?
Where is it failing?
Wouldnt creating the subscription and sync not work for 2000 with CE.
Previously I was using Sql mobile in device, that was worked fine. Now as per client's requirement they want the Database in sql ce.
First of all I would like to know that which are the required cab files for sql ce environment in the device and from where i can get those?
When i see the merge agent, it is connecting Sql server 2000 from sql mobile and it is able to connect the sql server 2000 publisher, but after connecting to publisher when it tries to connect the subscriber it stops with message "Connecting to subscriber WM_Dhaval1". after waiting for 10 minutes the message changes to "The agent is suspect, no response within last 10 minutes" with failed satus. I am sure the problem is with subscriber.
Is sql mobile compatible with sql server 2000? or i have to use sql ce for mobile device explicitely?
