Solved

Microsoft Sync Framework Filtering and Multiple Tables

Posted on 2013-05-25
8
1,308 Views
Last Modified: 2013-06-04
I need to wirte an application to sync data from an SQL Server 2008 and an SQL Server Compact 3.5 database.  The application will be written in VS VB 2008.  I need to use parameterized filtering so only the data the mobile user needs will be synced.  My problem is that I'm having a hard time wrapping my head around how to determine what tables will be added to each scope and how to design the scopes.  What's giving me the most trouble is how to do a paramterized filter for a table that doesn't have the data that will determine what will be filtered.  For example: our customer database is designed only with an identifier that joins it back to an address table for address information and the address table joins back to a zip code table to get the zip code information which contains the city and state, so for me to filter a customer by state I have to join it to 2 other tables.  How do I design the scopes for this situation?  Thanks for any help.
0
Comment
Question by:rcblevins
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 10

Expert Comment

by:Matt Bowler
ID: 39198317
I think that this is just a compromise that you make when you build a highly normalised OLTP system.

I suppose that you always have the option of de-normalising any of the filter fields that are commonly used and potentially giving performance issues due to the joins? Personally I wouldn't the join in your example doesn't sound overly complex or resource intensive.
0
 

Author Comment

by:rcblevins
ID: 39198367
Ok.  Thanks.  Are you saying I need to modify the db to have all of the fields I need to do the scope with in one table? Or is there a way to do the scope by joining the tables needed or is there a way to use a view?  Thanks for your help.
0
 
LVL 10

Expert Comment

by:Matt Bowler
ID: 39198499
Can you clarify what you mean by scope?
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:rcblevins
ID: 39198516
I'm referring to where you define a scope or template that you add tables to for Microsoft Sync Framework.  At least by what I read (I am not completely familiar with it yet) after you have set up the scope or template and provision it using the sync framework you can make it available for syncronization with the client db.  What I need clarification on is when I set up this scope and want to filter it how do I do that when I'll need to do table joins to get the filtering data I need. Thanks for your help.
0
 
LVL 10

Expert Comment

by:Matt Bowler
ID: 39198523
Okay - definitely use views :)
0
 

Author Comment

by:rcblevins
ID: 39199888
Ok.  Thanks.  I don't see any where it says you can use views with Microsoft Sync Framework.  Can you use views and can you give me an example of how to use it?  Thanks again.
0
 
LVL 10

Accepted Solution

by:
Matt Bowler earned 500 total points
ID: 39199893
Essentially you can follow the steps outlined here:

http://msdn.microsoft.com/en-us/library/dd918682(SQL.105).aspx

But at step 2 identify the tables to synchronise you use a view that has all the joins that you need. Views are table valued objects so will work the same way as a table in this context.
0
 

Author Comment

by:rcblevins
ID: 39218750
Thanks.  I think that will work.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question