[SP1 RTM] Differences between documentation and implementation. RRS feed

  • Question

  • Hi,


    I've been recently developing solution using MS Sync stuff. Unfortunately I had a problem to get work sync with SQL 2008 when using "date" type.


    Documentation states:

    -- doc ---------

    Mappings Between SQL Server 2008 and SQL Server Compact 3.5
    SQL Server 2008 data type  SQL Server Compact 3.5 SP 1 data type 
     nchar(10) value of the form 'YYYY-MM-DD' 1



    -- dll ---------

    DLL contains following code : Microsoft.Synchronization.Data.SqlServerCe, Version=
    public SqlCeDatatypeMapping()
        this._mapExt2SqlCe = new Hashtable(SqlCeSyncUtil.EqualityComparer);
        this._mapExt2SqlCe.Add("decimal", "numeric");
        this._mapExt2SqlCe.Add("smallmoney", "money");
        this._mapExt2SqlCe.Add("smalldatetime", "datetime");
        this._mapExt2SqlCe.Add("datetimeoffset", "nchar(34)");
        this._mapExt2SqlCe.Add("datetime2", "nchar(27)");
        this._mapExt2SqlCe.Add("date", "nchar(27)");
        this._mapExt2SqlCe.Add("time", "nchar(16)");
        this._mapExt2SqlCe.Add("char", "nchar");
        this._mapExt2SqlCe.Add("varchar", "nvarchar");
        this._mapExt2SqlCe.Add("text", "ntext");
        this._mapExt2SqlCe.Add("sql_variant", "ntext");
        this._mapExt2SqlCe.Add("xml", "ntext");
        this._mapExt2SqlCe.Add("guid", "uniqueidentifier");
        this._mapExt2SqlCe.Add("uuid", "uniqueidentifier");
        this._mapExt2SqlCe.Add("currency", "money");
        this._mapExt2SqlCe.Add("varchar2", "nvarchar");
        this._mapExt2SqlCe.Add("nvarchar2", "nvarchar");
        this._mapExt2SqlCe.Add("number", "numeric");
        this._mapExt2SqlCe.Add("clob", "ntext");
        this._mapExt2SqlCe.Add("nclob", "ntext");
        this._mapExt2SqlCe.Add("blob", "image");
        this._mapExt2SqlCe.Add("long", "ntext");
        this._mapExt2SqlCe.Add("long raw", "image");


    Because having 10 chars for date type make really sense, so I suspect that there is bug in implementation.

    But maybe I'm missing something...


    Of course using reflection on proper sync type and doing a few hacks can sole this bug, but It could break something later.


    Please someone from MS look at it and let me know where bug lies (doc OR dll)?



    Wojciech Gebczyk

    • Moved by Max Wang_1983 Friday, April 22, 2011 6:04 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Thursday, August 21, 2008 8:26 PM

All replies

  • The dll is right. We'll look into to fix the bug in the Doc.





    Thursday, August 21, 2008 11:53 PM
  • It's really surprise!


    1. I would expect that less characters is enough. Could you elaborate a bit why smaller precision type requires same storage space as 100-ns type (if I remember datetime2 has such precision)?


    2. If it takes so much space then probably value moved from SQL 2008 to CE 3.5 will have zeroes in time part (is it true?) What would happen if there will be non zero time part?


    3. How SyncFramework behaves if it moves value from CE to SQL? Truncate rest, is doing some checks?


    4. If there is waste of space (as I know now, maybe there are explanation to it), why not use datetime type when date is mapped? In both cases there is waste of space, but logically (CE)datetime is closer to (SQL)date than (CE)nchar(27) to (SQL)date.



    Wojciech Gebczyk

    Friday, August 22, 2008 9:46 AM
  • Hi Wojciech-


    The reason that date maps to nchar(27) is due to how 'date' is represented in ADO.NET Datatypes.  Columns of type 'date' map to System.DateTime in ADO.NET (with a time of all zeros), so when we receive a DataSet that contains a System.DateTime column, there is no way to tell if this was originally a 'date' column or a 'datetime' column on the server. 




    Friday, August 22, 2008 4:44 PM
  • Looking through code it seems that there is no conversion between DataSet .NET type and SQL Ce column definition. They are using ExtendedProperties (DataTypeName property) - probably that's why there is SqlCeDatatypeMapping type with mapping definitions. Additionally I've checked what is in SyncSchema and in ProviderDataType is "date" also underlying DataSet has DataTypeName="date".


    So there are at least 4 signs that they have enough information to distinct date from datetime. There must be something else.


    Still waiting for comment on this problem



    Wojciech Gebczyk

    Monday, August 25, 2008 9:12 AM
  • Wojciech,

    I could not find where on the DataSet there is a DataTypeName of 'date' for the date column.  Could you please clarify?  It is true that the SyncSchema contains all the information, however SyncSchema is only available on initial sync when the schema is needed on the client.

    One thing you could try is alter your SelectIncrementalInsertsCommand on the server side SyncAdapter to CAST your 'date' column to either nchar(10) or datetime and override the default behavior.  For example:

    select CAST(date_col as NCHAR(10)) as date_col from <table>

    This would impact the schema that is created on the client.

    Monday, August 25, 2008 4:28 PM