locked
Filers doesn't work with Inner Join RRS feed

  • Question

  • Hi due to my database structure I must use FilterClause with Inner Joins but It doesnt work at all. I'm getting next exception: 

    {"Cannot initialize the client database because the schema for table 'picture' could not be retrieved by the GetSchema() method of DbServerSyncProvider. Ensure that you can establish a connection to the client database, and that either the SelectIncrementalInsertsCommand property or the SelectIncrementalUpdatesCommand property of the SyncAdapter is specified correctly."}

    I use SQL 2008 server change tracking and other tables are working fine. 

    here's sample code I use: 

       SqlSyncAdapterBuilder newsBuilder = new SqlSyncAdapterBuilder(serverConn);

            newsBuilder.TableName = "dbo.picture";
            newsBuilder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;
            newsBuilder.SyncDirection = SyncDirection.Bidirectional;

            SqlParameter filterParameter = new SqlParameter("@Institution_ID", SqlDbType.UniqueIdentifier);

            string newsFilterClause = " department_id IN (select Collection_ID from picture inner join department on department.ID = collection.Department_ID where Institution_ID=@Institution_ID)";
            
            newsBuilder.FilterClause = newsFilterClause;
            newsBuilder.FilterParameters.Add(filterParameter);

            SyncAdapter newsSyncAdapter = newsBuilder.ToSyncAdapter();
            newsSyncAdapter.TableName = "picture";

            this.SyncAdapters.Add(newsSyncAdapter);

    C# developer
    • Moved by Max Wang_1983 Thursday, April 21, 2011 11:17 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Wednesday, August 19, 2009 11:47 PM

Answers

  • Use SQL Server Profiler, you will find out the reason.

    The problem here is failing to create schema. When local schema is not created, Sync Services will try to execute SelectIncrementalInsertsCommand/SelectIncrementalUpdatesCommand to get record(s) then get schema from the result. At that time, The value of SqlParameters may not available. Try following filter:

    string newsFilterClause = "@sync_client_id_binary IS NULL OR department_id IN (select Collection_ID from picture inner join department on department.ID = collection.Department_ID where Institution_ID=@Institution_ID)";

    When @sync_client_id_binary IS NULL, SelectIncrementalInsertsCommand is being run got getting schema, you can return only one record like: Select Top 1 * dbo.picture.
    Monday, August 31, 2009 2:40 AM

All replies

  • Hi,

    Can you confirm that the syntax of your filter clause is correct. Just prepend "select * from picture where " to it and try to execute it against your database. Did you mean to have picture.Department_ID instead of collection.Department_ID in your filterclause above?

    thanks
    Sudarshan
    Development Lead , Microsoft
    Thursday, August 20, 2009 10:52 PM
    Moderator
  • Use SQL Server Profiler, you will find out the reason.

    The problem here is failing to create schema. When local schema is not created, Sync Services will try to execute SelectIncrementalInsertsCommand/SelectIncrementalUpdatesCommand to get record(s) then get schema from the result. At that time, The value of SqlParameters may not available. Try following filter:

    string newsFilterClause = "@sync_client_id_binary IS NULL OR department_id IN (select Collection_ID from picture inner join department on department.ID = collection.Department_ID where Institution_ID=@Institution_ID)";

    When @sync_client_id_binary IS NULL, SelectIncrementalInsertsCommand is being run got getting schema, you can return only one record like: Select Top 1 * dbo.picture.
    Monday, August 31, 2009 2:40 AM
  • yes it worked fine,seems I found temporary solution, but I need to look deeper into Bob H.L. solution
    C# developer
    Sunday, September 27, 2009 8:54 AM
  • I will try it, for now I did a trick but it works not so fast. 

    I sync my DB with another empty DB(a copy of my real one but without data). so I ship it with my application inside installation package.

    however it works a bit long than expected, when I look at my SyncStatistics log file, I get strange results: 


    8:44 PM | Client ID: 0a6bf116-6541-4881-9719-29ff93865a48
    Changes applied to client for group Institution
    Inserts applied to client for group: 1
    Updates applied to client for group: 0
    Deletes applied to client for group: 351


    so I it's really filters by insitution, but why it says deleted 351 I have no idea, still I have in DB only 27-28 Institutions. and same with other tables, so it detects so many deletes on first SYNC with my EMPTY DB and takes much more time than expected.

    C# developer
    Sunday, September 27, 2009 8:58 AM
  • by the way, didnt get when should I execute this:

    When @sync_client_id_binary IS NULL, SelectIncrementalInsertsCommand is being run got getting schema, you can return only one record like: Select Top 1 * dbo.picture.

    C# developer
    Sunday, September 27, 2009 9:01 AM