locked
Oracle to SQL Server Sync Error - Invalid Cast Exception RRS feed

  • General discussion

  • I am trying to sync tables in a Oracle 11g and Sql Sever 2008 databases using the Sync Framework. 

     

    I used the Database Sync: Oracle and SQL Compact 2-Tier Sample as a base and was able to extend it to sync data between the Oracle 11g and Sql Server database using the same order and order_details  tables. I used ODP.NET data access libraries everything worked as expected. 

     

    After this, I decided to extend this the actually scenario I need to use it for. The data format and type are different for the scenario I am trying to use this for: Oracle -> RemoteProvider SQL Sever -> Local Provider. I am not sure if I need to have additional code porbably LocalDataConverter/RemoteDataConverter to map the data format's / types. Could anyone provide me some guidance?

     

    I am getting the following errors

     

    DownloadAndUpload Scenario : Exception "Specified cast is not valid".

     

     at Oracle.DataAccess.Client.OracleDataReader.GetInt32(Int32 i)

       at Microsoft.Synchronization.Data.DbSyncScopeHandler.ReadScope(IDbConnection connection, IDbTransaction transaction, ReadKnowledgeType readType, ReadForgottenKnowledgeType readFKtype)

       at Microsoft.Synchronization.Data.SyncScopeHandlerBase.ReadScope(IDbConnection connection, ReadKnowledgeType readType)

       at Microsoft.Synchronization.Data.RelationalSyncProvider.GetScope(DbSyncSession DbSyncSession)

       at Microsoft.Synchronization.Data.RelationalSyncProvider.GetSyncBatchParameters(UInt32& batchSize, SyncKnowledge& knowledge)

       at Microsoft.Synchronization.KnowledgeProviderProxy.GetSyncBatchParameters(ISyncKnowledge& ppSyncKnowledge, UInt32& pdwRequestedBatchSize)

       at Microsoft.Synchronization.CoreInterop.ISyncSession.Start(CONFLICT_RESOLUTION_POLICY resolutionPolicy, _SYNC_SESSION_STATISTICS& pSyncSessionStatistics)

       at Microsoft.Synchronization.KnowledgeSyncOrchestrator.DoOneWaySyncHelper(SyncIdFormatGroup sourceIdFormats, SyncIdFormatGroup destinationIdFormats, KnowledgeSyncProviderConfiguration destinationConfiguration, SyncCallbacks DestinationCallbacks, ISyncProvider sourceProxy, ISyncProvider destinationProxy, ChangeDataAdapter callbackChangeDataAdapter, SyncDataConverter conflictDataConverter, Int32& changesApplied, Int32& changesFailed)

       at Microsoft.Synchronization.KnowledgeSyncOrchestrator.DoOneWayKnowledgeSync(SyncDataConverter sourceConverter, SyncDataConverter destinationConverter, SyncProvider sourceProvider, SyncProvider destinationProvider, Int32& changesApplied, Int32& changesFailed)

       at Microsoft.Synchronization.KnowledgeSyncOrchestrator.Synchronize()

       at Microsoft.Synchronization.SyncOrchestrator.Synchronize()

       at DbSync.Program.Sync() in C:\_NET\Projects\DBSync\DBSync\Program.cs:line 119

     

     

    Download Scenario :  

     

       at Microsoft.Synchronization.Data.DbSyncScopeHandler.GetLocalTimestamp(IDbConnection connection, IDbTransaction transaction)

       at Microsoft.Synchronization.Data.DbSyncScopeHandler.ReadScope(IDbConnection connection, IDbTransaction transaction, ReadKnowledgeType readType, ReadForgottenKnowledgeType readFKtype)

       at Microsoft.Synchronization.Data.SyncScopeHandlerBase.ReadScope(IDbTransaction transaction, ReadKnowledgeType readType, ReadForgottenKnowledgeType readFKtype)

       at Microsoft.Synchronization.Data.RelationalSyncProvider.GetChanges(DbSyncScopeMetadata scopeMetadata, DbSyncSession DbSyncSession, UInt32 memoryBatchSize)

       at Microsoft.Synchronization.Data.RelationalSyncProvider.GetChangeBatch(UInt32 batchSize, SyncKnowledge destinationKnowledge, Object& changeDataRetriever)

       at Microsoft.Synchronization.KnowledgeProviderProxy.GetChangeBatch(UInt32 dwBatchSize, ISyncKnowledge pSyncKnowledge, ISyncChangeBatch& ppChangeBatch, Object& ppUnkDataRetriever)

       at Microsoft.Synchronization.CoreInterop.ISyncSession.Start(CONFLICT_RESOLUTION_POLICY resolutionPolicy, _SYNC_SESSION_STATISTICS& pSyncSessionStatistics)

       at Microsoft.Synchronization.KnowledgeSyncOrchestrator.DoOneWaySyncHelper(SyncIdFormatGroup sourceIdFormats, SyncIdFormatGroup destinationIdFormats, KnowledgeSyncProviderConfiguration destinationConfiguration, SyncCallbacks DestinationCallbacks, ISyncProvider sourceProxy, ISyncProvider destinationProxy, ChangeDataAdapter callbackChangeDataAdapter, SyncDataConverter conflictDataConverter, Int32& changesApplied, Int32& changesFailed)

       at Microsoft.Synchronization.KnowledgeSyncOrchestrator.DoOneWayKnowledgeSync(SyncDataConverter sourceConverter, SyncDataConverter destinationConverter, SyncProvider sourceProvider, SyncProvider destinationProvider, Int32& changesApplied, Int32& changesFailed)

       at Microsoft.Synchronization.KnowledgeSyncOrchestrator.Synchronize()

       at Microsoft.Synchronization.SyncOrchestrator.Synchronize()

       at DbSync.Program.Sync() in C:\_NET\Projects\DBSync\DBSync\Program.cs:line 119

    Monday, October 25, 2010 2:49 AM

All replies

  • did you modify the provider itself or did you just add the tables/columns you want to synchronize?
    Monday, October 25, 2010 4:57 AM
  • I did not modify the provider. All I did was change the DbSyncAdapters to use the ODP.NET data access library instead of the built in System.Data.OracleClient library and changed the data types and mapping.

    I think the issue was probably that I am was trying to cast the null values or there is a type mismatch somewhere.

    Monday, October 25, 2010 4:47 PM
  • check that data types in your databases matches the corresponding .NET data type in your provider.

    Tuesday, October 26, 2010 12:42 AM
  • Ok, I reverted back to using System.Data.OracleClient that resolved the issue with Cast not Valid exception. Progress....

    I de-provisioned my SQL Database and provisioned it again. Now I have run into a different issues  "Cannot apply changes because the local provider does not have adapters configured for the following tables that were received"

    I already have the DbSyncAdpaters configured  per the sample for the Oracle  DbSyncProvider, but there is nothing of this sort for the SqlSyncProvider. I guess the SqlSyncProvider constructs these dynamically from the SqlSyncProviderScopeConfiguration xml that is generated and stored in the scope_config table. 

    Am I missing something???

    Tuesday, October 26, 2010 4:31 PM