locked
Multiple and different client for one sql compact database RRS feed

  • Question

  • Hi, all

     

    I have a problem with Sync Services and one particular scenario :

     

    For my solution I have a DbServerProvider (Sql Server 2008 with Change Tracking) for Remote Provider and a SqlCeClientSyncProvider for Local Provider.

     

    I have used Sync Designer to generate code and migrate all sql to stored procedures.

     

    I explain my configuration :

     

    On my client application, i must log with login and password, which identify my User.

    This login is used identify my User and to filter User's contacts entities on server.

    No need to synchronize all datas !

     

    So, I have

    1. Added a SyncParameter to my SyncAgent
    2. Modify syncadapter to add this parameter
    3. Modifiy my stored procedures for incremental insert, update, deletes with one more parameter @UserId

     

    I have something like that :

     

    SQL :

    Alter PROCEDURE [SyncContactSelectIncrementalInserts]

    @sync_initialized bit,

    @sync_client_id_binary varbinary,

    @sync_last_received_anchor bigint,

    @sync_new_received_anchor bigint,

    @UserId uniqueidentifier <--- My New Parameter

    AS

    IF @sync_initialized = 0

    BEGIN

    SELECT [ContactId], [UserId], [FullName], [Email]

    FROM Contact

    LEFT OUTER JOIN CHANGETABLE(CHANGES Contact, @sync_last_received_anchor) CT

    ON CT.[ContactId] = Contact.[ContactId]

    WHERE (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)

    AND UserId = @UserId

    END

    ELSE

    BEGIN

    SELECT Contact.[ContactId], [UserId], [FullName], [EmailHi]

    FROM Contact

    JOIN CHANGETABLE(CHANGES Contact, @sync_last_received_anchor) CT

    ON CT.[ContactId] = Contact.[ContactId]

    WHERE (CT.SYS_CHANGE_OPERATION = 'I' AND CT.SYS_CHANGE_CREATION_VERSION <= @sync_new_received_anchor

    AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary));

    AND UserId = @UserId

     

     

    IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'Contact')) > @sync_last_received_anchor

    RAISERROR ('SQL Server Change Tracking has cleaned up tracking information')

    END

     

    C# Code (for my agent)

     

    SyncAgent agent = new SyncAgent();

    // Set User Id

    agent.Configuration.SyncParameters.Add(new SyncParameter("@UserId", CurrentConnectedUserId));

     

    SyncStatistics stats = agent.Synchronize();

     

    (I have added this parameter in my SyncAdapter, no need to post code for that, i think Smile)

     

    The code works fine, but ...

     

    Behavior

     

    I have my First User " JOHN " connected on the machine.

    It's the first time launch, the Sql Ce Database is empty,

    My Db Provider make its job and get all User's contacts.

     

    In Sql Server Profiler, everything is good :

     

    exec [SyncContactSelectIncrementalInserts]

    @sync_initialized=0,

    @sync_last_received_anchor=0,

    @sync_new_received_anchor=354,

    @UserId='AF251ABC-2A78-4BFF-A546-346CEB30F82E'

     

    1. @sync_initialized = 0 . Yes it's the first time synchronization
    2. @sync_last_received_anchor  = 0 : Yes we never made any sync. before
    3. @sync_new_received_anchor=354 : Why not Smile
    4. @UserId='AF251ABC-2A78-4BFF-A546-346CEB30F82E' : This is John Smile

     

    Ok, now, next step, a second user " PAUL " log on application, on same machine, so same sql ce database etc ..)

     

    The application does a synchronization and we have :

     

    exec [SyncContactSelectIncrementalInserts]

    @sync_initialized=1,

    @sync_last_received_anchor=354,

    @sync_new_received_anchor=354,

    @UserId='31E56A5B-9B0B-4C8F-A512-7BC6FA722B0D' : This is Paul

     

    And we have a problem here.

     

    1. @sync_initialized=1 : Yes, it's not our first synchronization on this particular machine - sqlcedatabase, but it's PAUL's first synchronization
    2. @sync_last_received_anchor=354 -

      @sync_new_received_anchor=354 : argh, Paul will never retrieve his contacts ...

    Conclusion

     

    So the problem is : Can i have 2 User, distincts, on same machine - application - sql ce compact database ?

     

    One solution would be : "If a new user is coming, i delete all tables and recreate schema" , but i think this is not the most valuable solution ...

     

    Any suggestion ? Smile

     

    Sorry for my poor english, i'm french !

     

     

     

     

    • Moved by Hengzhe Li Friday, April 22, 2011 2:25 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Tuesday, September 9, 2008 9:03 PM

Answers

  • Hi Sebastien,

    The CE client sync provider is supposed to be a complete out of box solution for a compact database and hence its a "closed" implementation. There are lots of things that cant be done from outside a compact database, such as enabling change tracking, and so the ce client provider masks the user from all those kind of things. Extending the CE client sync provider is not a supported scenario and may not work for all scenarios.

     

    Friday, September 12, 2008 6:07 AM
    Moderator

All replies

  •  

    Hi Sebastien,

    Offline cache is table specific since the last_sent/last_received anchors is specific to a table (irrespective of what filter you used on the server for download). So the only way to get your scenario working is if you have different client databases for different users.

     

    Wednesday, September 10, 2008 8:26 PM
    Moderator
  • Thx for this response

    I have worked on this problem and your solution was one of my solutions Smile

    But i can't have more than one database.

    I Think there is solution to counter this behavior (damn, we have a database !) 

    I'm actually work on a new SqlCeClientSyncProvider which could resolve this problem. First results are good.
    This provider contains the UserId of my User

    I Think part of the solutions is to override :  
    • GetTableReceivedAnchor(string tableName),
    • SyncAnchor GetTableSentAnchor(string tableName)
    • void SetTableReceivedAnchor(string tableName, SyncAnchor anchor)
    • void SetTableSentAnchor(string tableName, SyncAnchor anchor)

    And manage my own table to store LastSent and Received Anchor, associate with my UserId (and replace "__sysSyncArticles")

    Unfortunately, sometimes, SqlCeClientSyncProvider doesnt call those methods and call internal methods (SetTableAnchorInternal and GetTableAnchorInternal)

    I need some helps, but it's a work in progress
    Wednesday, September 10, 2008 10:12 PM
  • Hi Sebastien,

    The CE client sync provider is supposed to be a complete out of box solution for a compact database and hence its a "closed" implementation. There are lots of things that cant be done from outside a compact database, such as enabling change tracking, and so the ce client provider masks the user from all those kind of things. Extending the CE client sync provider is not a supported scenario and may not work for all scenarios.

     

    Friday, September 12, 2008 6:07 AM
    Moderator
  •  

    Thx Maheshwar for the answer, we will make a solution with multiple databases (one per user)

     

    Friday, September 12, 2008 6:49 AM
  • I'm having a similar problem trying to do this. Is it possible to 'calculate' the sync anchors at run time and plug them into the client provider before doing a sync with a new filter?

    Say,

                _ClientProvider.SetTableReceivedAnchor("mytable", getMyTableReceivedAnchor());
                syncAgent.Synchronize();

    getMyTableReceivedAnchor would be a custom function to return the highest timestamp in mytable having the filter value "John" (or the new filter value) (I'm using timestamps to track changes so I'm guessing this value would be the same as the 'real' anchor value for the same filter).

    ..and then do the same thing with the other client provider values? I'm trying to figure a way of keeping old data on the device when requesting a new filter? (don't want to re-sync the same data over again when a client device goes back and forth between different filters)
    Tuesday, July 28, 2009 8:46 AM