Sync service generate faulty insert query for some of the column names RRS feed

  • Question

  • Hi,

    I have been using Sync services for ADO.NET for around 8 months with custom providers. Everything was going good and syncing properly until we added a new table in SyncGroup.

    After adding this new table to the syncgroup, the syncing stopped throwing Sql exception. 

    The new table didn't have anything different from current set of tables. It was just another table with set of basic columns like varchar, decimal etc.

    After a through debugging using SQL profiler I came to know framework is generating a faulty insert statement for this table and trying to insert default value for 3 of the columns of table even though there value is available in dataset to be persisted and SQL is failing because those columns don't have default value in table definition. This is snippet of SQL generated from SyncFramework captured from profiler:

    declare @p93 int
    set @p93=NULL
    exec sp_executesql N'INSERT INTO [abc] ([TaxTerm], [Original_Allocation_ID], [Original_Notional], [Original_Counterparty], [isChanged]) VALUES (@TaxTerm, @p1, @p4,  @p7, @isChanged) SET @sync_row_count = @@rowcount', N'@TaxTerm char(1), @p1 int, @p4 decimal(28,10), @p7 varchar(8000), @isChanged bit, @sync_row_count int output', @TaxTerm='', @p1=default, @p4=default, @p7=default, @isChanged=1, @sync_row_count=@p93 output
    select @p93

    This was kind of strange behavior from SyncFramework. Digging more into internals of SyncServices reveled that they use some prefixes for renaming column names in some situations. These prefixes can be find in class - ParameterNames that is part of DbSyncAdapterBuilder.cs. This is snippet of class - ParameterNames:

          private const string DefaultOriginalPrefix = "Original_";
          private const string DefaultIsNullPrefix = "IsNull_";
          private const string AlternativeOriginalPrefix = "original";
          private const string AlternativeIsNullPrefix = "isnull";
          private const string AlternativeOriginalPrefix2 = "ORIGINAL";
          private const string AlternativeIsNullPrefix2 = "ISNULL";
          private string _originalPrefix;
          private string _isNullPrefix;
          private Regex _parameterNameParser;
          private DbSyncAdapterBuilder _dbSyncAdapterBuilder;
          private string[] _baseParameterNames;
          private string[] _originalParameterNames;
          private string[] _nullParameterNames;
          private bool[] _isMutatedName;

    I had 3 columns in my table whose name had a prefix "Original_". Sync framework while generating insert query was putting default value for these 3 columns even though they don't have any default value in table definition forcing sql to throw exception and aborting synchronization. 

    Renaming these 3 columns to different names solved my problem but I didn't find any official documentation regarding naming of columns for syncing. So I just want to confirm what all things I need to take care while defining columns.

    I spend good amount of time debugging this issue so just for the record I wanted to document this finding.

    Monday, March 3, 2014 4:20 PM