Remove From My Forums

Sql Compact 3.5 syncronization fails

Question
0

Sign in to vote
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

0

Sign in to vote
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

0

Sign in to vote

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

0

Sign in to vote

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

0

Sign in to vote
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

Answered by:

Sql Compact 3.5 syncronization fails

Question

Answers

All replies