Oracle to MSSQL 2008 Sync Framework 2.1 - error RRS feed

  • Question

  • Hi.

    I used Database Sync: Oracle and SQL Compact 2-Tier

    and it worked as designed after small adjustments to switch from CE to MSSQL 2008.

    But when I have switched from 2.0 to 2.1 I got exception on:

    SyncOperationStatistics stats = orchestrator.Synchronize();

    Console out:

    INFO   , SyncApplication, 1, 11/22/2011 13:21:43:187,    ----- Inserts for Table "orders" -----
    ERROR  , SyncApplication, 1, 11/22/2011 13:21:43:468, Caught exception while applying changes: System.InvalidCastException: Specified cast is not valid.
       at Microsoft.Synchronization.Data.SqlServer.SqlChangeHandler.ApplyBulkChanges(DataTable dataTable)
       at Microsoft.Synchronization.Data.RelationalSyncProvider.ApplyChangesInternal(DbSyncScopeMetadata scopeMetadata, IDbTransaction transaction, FailedDeleteDelegate_type failedDeleteDelegate, DataSet dataSet, ChangeApplicationType applyType)
       at Microsoft.Synchronization.Data.RelationalSyncProvider.ApplyChanges(DbSyncScopeMetadata scopeMetadata, IDbTransaction applyTransaction, DataSet dataSet, DbSyncSession DbSyncSession, Boolean commitTransaction, FailedDeleteDelegate_type failedDeleteDelegate, String batchFileName, ChangeApplicationAction& action)
    ERROR  , SyncApplication, 1, 11/22/2011 13:21:43:484, Rolling back application transaction.

    Thanks for help.



    • Edited by TPiec Tuesday, November 22, 2011 2:26 PM
    Tuesday, November 22, 2011 1:37 PM


  • try setting SetUseBulkProceduresDefault after you have added all the tables to the scope and just before calling Apply.
    • Marked as answer by TPiec Thursday, November 24, 2011 2:56 PM
    Thursday, November 24, 2011 2:43 PM

All replies

  • have you reprovisioned for 2.1?

    do you see stored procs with the keyword "bulk" in it? (e.g., tablex_bulkinsert, tablex_bulkupdate, etc...)


    Tuesday, November 22, 2011 11:57 PM
  • JuneT: yes those procedures were created in MSSQL 2008 by Sync Framework.

    If I undestand well, all those methods (tablex_selectchanges, tablex_updatemetada, tablex_update etc.) in my scenario are created manualy (SQL on DB) and assigned to DbSyncAdapter object like this eg.:


    DbSyncAdapter adapter = new DbSyncAdapter(SyncUtils.SyncAdapterTables[i]);
    // select incremental changes command
    OracleCommand chgsOrdersCmd = new OracleCommand();
    chgsOrdersCmd.CommandType = CommandType.StoredProcedure;
    chgsOrdersCmd.CommandText = "sp_" + SyncUtils.SyncAdapterTables[i] + "_selectchanges";
    chgsOrdersCmd.Parameters.Add(DbSyncSession.SyncMetadataOnly, OracleType.Int32);
    chgsOrdersCmd.Parameters.Add(DbSyncSession.SyncMinTimestamp, OracleType.Number);
    chgsOrdersCmd.Parameters.Add(DbSyncSession.SyncScopeLocalId, OracleType.Int32);
    chgsOrdersCmd.Parameters.Add("sync_changes", OracleType.Cursor).Direction = ParameterDirection.Output;
    adapter.SelectIncrementalChangesCommand = chgsOrdersCmd;

    And SyncAdapterTables[i] is eg. "orders", and all others are created in the same manner.

    I can not see any apropriate methods (those "bulk" ones) in DbSyncAdapter so I do not have any of those in Oracle DB. Is this a bug of 2.1 ?

    Correct me if I am wrong.

    Thanks for help again.

    Wednesday, November 23, 2011 3:41 PM
  • the error you are getting is on the SQL Server side.

    can you try enabling Sync Tracing in verbose mode?

    also, can you check in your SQL Server if the User Defined Table Type matches the columns defined in your scope? (in SSMS, with your database selected, under Programmability->Types->User Defined Table Types

    Thursday, November 24, 2011 2:11 AM
  • Tracking on verbose level was enabled and gives the same output as console mentioned above.

    (Here is full log : http://www.tpiec.cba.pl/TraceSample.txt )

    And both tables are under Programmanility > Types > User Defined Table Types :

    orders_BulkType and order_details_BulkType


    Thursday, November 24, 2011 9:41 AM
  • the only code i can see that's doing a cast looks like this:

     cmd = ((SqlSyncAdapter) base.Adapter).BulkInsertCommand;

    (one for insert, delete, update)

    and since you're using SqlSyncProvider on one side, am assuming you're not setting the adapters manually in code, only the Oracle ones.

    Thursday, November 24, 2011 12:33 PM
  • Yes. Exactly.

    Only Oracle side is manually created. MSSQL side is created automaticaly.

    Thursday, November 24, 2011 12:55 PM
  • have you tried checking the scope_config table? for the scope you're synching, check the config_data column in the scope_config table and see if the bulk procedures are specified... (am assuming they are there).
    Thursday, November 24, 2011 1:03 PM
  • Again, you are right :

    <SqlSyncProviderScopeConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" IsTemplate="false">
      <Adapter Name="[orders]" GlobalName="[orders]" TrackingTable="[orders_tracking]" SelChngProc="[orders_selectchanges]" SelRowProc="[orders_selectrow]" InsProc="[orders_insert]" UpdProc="[orders_update]" DelProc="[orders_delete]" InsMetaProc="[orders_insertmetadata]" UpdMetaProc="[orders_updatemetadata]" DelMetaProc="[orders_deletemetadata]" BulkTableType="[orders_BulkType]" BulkInsProc="[orders_bulkinsert]" BulkUpdProc="[orders_bulkupdate]" BulkDelProc="[orders_bulkdelete]" InsTrig="[orders_insert_trigger]" UpdTrig="[orders_update_trigger]" DelTrig="[orders_delete_trigger]">
        <Col name="ORDER_ID" type="int" param="@P_1" pk="true" />
        <Col name="ORDER_DATE" type="datetime" null="true" param="@P_2" />
      <Adapter Name="[order_details]" GlobalName="[order_details]" TrackingTable="[order_details_tracking]" SelChngProc="[order_details_selectchanges]" SelRowProc="[order_details_selectrow]" InsProc="[order_details_insert]" UpdProc="[order_details_update]" DelProc="[order_details_delete]" InsMetaProc="[order_details_insertmetadata]" UpdMetaProc="[order_details_updatemetadata]" DelMetaProc="[order_details_deletemetadata]" BulkTableType="[order_details_BulkType]" BulkInsProc="[order_details_bulkinsert]" BulkUpdProc="[order_details_bulkupdate]" BulkDelProc="[order_details_bulkdelete]" InsTrig="[order_details_insert_trigger]" UpdTrig="[order_details_update_trigger]" DelTrig="[order_details_delete_trigger]">
        <Col name="ORDER_DETAILS_ID" type="int" param="@P_1" pk="true" />
        <Col name="ORDER_ID" type="int" null="true" param="@P_2" />
        <Col name="PRODUCT" type="nvarchar" size="100" null="true" param="@P_3" />
        <Col name="QUANTITY" type="int" null="true" param="@P_4" />

    Thursday, November 24, 2011 1:17 PM
  • if you want to test without using bulk procedures, you can set SetUseBulkProceduresDefault  to false during provisioning...its a shame though cause bulk procedures are like one of the best reasons to move to 2.1.


    Thursday, November 24, 2011 1:51 PM
  • This is strange. I tried your suggestion but those bulk procedures are still created and the same error returns.

    Thanks for help JuneT. I will use 2.0 if we can't find reason of this exception.

    And I am not pro enough to digg this.

    Thursday, November 24, 2011 2:20 PM
  • have you deprovisioned first before provisioning again?
    Thursday, November 24, 2011 2:23 PM
  • Yeah I have droped whole MSSQL database, although Oracle was the same (I guess it can stay untouched).
    Thursday, November 24, 2011 2:33 PM
  • try setting SetUseBulkProceduresDefault after you have added all the tables to the scope and just before calling Apply.
    • Marked as answer by TPiec Thursday, November 24, 2011 2:56 PM
    Thursday, November 24, 2011 2:43 PM
  • Excelent ! First success !

    Now sync works as designed except that you have mentioned above. Bulk operations are disabled.

    At lease I can deprovison using 2.1 method ( scopeDeprovisioning.DeprovisionScope(scopeName)) instead of my own methods on 2.0.

    Thanks for help JuneT and your time.

    Thursday, November 24, 2011 2:56 PM
  • Hi TPiec and JuneT,

    I am currently working on a project where i want to sync between a Oracle table and a SqlServer 2008 R2 express.

    I started with the sample proj which syncs the order and Order_Details Tables between Oracle and SqlCe. I tried changing the code to suit SQl Server 2008.

    I am getting  the follwing error during provisioning:
    Line 2: Length or Precision specification 0 is invalid. Must Declare the scalar variable @sync_scope_local_id.

    The code used is :

     private void CheckIfProviderNeedsSchema(RelationalSyncProvider sqlserverProvider, RelationalSyncProvider oracleProvider)
                if (sqlserverProvider != null)
                    sqlserverProvider = sqlserverProvider as SqlSyncProvider;
                    SqlSyncScopeProvisioning sqlConfig = new SqlSyncScopeProvisioning();
                    SqlConnection sqlConn = (SqlConnection)sqlserverProvider.Connection;
                    string scopeName = sqlserverProvider.ScopeName;
                    if (!sqlConfig.ScopeExists(scopeName, sqlConn))
                        DbSyncScopeDescription scopeDesc = ((DbSyncProvider)oracleProvider).GetScopeDescription();
                        // We have to manually fix up the Oracle types on the DbSyncColumnDescriptions. 
                        // Alternatively we could just construct the DbScopeDescription from scratch.
                        for (int i = 0; i < scopeDesc.Tables.Count; i++)
                            for (int j = 0; j < scopeDesc.Tables[i].Columns.Count; j++)
                                // When grabbing the Oracle schema table the type field gets set to the index of that type in the OracleType enumeration.
                                // We will have to change it to the actual name instead of the index.
                                scopeDesc.Tables[i].Columns[j].Type = ((OracleType)Int32.Parse(scopeDesc.Tables[i].Columns[j].Type)).ToString().ToLower();
                                //// We also have to convert number to a decimal for sql server
                                if (scopeDesc.Tables[i].Columns[j].Type == "number")
                                    scopeDesc.Tables[i].Columns[j].Type = "decimal";
                                // Because the DbSyncColumnDescription only had a number for the Type (which it does not understand), it could not 
                                // auto-fill in the required attributes for that field type.  So in the case of a string field, we have to manually 
                                // set the length ourselves.  If we wanted to set scale and precision for the previous decimal field we need to do the same.
                                if (scopeDesc.Tables[i].Columns[j].Type == "nvarchar")
                                    scopeDesc.Tables[i].Columns[j].Size = "100";

    Can you pls suggest how do I get over this issue.

    If possible can you kindly mail me (or post in this thread) a working copy of a sync between the Oracle and Sql server, mentioning any pre reqs.



    Thursday, May 10, 2012 7:41 AM
  • have you tried setting precision for the decimal data type? (i.e., scopeDesc.Tables[i].Columns[j].Precision)

    Thursday, May 10, 2012 8:00 AM
  • I think decimal needs precision. I guess you can try JuneT's solution or this:

    if (scopeDesc.Tables[i].Columns[j].Type == "number")
        scopeDesc.Tables[i].Columns[j].Type = "int";

    • Edited by TPiec Thursday, May 10, 2012 8:23 AM
    Thursday, May 10, 2012 8:22 AM
  • Added the check fr precision and it works smoothly.

    Thanks for the tip. BTW TPiec's suggestion worked too., but I am going with the Precision way.
    On a separate note can we sync th content of view to table?

    Thursday, May 10, 2012 11:17 AM
  • views are not supported out of the box... the change tracking is based on triggers and for views, you will have to write instead of triggers.
    Thursday, May 10, 2012 12:59 PM
  • I could get it to work with the suggested change of code.

    Now I am modifying to fit it to my real life tables and I am getting the following error.

    System.ArgumentException: The property '{0}' requires a string value that is not null or empty.
    Parameter name: Type
       at Microsoft.Synchronization.Data.SyncExptEx.CheckStringPropertyNullOrEmpty(String value, String propertyName)
       at Microsoft.Synchronization.Data.DbSyncColumnDescription.set_Type(String value)
       at Microsoft.Synchronization.Data.DbSyncColumnDescription.SetPropertiesFromDataColumn(SyncSchemaColumn dataColumn)
       at Microsoft.Synchronization.Data.DbSyncTableDescriptionBuilder.FromSchemaTable(SyncSchemaTable schemaTable)
       at Microsoft.Synchronization.Data.DbSyncTableDescriptionBuilder.FromSyncSchema(SyncSchema schema)
       at Microsoft.Synchronization.Data.DbSyncScopeDescription..ctor(String scopeName, SyncSchema syncSchema)
       at Microsoft.Synchronization.Data.DbSyncProvider.GetScopeDescription()
       at SqlOracleSync.SynchronizationHelper.CheckIfProviderNeedsSchema(SqlSyncProvider localProvider, OracleDbSyncProvider remoteProvider) in F:\Projects\SqlOracleSync\SqlOracleSync\SynchronizationHelper.cs:line 69
       at SqlOracleSync.SynchronizationHelper.SynchronizeProviders(RelationalSyncProvider localProvider, RelationalSyncProvider remoteProvider) in F:\Projects\SqlOracleSync\SqlOracleSync\SynchronizationHelper.cs:line 42}

    This is wht we did:

    1. Created a new scope in the Oracle scope_info called "customer_scope"

    2. Added the table name for the scope in the scope_table_map, "customer"

    3. In the SyncUtils class, added the same names as strings as required

    4. Changed the ConfigureDBSyncProvider method appropriately to fill the commands and the adapter object.

    5. Then, when the following code executes,

    DbSyncScopeDescription scopeDesc = (remoteProvider).GetScopeDescription();
    we see the error. I am suspecting a mismatch in variable names between theC# code and the generaed Sprocs. Pls suggest.

    Monday, May 14, 2012 3:37 PM
  • i think the error is in specifying the data type...have you pinned down the specific column where its failing?

    Wednesday, May 16, 2012 11:41 AM
  • Show us what columns you have in your table (types).
    I had issues with that GetScopeDescription (eg. Float from Oracle was mapped to Int in MSSQL. Try it.)

    So when you will understand sync methods prepare destination db (MSSQL) yourself ignoring that bugged GetScopeDescription. (I did myself).

    Wednesday, May 16, 2012 12:15 PM