SQL Development For Desktop/Device Synchronization
May 29, 2007
I want to create an application that will have support two platforms, Window's desktop and Windows Mobile 5/6. The application will need to have the data synchronized when the device is put in the cradle via ActiveSync on WinXP or Sync Center on Vista.
I want to use the new SQL Compact Edition to do this. The problem I'm having is how do I sync the data? I know about RDA and Merge Replication, but for a generally distributed application to customers via the internet, how do I make this work seamlessly without a complicated setup? This application will be sold to thousands of users without technical knowhow of how to use advanced synchronization configurations.
I have to assume other developers must be interested in a scenario like this, but I have yet to see a good solution in my searches on the web or Microsoft's site.
I don't want to use Access on the desktop. I also don't want to use RAPI to transfer files as there are security issues with newer devices.
I'm coming close to scoping out a 3rd party database or home-grown solution, but this would not be optimal.
...works on my device, but on my computer it gives SqlCeException with message: The database file cannot be found. Check the path to the database. [ File name = Program FilesTreeNotesTreeNotes.sdf ]
I've tried all sorts of things, but the computer just can't find the database. I've even tried putting a copy of the database into... C:Program FilesTreeNotesTreeNotes.sdf...hoping that the computer will find it there.
I want to create an application for home users which runs on their mobile device (smartphone) and their desktop PC and keeps the data synchronized between them. On their desktop I want to run either SSCE or SSExpress. There are so many options in the SSCE literature, which options should I use? How do I synchronize the data? The literature has so many options I don't know where to start.
I am developing an application for a Pocket PC (PPC). I am using Visual studio 2005, and the Operating system on this PPC is Window Mobile 2003 SE. The followings are my codes to connect PPC with the database on my PC with sql server 2005. But when I cick the button1, the error with "SQL Server does not exist or access denied." Any ideas? Thanks a lot.
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Math
Imports System.Drawing
Imports System
Public Class Form1
Dim strConnection As String = "Server=localhost;Integrated Security=SSPI;database=AdventureWorks"
Dim strQuery As String
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Label1.Text = "Hello, Clicking after."
ReadOrderData()
End Sub
Private Sub ReadOrderData()
Dim queryString As String = "select * from Person.Address where AddressID < '10'"
Using connection As New SqlConnection(strConnection)
Dim command As New SqlCommand(queryString, connection)
connection.Open()
Dim reader As SqlDataReader = command.ExecuteReader()
M doing PDA Project in Vb.net Compact framework [ CF ]. In tht I need to do synchronization My PDA's database with Desktop Computer's database manually. So Plz Suggest me some options ..... One of them I know & Its RAPI.. --> "RAPI allows desktop based applications to talk with CE devices while they're connected to the desktop via ActiveSync".
but M confused regarding ActiveSync Bcoz i want to do it Manually.
Dear All, i have a question abt winCE 4.2 and SQL server CE. i am using VB.net of Visual Studio 2005 My platform is using a PDA with winCE 4.2 and SQL server CE. The Host program is using dbf files on desktop side.
I got a problem of how to sync / read the sql CE data from a windows application.
so, i wanna ask,
1. any method to access the data from winCE data by windows application? or can i convert the sdf file to windows readable files? or any others?
2. Can i use a MDB to sync with SQL server CE? can i synchronize the mobile device which has a SQL Server CE database with the Access database on the desktop?
last question, 3. is that windows CE .net 4.2 not support pocket access (cdb) anymore?
Have two C# applications - one WinForms desktop app, the other a WinForms smart device app. Both use SQL Server Compact CE 3.5 to store data locally. Both work just fine.
Now the desktop app needs to open and update the .SDF file that is stored on the Windows Mobile device. (Device will be docked via USB.)
What format would the path to the .SDF look like that the WinForms application would need to specify in order to open it?
I have a database in development in SQL Server 6.5 that needs to be occasionally deleted and rebuilt from a script when table structures are changed. I found that when very complex queries were performed, the 2 MB default size of tempdb filled up and returned errors, so I went to the Enterprise Manager to expand tempdb, learned that I had to first expand a device to expand tempdb into, and foolishly chose to expand tempdb into the same device space used by my application, instead of into one of the system databases. Now when I try to delete the device in preparation for its rebuild, the Enterprise Manager responds with an error message saying the device can't be deleted because it contains system tables. Is there any way to get the expanded portion of tempdb out of my application device so that the device can be deleted, without reinstalling SQL Server?
There is some strange behaviour i've recently noticed while watching synchronization progress in Replication Monitor on SQL 2005 Server Standard with merge replication configured. The merge process seems to repeat several times.
This is the initial synchronizaion (reinitalization at the subsciber). Client is using Microsoft.SQLServer.Replication objects from .net framework assemblies.
The synchronization starts normally (status is "Running"). The last message of selected session box shows (among other messages): "Beginning evaluating partial replication filters" then "Finished evaluating partial replication filters" and finally "Merge completed after processing xxx changes... etc." after a few seconds. Status changes to "Completed" and then... the merge process starts again!! "Beginning evaluating partial replication filters" etc. And this repeats about 15-20 times.
And so whole process takes about 15 minutes instead about 45 seconds to complete initial synchronization. The number of changes is "Merge completed after processing ..." never change since the first such message.
Is this some bug in web synchronization or some invalid configuration setting? Why does merge process repeat itself so many times??
Basically I've been using Visual Studio 2005 for a few weeks now moving a Pocket PC project from 2003 to 2005. When I hit the Start Debugging Button every time until today the project would rebuild and deploy to my pocket PC allowing me to debug etc but now I get
The remote connection to the device has been lost.
Please verify the device conection and restart debugging.
I used to get this problem in VS2003 sometimes and just like the numerous posts on different sites that I've looked at the problem eventually goes away and I'm none the wiser. One guy said that he found that if he went to bed the problem was resolved when he came back!
My PDA running Windows 2003 2nd Edition is directly connected to my PC via a USB port. I've rebooted my PC and done a soft reset on the PDA but it didn't help. I'm using ActiveSync 4.1.
backup database web to disk = 'c:inetpubwwwrootackupmybakup.bak' with format
I m Getting Error like :
Server: Msg 3201, Level 16, State 1, Line 1 Cannot open backup device 'c:inetpubwwwrootackupmybakup.bak'. Device error or device off-line. See the SQL Server error log for more details. Server: Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally.
This error is Generated only when i m trying to access folders within "wwwroot" but not in any other folders , even command runs success fully for "wwwroot" folder . !!
Is it possible in SQL Server to have replication happen immediately aschanges are made. That is, a change is made on server A, and that change isautomatically applied to server B, rather than the replication happening atset intervals?Thanks.
Hello guys! I went through the bunch of documentation pages about configuring the one for providing web subscriptions. The most usefull for me were:
Security Architecture for Web Synchronization How to: Configure an IIS Server for Web Synchronization (SQL Management Studio) How to: Configure a Subscription to Use Web Synchronization Topologies for Web Synchronization Securing the Publisher Subscribing to Publications Mssubscription_properties (Transact-SQL) How to: Configure a Subscription to Use Web Synchronization (Replication Transact-SQL) sp_addmergepullsubscription (Transact-SQL) Web Synchronization for Merge Replication But I still have questions about it. If you could help me with small notes about subscribing to web based merge publication I would really appreciate that!
I believe, I just need a bit of clarification. The target setting is simple as it is: a client should connect to the our hosting server via HTTPS and update his pull subscription on demand by running .NET application. Avoiding the server part, are the following steps correct and enough to accomplish what I need? 1. To create subscription, should I use the script:-- This script uses sqlcmd scripting variables. They are in the form-- $(MyVariable). For information about how to use scripting variables -- on the command line and in SQL Server Management Studio, see the -- "Executing Replication Scripts" section in the topic-- "Programming Replication Using System Stored Procedures". -- Publication must support anonymous Subscribers.-- Execute this batch at the Subscriber.DECLARE @publication AS sysname;DECLARE @publisher AS sysname;DECLARE @publicationDB AS sysname;DECLARE @websyncurl AS sysname;DECLARE @security_mode AS int;DECLARE @login AS sysname;DECLARE @password AS nvarchar(512);SET @publication = N'AdvWorksSalesOrdersMergeWebSync';SET @publisher = $(PubServer);SET @publicationDB = N'AdventureWorks';SET @websyncurl = 'https://' + $(WebServer) + '/WebSync';SET @security_mode = 0; -- Basic Authentication for IISSET @login = $(Login);SET @password = $(Password); -- At the subscription database, create a pull subscription -- to a merge publication.USE [AdventureWorksReplica]EXEC sp_addmergepullsubscription @publisher = @publisher, @publication = @publication, @publisher_db = @publicationDB, @subscriber_type = N'anonymous'; -- Add an agent job to synchronize the pull subscription. EXEC sp_addmergepullsubscription_agent @publisher = @publisher, @publisher_db = @publicationDB, @publication = @publication, @distributor = @publisher, @job_login = @login, @job_password = @password, @use_web_sync = 1, @internet_security_mode = @security_mode, @internet_url = @websyncurl, @internet_login = @login, @internet_password = @password;GO
What is @job_login parameter here? As I understand, I will not run SQL Agent job as our client uses MS SQL Server 2005 Express. And I do not want to run it as we will provide a client with .NET application for performing synchrinization on demand Parameter @publisher means full SQL Server instance name? For example, MYMACHINEMSSQLSERVER? What user will be used to connect to the distributor and to the Publisher? Will it be @internet_login? Do I need running sp_addmergepullsubscription_agent if I€™m going to use the following .NET application code to synchronize over the Internet?
2. To perform synchronization with our hosting servers, should a client use the following code? // Define the server, publication, and database names. string subscriberName = instanceName; string publisherName = instanceName; string publicationName = "AdvWorksSalesOrdersMerge"; string subscriptionDbName = "AdventureWorksReplica"; string publicationDbName = "AdventureWorks"; string hostname = "adventure-works\garrett1"; string webSyncUrl = "https://" + instanceName + "/SalesOrders/replisapi.dll";
// Create a connection to the Subscriber. ServerConnection conn = new ServerConnection(subscriberName);
// If the pull subscription exists, then start the synchronization. if (subscription.LoadProperties()) { // Get the agent for the subscription. agent = subscription.SynchronizationAgent;
// Check that we have enough metadata to start the agent. if (agent.PublisherSecurityMode == null) { // Set the required properties that could not be returned // from the MSsubscription_properties table. agent.PublisherSecurityMode = SecurityMode.Integrated; agent.DistributorSecurityMode = SecurityMode.Integrated; agent.HostName = hostname;
// Set optional Web synchronization properties. agent.UseWebSynchronization = true; agent.InternetUrl = webSyncUrl; agent.InternetSecurityMode = SecurityMode.Standard; agent.InternetLogin = winLogin; agent.InternetPassword = winPassword; } // Enable agent output to the console. agent.OutputVerboseLevel = 1; agent.Output = "";
// Synchronously start the Merge Agent for the subscription. agent.Synchronize(); } else { // Do something here if the pull subscription does not exist. throw new ApplicationException(String.Format( "A subscription to '{0}' does not exist on {1}", publicationName, subscriberName)); } } catch (Exception ex) { // Implement appropriate error handling here. throw new ApplicationException("The subscription could not be " + "synchronized. Verify that the subscription has " + "been defined correctly.", ex); } finally { conn.Disconnect(); }
What does HostName parameter use for? What is that host? Subscriber€™s machine name instance name? Or Publisher€™s? Or Distributor€™s? Why parameters PublisherSecurityMode and DistributorSecurityMode have value SecurityMode.Integrated? How it is used in the replication model for web synchronization? As I understood from the documentation, client (Merge Agent) should connect to the Subscriber, get the changes, connect to hosting server via HTTPS and perform transactions through the SQL Server Replication Listener, thus the internet login to the IIS should be used to perform all the operations on hosting server side? The same internet login will be used to get snapshot from UNC share? If I€™ve chosen to use FTP to provide with snapshots, should I then provide ftp parameters to sp_addmergepullsubscription_agent stored procedure or they will be used automatically by ISAPI filters on hosting server? Thank you.
I'm having to build a smart client application that works with an offline sql express database on the client. And works with several types of databases - that may be configured to work as a central server. The list includes the sql server family including 2000, 2005 and express and also IBM DB2.
I have to address some rather complex merge replication scenarios. By Merge I mean the ability to deal with handling and synchronization of bi-directional changes. More specifically changes to data may be made to any database in the system. either local or central.
I'm afraid, I cannot use sql replication in a heterogenous mode for several reasons. Some being - the database is being auto attached on the client, the application is expected to live up to the 'low impact' install ideology and should not need any major configuration etc.....
Are there any recomended approaches/Ideas one may use to acheive replication. Any links/information/ideas will be great.
I'm developing a Windows Mobile solution (Pocket PC Win 5-6). I have Sql Server CE on the device and I use RDA (Remote Data Access) to pull the tables to the Sql Server CE. I use RdaTrackOption.TrackingOffWithIndexes so I can Alter the table design. Then I use the function SubmitSql to push back my changes. On the server I have a table for example Customers, it has a column called CustomerId with identity(1,1). Let's say the highest Id is 100 and the lowest is 50, when I add a record in the Sql Server CE it gets CustomerId = 1 then next one 2 etc which means identity is working but does not continue after 100. I don't need to use the CustomerId when I push my data back because I have sql syntax taking care of @@IDENTITY when inserting new records. Anyone who knows why it begins on 1 and not 101?
Hello, i'm trying to make a c# web form in vs.net 2003 and a connection to a sql database.I'm using a sqlconnection control and a sqlcommand, but when i test it, the page said: "There was an error in login Encuesta, with the user LOCALHOST/ASPNET" (or something like that)i don't have the source code, and i am not in my pc, so, can somebody help me?
I've installed SQL Compact Edition (with Visual Studio 2005) and i try to compile Northwindoledb for the DeskTop. The goal of this operation is to migrate a Pocket PC application (C++/OLE DB/SQL Mobile) to the desktop. If i can run properly Northwindoledb project i could start to migrate ...
I have 2 distributed databases which need to be synchronized at regular basis. I plan to write a service that does it. But I'm just curious how to do the update incrementally. Thanks
I have a website that is running on the intranet and on a notebook. I have a notebook that is not always connected to the internet.. when it is not connected, it will read in some data from a barcode reader; and when it's connected, it will synchronize the data with the db on the intranet.. any idea on how to achieve this?
Hi All, I want to do SQL Server DB Synchronization. The scenario is like this.. DataBase is presently on SQL Server 2005, When end user click on button or on some action event all data to from the server should be fetched & should be saved on client m/c. Afer that when user click on button or on some event, DB should be synchronized. i.e. if there is any change in Server Data, client data should be automatically update... How can i achieve this using C#.. Plzzz Help!!!!!
Once I go to client site (where production DB is located) I want to generate a DataSet (which I am able to do) and compare this DataSet with the DataSet created when I read in the XML file.
## 'read in XML file XMLDataSet.ReadXML(Server.MapPath("xDataDictionary.xml")) ##
There is a unique identifier in the table and XML file called 'FieldID'. What I need to do is compare DataSet (from Production DB) with DataSet from XML file.
If there is a 'FieldID' in the DataSet from XML file that IS NOT in DataSet from ProductionDB I want to insert this node/row into the database.
I was wondering if anyone knows if there is any software available or if it is easy to implement the following.
We are running a SQL Server database locally in house and also have the exact same database running on a hosted webserver. The data changes on both versions of the database daily and what we need to do is figure out a simple easy / cheap way to have the two databases synchronize with each other every evening so that the data is exactly the same every morning.
i want to create a web application architechture which having multiple web server. this all web server having sql server database. now what i want is to Keep all data in all databases. means i want to synchronize my all sql servers. it is also possible ki my one webserver can have multiple clusted database. hope to hear soon from u people.
Hi Guys: I am trying to synchronize my home sqlserver database with a database on my web hosting company. I am not really sure how to go about it, any suggestions?
Hi, I want to know the a solution for my Synchronization Scenario
I have a several client databses which are SQL Server 2005 Express and i have a master database which is SQL Server 2000 containing all the individual Client databases. All the individual client databases are kept seperately at the master location. I need to Synchronization the client database with its copy at the master database (something like Merge replication). Both the Client Copy as well as Master Copy could be Publishers & Subscribers.
Now the problem is Because of security & firewall issues, only the Client should have the ability to schedule & initiate the synchronization process with the master copy. Unfortunately SQL Server 2005 Express has only subscriber agent and not a publisher agent.
Any help on how to achieve this would be appreciated . Thank You
I have one Informix in OpenVMS machine and another MS SQL in Windows machine. I cannot touch the Informix server at all.
How to do the extract/update the data periodically from Informix to SQL? I was thinking of writing a service. But I don't know how to do the incremental update.
I was thinking of doing a pull subscription from MS SQL. Is it feasible? Is it a replication or integration service? I dunno. Please help.
Say: Server "A" contains "123" database. Server "B" contains "123" database.
But changes is made on Server A. I want that every changes made on server A "123" database should reflect on Server B "123" database on real time basis.
whether it is done through replication or any other way, i want practical answer so that i can try it out.
I am using SQL Server 2000 Ent/Dev edition. I have a 2 identical database in 2 different servers and i'm required to synchronize both database. However, i need to exclude some of the tables in during this process.
I tried to search this forum but can't find any solution. How can i perform the above task? BTW i can't use trigger as i have more than 100 tables.
Hi, I am having this scenario; I have 2 SQL Server 2005 databases, they are basically identical in structure. The first one is used by my web application and the other is by desktop application ... I created the web db for security reasons ,.. i dont want online users to have access to the major backed (desktop db) directly so the web app is writing to the web db ... now, I want to update my major backend as transactions to the web db happen and vice versa to keep the users of both applicaions happy ... Is there a good design patterns for that? I am thinking of using db triggers for this but not sure on the implementation details ... Your help is much appreciated
I'm currently working on a Database Synchronization project and I wondered if anyone had any ideas other than what I've got so far. The context of this project is that we have a central server which stores data for 10 different locations in a consolidated database. Each store contains a replica of that database, but with only data relevant to their own operation.
Store owners can change data at the store level or chain owners can change data at the central level, but for either change the other level has to be notified. I currently have 2 ideas on how to do this.
Note: I am using MS SQL Server 7 for the central, but MSDE (Desktop Edition) for the store levels. So, replication from the store to the server is not available. I think I need to actually code this on my own.
