locked
2 Bugs of Synchronization Services for ADO.NET RRS feed

  • General discussion

  • I'm recently using the Sync Services to sync data from SQL Server to SQL CE, but got two bugs here. Just let you know and fix them.

    The first bug is the default value of the columns will not be set in the tables created in CE DB.

    The 2nd bug is that the table which contains columns with name of "??sys...." will not be successfully synchronized. By looking into the Microsoft.Synchronization.Data.SqlServerCe.dll, I have already located the problem.

    In the SqlCeTableColumns method of SqlCeSyncUtil class, there is one sql to select all column of a table, which is
    SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,  COLUMN_DEFAULT, NUMERIC_PRECISION, NUMERIC_SCALE, ORDINAL_POSITION 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE
     COLUMN_NAME NOT LIKE '__sys%'  AND TABLE_NAME = 'tableName' ORDER BY ORDINAL_POSITION

    COLUMN_NAME NOT LIKE '__sys%' is the problem source. It intends to forbid the sys columns created Synchronization services, but also forbid all other normal column which like '__sys%'  (for example "IsSystem", "tbSystem").
    There is one way to reduce the confliction is to use "escape '_'" which tells the SQL executor '_' represents just underscore itself.
    update the SQL Clause as shown bellow:
    COLUMN_NAME NOT LIKE '__sys%' escape '_' 





    • Moved by Hengzhe Li Friday, April 22, 2011 3:14 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Thursday, May 21, 2009 6:25 AM

All replies


  • Thanks for pointing out the second bug- I have 2 projects that are affected by this! However, your suggested fix does not work.

    This I think is more correct:

    SELECT

     

    COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLUMN_DEFAULT, NUMERIC_PRECISION, NUMERIC_SCALE, ORDINAL_POSITION

    FROM

     

    INFORMATION_SCHEMA.COLUMNS

    WHERE

     

    SUBSTRING(COLUMN_NAME, 1,5) <> '__sys' 

    SUBSTRING(COLUMN_NAME, 1,5) <> '__sys' 

     




     


    Erik Ejlskov Jensen, MCTS: WM App, MCITP: SQL 2008 Dev - http://erikej.blogspot.com Please mark as answer, if this was it.

    • Edited by ErikEJMVP Thursday, May 21, 2009 1:22 PM
    Thursday, May 21, 2009 12:40 PM