locked
SelectIncrementalInsertsCommand - CommandText contains two different tables with an inner join. All rows are always downloaded RRS feed

  • Question

  • Trying to sync SQL Server 2005 and SQL Server Compact 3.5 (download only).

    I have download only synchronisation working correctly for most of the tables in my database, but there is one scenario I am struggling to handle.  I have two tables Product and ProductBarcode.  The product table has a bit field called Active.  I download all the products that are active - no problem.  I want to download all the product barcodes - but only where they relate to an active product.

    So to do this I am using the following sql in the commandtext:

    SELECT ProductBarcode.* FROM ProductBarcode INNER JOIN Product ON ProductBarcode.ProductCode = Product.ProductCode
    WHERE Product.Active=1 AND (ProductBarcode.SyncInsertTimestamp > @SYNC_LAST_RECEIVED_ANCHOR AND ProductBarcode.SyncInsertTimestamp <= @SYNC_NEW_RECEIVED_ANCHOR)"
    

    Everytime I sync all active barcodes are brought down, not just the incremental changes.

    When I check the __sysSyncArticles table in the client database, the ProductBarcode table does not appear.  (All my other sync tables do...) 

    I have tried both Microsoft Sync Framework v1 and v2, same thing.

    When I remove the join to the Product table it works as I would expect it to.

    Any suggestions?  Are joins supported?

    Wednesday, June 30, 2010 2:38 PM