Answered by:
Sql Compact 3.5 syncronization fails

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