locked
Sql Compact 3.5 syncronization fails RRS feed

  • Question

  • Hello

    I have an application that syncs between 2 database: the central one is hosted on a Sql Server 2008 and the local one is a Sql Compact 3.5 database.

    From time to time I need to upgrade the schema of one of the tables included in a sync scope and I do not want to deprovision and provision again, so I used this approach and for Sql Compact I have something like this:

    public void UpdateProvisionSchema(string connString, string scopeName, string newConfigData)
    {
        _log.Verbose("UpdateProvisionSchema: " + scopeName);
    
        try
        {
            string query = string.Format("SELECT [scope_config_id] FROM [scope_info] WHERE [sync_scope_name]='{0}'", scopeName);
            Guid config_id = (Guid)SqlCompactManager.ExecuteScalar(connString, query);
    
            string updateCommand = string.Format("UPDATE scope_config SET [config_data]='{0}' WHERE [config_id] ='{1}'", newConfigData, config_id);
            SqlCompactManager.ExecuteCommand(connString, updateCommand);
        }
        catch (Exception ex)
        {
            _log.Error(ex, string.Format("Failed to upgrade schema for scope {0}", scopeName));
            throw new Exception(string.Format("Update of provisioned schema failed for scope: {0}", scopeName));
        }
    }
     

    and I manually change the config scope for my table. I think the scope config I send is the correct one, because I generate it by provisioning an empty table.

    I have just added a column to a table included in a sync scope (its data is only downloaded from central database to local database) and now I receive an error on ApplyChangeFailed.

    It seems that in the Sql Compact database, the table included in the sync scope with the problem, has this column: __sysTrackingContext which does not exist on the central table.

    First time I have created the tables and provisioned them and then I added 2 new columns (BusinessType, HideCommentAndSerialNumber ). If I look at the DbApplyChangeFailedEventArgs and examine the columns, I see the following:

    Central:

    ..| BusinessType | HideCommentAndSerialNumber | sync_update_peer_timestamp | sync_update_peer_key | sync_create_peer_timestamp | sync_create_peer_key | 

    Local:

    ..| __sysTrackingContext | BusinessType | sync_update_peer_key | HideCommentAndSerialNumber | sync_update_peer_timestamp | sync_create_peer_key | sync_create_peer_timestamp |

    What am I doing wrong?


    sb_angela

    Tuesday, November 20, 2012 2:29 PM

Answers

  • I have found the problem. The script I used to add the latest column was something like this:

    ALTER TABLE [vcrStore]
    ADD [HideCommentAndSerialNumber] bit NULL
    GO
               
    UPDATE [vcrStore] SET HideCommentAndSerialNumber = 0
    GO

    and it got executed on both databases, but instead the update part should have been executed only on the central database and the data then downloaded to the client during synchronization.

    I have changes this and now it works.


    sb_angela

    • Marked as answer by sb_angela Wednesday, November 21, 2012 2:39 PM
    Wednesday, November 21, 2012 2:34 PM

All replies

  • SQLCe is provisioned a bit differently...

    check that in the SQL Ce side, when you provisioned, you didnt include the columns starting with _sysXXXXX

    Wednesday, November 21, 2012 5:20 AM
  • Hello

    I am not sure where t check this. I looked in the scope_config table and no _sysXX columns appear. This is the code I use to provision the sql ce database:

    public void ProvisionDatabase(string scopeName)
    {
        _log.Verbose("ProvisionDatabase: " + scopeName);
    
        try
        {
            var provider = new SqlCeSyncProvider();
            provider.ScopeName = scopeName;
            provider.Connection = _sqlCeConnection;
    
            //get the scope description and with tables to this scope
            DbSyncScopeDescription scopeDesc = PosScopeList[scopeName];
    
            var serverConfig = new SqlCeSyncScopeProvisioning((SqlCeConnection)provider.Connection);
    
            //determine if this scope already exists; if not go ahead and provision
            if (serverConfig.ScopeExists(scopeName) == false)
            {
                scopeDesc = GetScopeDescription(scopeName);
    
                //call after the tables have been added to the scope
                serverConfig.PopulateFromScopeDescription(scopeDesc);
    
                //indicate that the base table already exists and does not need to be created
                serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
    
                //provision the server
                serverConfig.Apply();
            }
        }
        catch (Exception ex)
        {
            _log.Error(ex, string.Format("Failed to provision database for scope {0}", scopeName));
            throw new Exception(string.Format("Failed to provision database for scope: {0}", scopeName));
        }
    }
    


    sb_angela

    Wednesday, November 21, 2012 7:12 AM
  • does this happen only on that specific table? the sql ce sync provider is supposed to be stripping out this column.

    would you mind posting the scopeDesc  and the corresponding scope config entry? and ill check if i can reproduce.

    also, what's the exact error you get in ApplyChangeFailed event?

    Wednesday, November 21, 2012 7:46 AM
  • It is not the first time when I used this approach to add/remove columns for tables already included in a sync scope and it worked before - I will test this once again.

    The scope with the problem contains 10 tables. This is the information from scope_config.

    <SqlCeSyncProviderScopeConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <Table Name="[vcrStore]" GlobalName="[vcrStore]" TrackingTable="[vcrStore_tracking]">
        <Col name="StoreNo" type="int" param="@P_1" pk="true" />
        <Col name="Name" type="nvarchar" size="80" param="@P_2" />
        <Col name="AddressLine" type="nvarchar" size="40" null="true" param="@P_3" />
        <Col name="AddressLine2" type="nvarchar" size="40" null="true" param="@P_4" />
        <Col name="AddressLine3" type="nvarchar" size="40" null="true" param="@P_5" />
        <Col name="AddressLine4" type="nvarchar" size="40" null="true" param="@P_6" />
        <Col name="PostalCode" type="nvarchar" size="10" null="true" param="@P_7" />
        <Col name="CountryNo" type="int" param="@P_8" />
        <Col name="City" type="nvarchar" size="50" null="true" param="@P_9" />
        <Col name="Email" type="nvarchar" size="60" null="true" param="@P_10" />
        <Col name="Telephone" type="nvarchar" size="20" null="true" param="@P_11" />
        <Col name="Fax" type="nvarchar" size="20" null="true" param="@P_12" />
        <Col name="OrganizationNo" type="nvarchar" size="30" null="true" param="@P_13" />
        <Col name="ERPHostCompanyNo" type="int" param="@P_14" />
        <Col name="WarehouseNo" type="int" param="@P_15" />
        <Col name="DefaultCustomerNo" type="int" param="@P_16" />
        <Col name="DefaultFloatAmount" type="numeric" prec="28" scale="6" param="@P_17" />
        <Col name="ReceiptProfileName" type="nvarchar" size="60" null="true" param="@P_18" />
        <Col name="GiftReceiptProfileName" type="nvarchar" size="60" null="true" param="@P_19" />
        <Col name="RefundReceiptProfileName" type="nvarchar" size="60" null="true" param="@P_20" />
        <Col name="MerchandiseCreditProfileName" type="nvarchar" size="60" null="true" param="@P_21" />
        <Col name="InvoicePaymentReceiptProfileName" type="nvarchar" size="60" null="true" param="@P_22" />
        <Col name="CashPaymentMethod" type="int" null="true" param="@P_23" />
        <Col name="BankCardPaymentMethod" type="int" null="true" param="@P_24" />
        <Col name="CashOverShortAccount" type="int" null="true" param="@P_25" />
        <Col name="MerchandiseCreditPaymentMethod" type="int" null="true" param="@P_26" />
        <Col name="MerchandiseCreditIssuedAccount" type="int" null="true" param="@P_27" />
        <Col name="PosTimeoutPeriod" type="int" null="true" param="@P_28" />
        <Col name="IsBlindBalancing" type="bit" param="@P_29" />
        <Col name="MaxCashRefund" type="numeric" prec="28" scale="6" null="true" param="@P_30" />
        <Col name="MaxSaleOrderValue" type="numeric" prec="28" scale="6" null="true" param="@P_31" />
        <Col name="MaxSaleOrderLineQuantity" type="int" null="true" param="@P_32" />
        <Col name="RefundDaysLimit" type="int" null="true" param="@P_33" />
        <Col name="DefaultCustomerSaleLimit" type="numeric" prec="28" scale="6" null="true" param="@P_34" />
        <Col name="MinimumMerchandiseCreditAmount" type="numeric" prec="28" scale="6" null="true" param="@P_35" />
        <Col name="GroupCode" type="nvarchar" size="3" null="true" param="@P_36" />
        <Col name="EnableQuickLogin" type="bit" param="@P_37" />
        <Col name="WebAddress" type="nvarchar" size="100" null="true" param="@P_38" />
        <Col name="StoreLogoPath" type="nvarchar" size="200" null="true" param="@P_39" />
        <Col name="AlwaysPrintInvoiceForOnAccountOrders" type="bit" param="@P_40" />
        <Col name="AlwaysPrintPackingListForOnAccountOrders" type="bit" param="@P_41" />
        <Col name="VB520MultiTenderPaymentMethod" type="int" null="true" param="@P_42" />
        <Col name="VB520CashOrderLayoutNo" type="int" null="true" param="@P_43" />
        <Col name="StoreOrgUnitValue" type="int" null="true" param="@P_44" />
        <Col name="IsDemoStore" type="bit" null="true" param="@P_45" />
        <Col name="DefaultLanguage" type="nvarchar" size="50" param="@P_46" />
        <Col name="InvoicePaymentVoucherNoSeries" type="int" null="true" param="@P_47" />
        <Col name="MiscPaymentVoucherNoSeries" type="int" null="true" param="@P_48" />
        <Col name="ProductTypesConf" type="ntext" null="true" param="@P_49" />
        <Col name="ProductCategoriesConf" type="ntext" null="true" param="@P_50" />
        <Col name="CurrencyCode" type="int" param="@P_51" />
        <Col name="StoreGUID" type="uniqueidentifier" param="@P_52" />
        <Col name="GiftCardPaymentMethod" type="int" null="true" param="@P_53" />
        <Col name="AllowGiftCardOvertendering" type="bit" param="@P_54" />
        <Col name="AllowMultipleGiftCardsPerSale" type="bit" param="@P_55" />
        <Col name="DefaultGiftCardExpirationPeriod" type="int" param="@P_56" />
        <Col name="BankCardTypes" type="ntext" null="true" param="@P_57" />
        <Col name="AutoSuggestGiftCardNumbers" type="bit" null="true" param="@P_58" />
        <Col name="GiftCardNumberFormat" type="nvarchar" size="40" null="true" param="@P_59" />
        <Col name="NextGiftCardNumber" type="int" null="true" param="@P_60" />
        <Col name="NextMerchandiseCreditNumber" type="int" param="@P_61" />
        <Col name="MerchandiseCreditNumberFormat" type="nvarchar" size="40" null="true" param="@P_62" />
        <Col name="AllowMerchandiseCreditOvertendering" type="bit" param="@P_63" />
        <Col name="DefaultMechandiseCreditExpirationPeriod" type="int" param="@P_64" />
        <Col name="AllowMultipleMerchandiseCreditPerSale" type="bit" null="true" param="@P_65" />
        <Col name="StoreOrgUnitType" type="int" null="true" param="@P_66" />
        <Col name="ProjectOrgUnitType" type="int" null="true" param="@P_67" />
        <Col name="PosTerminalOrgUnitType" type="int" null="true" param="@P_68" />
        <Col name="DefaultCustomerSeriesNo" type="int" null="true" param="@P_69" />
        <Col name="BusinessType" type="nvarchar" size="40" null="true" param="@P_70" />
        <Col name="HideCommentAndSerialNumber" type="bit" param="@P_71"/>
      </Table>
      <Table Name="[vcrUser]" GlobalName="[vcrUser]" TrackingTable="[vcrUser_tracking]">
        <Col name="UserNo" type="int" param="@P_1" pk="true" />
        <Col name="Name" type="nvarchar" size="60" param="@P_2" />
        <Col name="Username" type="nvarchar" size="50" param="@P_3" />
        <Col name="Password" type="nvarchar" size="50" param="@P_4" />
        <Col name="QuickLogin" type="nvarchar" size="50" param="@P_5" />
        <Col name="IsActive" type="bit" param="@P_6" />
        <Col name="EmployeeNo" type="int" null="true" param="@P_7" />
        <Col name="UserPic" type="image" null="true" param="@P_8" />
      </Table>
      <Table Name="[vcrRole]" GlobalName="[vcrRole]" TrackingTable="[vcrRole_tracking]">
        <Col name="RoleNo" type="int" param="@P_1" pk="true" />
        <Col name="Name" type="nvarchar" size="50" param="@P_2" />
      </Table>
      <Table Name="[vcrUserToRole]" GlobalName="[vcrUserToRole]" TrackingTable="[vcrUserToRole_tracking]">
        <Col name="RoleNo" type="int" param="@P_1" pk="true" />
        <Col name="UserNo" type="int" param="@P_2" pk="true" />
      </Table>
      <Table Name="[vcrPermissionToRole]" GlobalName="[vcrPermissionToRole]" TrackingTable="[vcrPermissionToRole_tracking]">
        <Col name="PermissionNo" type="int" param="@P_1" pk="true" />
        <Col name="RoleNo" type="int" param="@P_2" pk="true" />
      </Table>
      <Table Name="[vcrPointOfSale]" GlobalName="[vcrPointOfSale]" TrackingTable="[vcrPointOfSale_tracking]">
        <Col name="PointOfSaleNo" type="int" param="@P_1" pk="true" />
        <Col name="Name" type="nvarchar" size="100" param="@P_2" />
        <Col name="StoreNo" type="int" param="@P_3" />
        <Col name="AllowSharedFloats" type="bit" param="@P_4" />
        <Col name="AllowOTCSales" type="bit" param="@P_5" />
        <Col name="PointOfSaleMachineSID" type="nvarchar" size="200" null="true" param="@P_6" />
        <Col name="POSTerminalOrgUnitValue" type="int" null="true" param="@P_7" />
        <Col name="AltPosTerminalID" type="nvarchar" size="50" null="true" param="@P_8" />
        <Col name="SerialNumber" type="nvarchar" size="50" null="true" param="@P_9" />
        <Col name="PointOfSaleMachineName" type="nvarchar" size="200" null="true" param="@P_10" />
      </Table>
      <Table Name="[vcrCurrencyDenomination]" GlobalName="[vcrCurrencyDenomination]" TrackingTable="[vcrCurrencyDenomination_tracking]">
        <Col name="DenominationNo" type="int" idSeed="0" idStep="1" param="@P_1" pk="true" />
        <Col name="DenominationName" type="nvarchar" size="50" param="@P_2" />
        <Col name="DenominationValue" type="numeric" prec="28" scale="6" param="@P_3" />
        <Col name="CurrencyCode" type="int" param="@P_4" />
        <Col name="IsActive" type="bit" param="@P_5" />
      </Table>
      <Table Name="[vcrConfigurationParam]" GlobalName="[vcrConfigurationParam]" TrackingTable="[vcrConfigurationParam_tracking]">
        <Col name="ParamNo" type="int" idSeed="0" idStep="1" param="@P_1" pk="true" />
        <Col name="ConfigurationName" type="nvarchar" size="100" param="@P_2" />
        <Col name="ParamName" type="nvarchar" size="100" param="@P_3" />
        <Col name="ParamType" type="int" param="@P_4" />
        <Col name="StringValue" type="nvarchar" size="200" null="true" param="@P_5" />
        <Col name="IntegerValue" type="int" null="true" param="@P_6" />
        <Col name="BooleanValue" type="bit" null="true" param="@P_7" />
      </Table>
      <Table Name="[vcrReceiptProfile]" GlobalName="[vcrReceiptProfile]" TrackingTable="[vcrReceiptProfile_tracking]">
        <Col name="Name" type="nvarchar" size="60" param="@P_1" pk="true" />
        <Col name="IsActive" type="bit" param="@P_2" />
        <Col name="HeaderText" type="nvarchar" size="256" null="true" param="@P_3" />
        <Col name="FooterText" type="nvarchar" size="256" null="true" param="@P_4" />
      </Table>
      <Table Name="[vcrReturnCode]" GlobalName="[vcrReturnCode]" TrackingTable="[vcrReturnCode_tracking]">
        <Col name="ReturnCodeNo" type="int" idSeed="0" idStep="1" param="@P_1" pk="true" />
        <Col name="ReturnCode" type="nvarchar" size="50" param="@P_2" />
        <Col name="Description" type="nvarchar" size="200" null="true" param="@P_3" />
        <Col name="IsInventoryAffected" type="bit" param="@P_4" />
        <Col name="IsActive" type="bit" param="@P_5" />
      </Table>
    </SqlCeSyncProviderScopeConfiguration>


    sb_angela

    Wednesday, November 21, 2012 8:36 AM
  • I have found the problem. The script I used to add the latest column was something like this:

    ALTER TABLE [vcrStore]
    ADD [HideCommentAndSerialNumber] bit NULL
    GO
               
    UPDATE [vcrStore] SET HideCommentAndSerialNumber = 0
    GO

    and it got executed on both databases, but instead the update part should have been executed only on the central database and the data then downloaded to the client during synchronization.

    I have changes this and now it works.


    sb_angela

    • Marked as answer by sb_angela Wednesday, November 21, 2012 2:39 PM
    Wednesday, November 21, 2012 2:34 PM