locked
Provisioning the Server DB with FK relationships for sync RRS feed

  • Question

  • Hi  Everybody!

    I am really wonder in Provisioning server db(MS SQL).

    I want to define the scope on server DB to sync MS SQL server and MS Sql Express.

    1.

    I have a trouble for provisioning. The main point is FK constraints.

    We have a db schema on server, which has lots of FK constraints.

    When provisioning the server db at the server side, I used the DbSyncCreationOption as a Skip.

    So though I define the scope with FK constraints, I can't apply the FK constraints(After applying the server config and when getting the

    scope description to apply to client using SqlSyncDescriptionBuilder.GetDescriptionFoprScope(... ...), I can't see any FK constraints.

    So Client DB(Express) doesn't have any FK constraints.

    So I tried to provision the client db and alter all tables on client db to add FK constraints. In this case, sync Process doesn't work, that's sync items are failed to download. If removed the Alter Table function on client side, then sync works well.

    what is the problem?

     

    2.

    My issue is occurred in Filtering. I could apply the FK constraints on client adding each FK constraint manually in definition for the client scope description.

    e.g. clientScopeDesc.Tables["XXX"].Constraints.Add(new ... ...);

    In this case, FK constraints are installed on client as the same as server db, but data filtering doesn't work.

    I defined the filtering on server side.

    If I remove the codes for applying the FK constraints to client db, then filtering works well.

    What is the problem?

     

    I really don't know how to provision the server db for FK constraints and filtering. I studied sync section in MSDN several times and followed them, but I got failed.

     

    Please give me your answers for 2 issues and my trouble.

    Sync Fx is very good, but it doesn't work well according to my intention.

    Please help me. Thanks for your attention!

    Best Regards

     

     

     

    Thursday, January 20, 2011 4:06 AM

Answers

  • try applying a filter on your UserDetail table like this: UserId IN (Select UserId from UserTable where UserName Like "a%")

    • Marked as answer by Leonardo Lee Thursday, January 20, 2011 2:12 PM
    Thursday, January 20, 2011 2:06 PM

All replies

  • Hi, everybody!

    I have found the reason for issue 2.

    The reason was :

    User table and UserDetail table are existed on server, UserDetail has a FK relationship with UserTable.(Users table is parent.)

    I have applied a filter for UserName field in User table so that client can get the users information in which UserName starts with 'a' only.

     

    FilterClause is [UserName Like "a%"]

     

    During sync process, User information is filtered correctly. But there is an error during downloading the UserDetail information,

    because some UserDetail information has a UserId which is not existed in User table on client.

    This is occurred because I applied the filter for UserName.

    How can I solve this issue?

    Looking forward to your solution.

    Thanks

    Best Regards

     

     

    Thursday, January 20, 2011 2:00 PM
  • try applying a filter on your UserDetail table like this: UserId IN (Select UserId from UserTable where UserName Like "a%")

    • Marked as answer by Leonardo Lee Thursday, January 20, 2011 2:12 PM
    Thursday, January 20, 2011 2:06 PM
  • Hi, JuneT Thanks for your reply. Isn't there an other way for this? Regards
    Thursday, January 20, 2011 2:14 PM