I'm a young developer working on a project that involves merge replication between SQL Server 2005 Mobile Edition and SQL Server 2005 and I've been having a hard time wrapping my head around exactly how to implement the best filters for the subscription(s) and publication(s).
Users of our application are Auction goers who collect data about AuctionItems in a mobile db and sync that information with a central db once he or she is finished. The central db is, obviously, pre-populated with most of the information about any/all AuctionItems. Central db information is available to view/change via web access.
Multiple users can change the same Auction data so there will be overlapping partitions.
What I would like to do is:
Present the user with a list of all available Auctions for the next 2 weeks (select * from Auctions where blah blah blah). This is a simple publication to create. Now, I'd like to be able allow the user to select AuctionID 4, AuctionID 3, AuctionID 12 and then, via a seperate AuctionListing publication only download AuctionItems that apply to those IDs.
Obviously, the publication has to be created and include the AuctionItem table and all necessary related tables/columns. But how do I create a parameterized filter based on that AuctionID?
Can a subscription be "dynamically created" once those AuctionIDs are known? Obviously, I don't want to bog my mobile devices down with tens of thousands of auction listings to auctions the user is not planning to attend.
Basically, my question is: am I wasting my time or is there some clever manipulation of HOST_NAME(), SUSER_NAME(), both, or some other method that I've missed that can get only those Auctions where the the ID matches one selected by the user at runtime?
You can do it with either one publication or 2 publications.
With one publication, I can think of dynamically populating a table with the IDs from what the user chooses. The filter will be based on IDs present in this table. However note that populating the table and synchronization need to be in 2 different transactions because if they are in the same one, enumerations will be looking at not correct data.
Let me know if you have trouble setting the filter.
|||Could you elaborate a little more on some of the parameters/steps involved involved with the filtering process?How would we do it, exactly, in one publication? Do I need to use a
mix of RDA and merge replication (or is that even possible)?
In my head I think I know how it needs to work but, as a SQL Server beginner (just started using it this year), I don't know if I can describe the problem and solution eloquently with the available tools.|||Mahesh:
I've decided to go with one publication. How can I separate the population of the database dynamically from the sync call that I will need to make to pull the results down? How do split this action into separate transactions.|||One more thing to consider: I can see the feasibility of using two publications to accomplish this tasks and in some ways it might be easier. Nevertheless, consider this scenario.
We use a table called 'AuctionDownload' that is populated with the AuctionIDs and some string value that we can filter on using HOST_NAME(). This table will be included in the publication along with the main 'Auction' table.
User A subscribes, calls sync, gets a list of auctions and chooses IDs 1 and 2 that he wants listings for. He populates the rows in 'AuctionDownload' on his mobile device and syncs. That's fine.
However, imagine User B is also using the system at the same time as User A. The first time he calls sync to get the list of available auctions, his EMPTY AuctionDownload table will need to be merged with the central AuctionDownload table. Thus User A's values are erased before he or she could call sync on a different publication to get the actual listings!
Is there a work around or am I imagining this syncing scenario incorrectly.
No comments:
Post a Comment