Last post Apr 20, 2010 10:36 AM by atconway
Apr 18, 2010 06:38 AM|aspfun|LINK
I have an off-line project. User uses a laptop to input data off line into local SQL server express. Once connected to domain (on line), local data will sync with remote SQL server using sync framework. (It is done, works fine)
One job left:
Since another user used another program to update some tables in remote SQL server, such as CUSTOMER, SHIPINFO, my off-line program need to update these new data once connect to domain.
Here is what I design in Pseudo code: (take CUSTOMER as example)
1) In remote SQL server, create a store procedure (sp_customer) to select all CUSTOMERS
2) In application, create a dataset (dst_customer) based on sp_customer
3) Insert all customers data which is in remote SQL server but not in local SQL server based on CUSTOMER_ID
I do not think this a smart way to do it.
Can someone give me an idea of how to do it in an efficient way?
Apr 18, 2010 09:46 AM|nisarkhan|LINK
have you thought about replication?
Apr 19, 2010 08:58 AM|aspfun|LINK
Thare are about 15 users using off line program. Is it possible to create replication for each of them?
Apr 19, 2010 02:00 PM|atconway|LINK
I think you have the proper tool (Microsoft Sync Framework) but you may be trying to do something manual that is already accounted for and built into the framework: Change Tracking. This is especially true in SQL Server 2008 with "SQL Server 2008 Change
Tracking". Take a look to the following link that details how the sync operations take place in the framework and specifically take note of your question about syncing with changed data in the section labeled "Change Tracking":
Introduction to Sync Framework Database Synchronization:
Apr 19, 2010 04:48 PM|aspfun|LINK
Yes, I used sync framework for local data to upload to remote.
For remote server my project can not use sync framework because another department already use another application to input data.
My question is:
How to download new data from remote to local?
Apr 20, 2010 10:36 AM|atconway|LINK
I am not sure I completely understand; it seems like the questions are intermixed. The Sync Framework will sync both ways: remote to local
and local to remote.
Now if you are trying to state that one of your remote clients can not use the sync framework because the application can not be modified, then you are going to manually need to do what the framework does for you. And this can be tricky. There is a lot
of change tracking and updating that has to be monitored and accounted for in order to do this successfully. What happens if the data has changed since the last time you populated your DataSet and you are out of sync? My reccomendation is to figure out if
it is at all possible to open that problematic app up and inject the Sync Framework to make the problem to be solved easier.
If this is not an option, you can look at populating an object or DataSet as you mentioned with all of the current data to be persisted to the remote client.