none
Operand type clash: datetime2 is incompatible with uniqueidentifier RRS feed

  • Question

  • I'm trying to debug a problem with a provisioned SQL 2008 Server 

    After provisioning a newly created database, it was able to synchronize and do so for 10 hours until someone started to insert / update data in the tables... Soon after the Sync would start giving me this error :

    INFO   , SyncApp, 10, 08/23/2012 11:42:42:922,    ----- Updates for Table "DUB_LINES" -----

    WARNING, SyncApp, 10, 08/23/2012 11:42:42:930, Bulk command BulkUpdateCommand failed with the following exception.
     Rows will be retried during single apply. System.Data.SqlClient.SqlException (0x80131904): Operand type clash: datetime2 is incompatible with uniqueidentifier
    The data for table-valued parameter "@changeTable" doesn't conform to the table type of the parameter.

       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
       at System.Data.SqlClient.SqlDataReader.get_MetaData()
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
       at Microsoft.Synchronization.Data.SqlServer.SqlChangeHandler.ExecuteTVPCommand(IDbCommand cmd, DataTable applyTable, DataTable failedRows)
    ERROR  , SyncApp, 10, 08/23/2012 11:42:42:931, Transaction has exited due to command execution failure, throwing exception
    ERROR  , SyncApp, 10, 08/23/2012 11:42:42:934, Caught exception while applying changes: Microsoft.Synchronization.Data.DbSyncException: Failed to execute the command 'BulkUpdateCommand' for table 'DUB_LINES'; the transaction was rolled back. Ensure that the command syntax is correct. ---> System.Data.SqlClient.SqlException: Operand type clash: datetime2 is incompatible with uniqueidentifier
    The data for table-valued parameter "@changeTable" doesn't conform to the table type of the parameter.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
       at System.Data.SqlClient.SqlDataReader.get_MetaData()
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
       at Microsoft.Synchronization.Data.SqlServer.SqlChangeHandler.ExecuteTVPCommand(IDbCommand cmd, DataTable applyTable, DataTable failedRows)
       --- End of inner exception stack trace ---
       at Microsoft.Synchronization.Data.ChangeHandlerBase.CheckZombieTransaction(String commandName, String table, Exception ex)
       at Microsoft.Synchronization.Data.SqlServer.SqlChangeHandler.ExecuteTVPCommand(IDbCommand cmd, DataTable applyTable, DataTable failedRows)
       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  , SyncApp, 10, 08/23/2012 11:42:42:934, Rolling back application transaction.
    INFO   , SyncApp, 10, 08/23/2012 11:42:42:934,    EndSession() called on Provider SqlSyncProvider, Microsoft.Synchronization.Data.SqlServer, Version=3.1.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
    INFO   , SyncApp, 10, 08/23/2012 11:42:42:934,    EndSession() called on Provider SqlSyncProvider, Microsoft.Synchronization.Data.SqlServer, Version=3.1.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
    INFO   , SyncApp, 10, 08/23/2012 11:42:42:934,       SyncBatchProducer: Canceling Background enumeration thread. Waiting for cancellation event: False


    My first thought was to check the BulkType for inconsistencies eg: columns out of order - but to my great surprise, nearly 50% of the columns in the BulkType are positioned differently than they are in the table designer !!!

    • Edited by Montago Thursday, August 23, 2012 12:47 PM
    Thursday, August 23, 2012 12:44 PM

All replies

  • The position of the columns in the table designer nor the UDT has no bearing.

    Check the data type instead if the source and destination are the same.

    Thursday, August 23, 2012 1:26 PM
    Moderator
  • The position of the columns in the table designer nor the UDT has no bearing.

    Check the data type instead if the source and destination are the same.

    Thank for the quick response.

    ALL DATABASES in this scenario has been created using the same script so i cant imagine how or why the datatypes can be different between the tables...  :-/

    Thursday, August 23, 2012 2:23 PM
  • Do you have any other advice how to track down this error and find a solution ?..

    I'm now minutely checking that datatypes are correct in all ends. 

    Friday, August 24, 2012 9:32 AM
  • I've now noticed that DataTypes in the BulkType are Nullable while the values in the table are 'Not Null' 

    Would that be a problem ?

    Friday, August 24, 2012 9:48 AM
  • is it possible to 'decompile' / 'decompress' the Batch files to look at the data being transfered ?
    Friday, August 24, 2012 10:51 AM
  • The Nullable UDT and Not Null on the column may not be causing the error.

    the batch files are actually dataset surrogates (serialized as byte arrays)

    you can just isolate checking the UDTs and table columns types to the table where its causing an error.

    do you have datetime2 columns?

    Saturday, August 25, 2012 2:41 AM
    Moderator
  • We don't have a single DateTime2 column in any of the tables - so unless the SyncFX uses DateTime2 we don't have it in the database. (unless Microsoft SQL Management Studio is lying and is actually creating DateTime2 instead of DateTime)

    I've checked all the UDT BulkTypes and crosschecked between the datatables, and everything chekcs out correct, there are no inconsistancy between datatypes or names :-(

    Saturday, August 25, 2012 8:43 AM
  • can you subscribe to the ChangesSelected event, put a breakpoint and see the structure and values of the datatable?

    Thursday, August 30, 2012 11:11 AM
    Moderator