Invalid object name 'Evaluation_tracking'. Statement(s) could not be prepared.

[Tlocke1]

Hello!

This one has us stumped.

We are using Entity Framework with SQL Server to LocalDB. Change Tracking on both databases and it has been working well.
However, in trying a soft test release to a few real users, we have run into this error (stack trace below).
The user had already successfully synched and later when they went to sync again, they would get this error and the table name would change. In this case, the table is called "Evaluation". The object is a CTE found in several stored procedures related to that table
Evaluation_changes
Evaluation_initialize
Evaluation_update
Evaluation_delete
Evaluation_bulkupdate
Evaluation_bulkdelete

There is no indication which stored procedure was called however.
It has happened at least once to each user but difficult for us to recreate.

Any suggestions would be greatly appreciated!

Warm Regards,
Tim

Exception Stack:

Invalid object name 'Evaluation_tracking'.
Statement(s) could not be prepared.  Exception Attributes:
    Message: Invalid object name 'Evaluation_tracking'.
Statement(s) could not be prepared.
    Exception type: Dotmim.Sync.SyncException
    Source: Dotmim.Sync.Core
    Thrown by code in 
Method: RaiseError
    Thrown by code in class: BaseOrchestrator
  Stack Trace:

Method: Dotmim.Sync.BaseOrchestrator.RaiseError(Exception exception)    
Method: Dotmim.Sync.BaseOrchestrator.<RunInTransactionAsync>d__67`1.MoveNext()    
Method: --- End of stack trace from previous location where exception was thrown ---    
Method: System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    
Method: Dotmim.Sync.BaseOrchestrator.<RunInTransactionAsync>d__67`1.MoveNext()    
Method: --- End of stack trace from previous location where exception was thrown ---    
Method: System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    
Method: System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)    
Method: Dotmim.Sync.SyncAgent.<>c__DisplayClass53_0.<<SynchronizeAsync>b__0>d.MoveNext()    
Method: --- End of stack trace from previous location where exception was thrown ---    
Method: System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    
Method: System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)    
Method: Dotmim.Sync.SyncAgent.<SynchronizeAsync>d__53.MoveNext()    
Method: --- End of stack trace from previous location where exception was thrown ---    
Method: System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    
Method: System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)    
Method: System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()    
Method: CoEcho.LocalDataSyncUtility.SyncHelper.<SynchronizeAsync>d__23.MoveNext()
Invalid object name 'Evaluation_tracking'.
Statement(s) could not be prepared.  Exception Attributes:
    Message: Invalid object name 'Evaluation_tracking'.
Statement(s) could not be prepared.
    Exception type: Microsoft.Data.SqlClient.SqlException
    Source: Framework Microsoft SqlClient Data Provider
    Thrown by code in 
Method: OnError
    Thrown by code in class: SqlConnection
  Stack Trace:
    
Method: Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)    
Method: Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)    
Method: Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)    
Method: Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)    
Method: Microsoft.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption)    
Method: Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, String endMethod, Boolean isInternal)    
Method: Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)    
Method: Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)    
Method: System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)    
Method: --- End of stack trace from previous location where exception was thrown ---    
Method: System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    
Method: System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)    
Method: Dotmim.Sync.BaseOrchestrator.<InternalUpdateMetadatasAsync>d__79.MoveNext()    
Method: --- End of stack trace from previous location where exception was thrown ---    
Method: System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    
Method: System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)    
Method: Dotmim.Sync.BaseOrchestrator.<HandleConflictAsync>d__6.MoveNext()    
Method: --- End of stack trace from previous location where exception was thrown ---    
Method: System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    
Method: Dotmim.Sync.BaseOrchestrator.<InternalApplyChangesBatchAsync>d__5.MoveNext()    
Method: --- End of stack trace from previous location where exception was thrown ---    
Method: System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    
Method: Dotmim.Sync.BaseOrchestrator.<InternalApplyTableChangesAsync>d__4.MoveNext()    
Method: --- End of stack trace from previous location where exception was thrown ---    
Method: System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    
Method: Dotmim.Sync.BaseOrchestrator.<InternalApplyChangesAsync>d__0.MoveNext()    
Method: --- End of stack trace from previous location where exception was thrown ---    
Method: System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    
Method: System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)    
Method: Dotmim.Sync.LocalOrchestrator.<>c__DisplayClass7_0.<<ApplyChangesAsync>b__0>d.MoveNext()    
Method: --- End of stack trace from previous location where exception was thrown ---    
Method: System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()    
Method: System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)    
Method: Dotmim.Sync.BaseOrchestrator.<RunInTransactionAsync>d__67`1.MoveNext()

[Mimetis]

Change Tracking using the SqlSyncChangeTrackingProvider is not using any tracking table (like Evaluation_tracking you are mentioning)
I guess you have made tests with SqlSyncProvider and then change to SqlSyncChangeTrackingProvider.
These two providers generate different things, that are not compatible.
I guess your best option it to Deprovision / Provision again your schema
If you don't want to loose anything about the history of changes tracked, just do a Deprovision on Triggers / Stored procedures
Then provision again.
Should resolve your issue

[Tlocke1]

Thanks for your reply.

We have only used the SqlSyncChangeTrackingProvider.
Evaluation_tracking is not a table, its a Common Table Expression in the stored procedures DMS creates.

For our Evaluation table, DMS created the following 9 stored procedures
Evaluation_changes
Evaluation_initialize
Evaluation_selectrow
Evaluation_update
Evaluation_delete
Evaluation_deletemetadata
Evaluation_bulkupdate
Evaluation_bulkdelete
Evaluation_reset

Inside of the Evaluation_changes stored procedure, there is the following code that contains a Common Table Expression:
;WITH
[Evaluation_tracking] AS (
SELECT [CT].[Id],
CAST([CT].[SYS_CHANGE_CONTEXT] as uniqueidentifier) AS [update_scope_id],
[CT].[SYS_CHANGE_VERSION] as [timestamp],
CASE WHEN [CT].[SYS_CHANGE_OPERATION] = 'D' THEN 1 ELSE 0 END AS [sync_row_is_tombstone]
FROM CHANGETABLE(CHANGES [User], @sync_min_timestamp) AS [CT]
)
SELECT DISTINCT
[side].[Id],
[base].[]
[side].[sync_row_is_tombstone],
[side].[update_scope_id]
FROM [Evaluation] [base]
RIGHT JOIN [Evaluation_tracking] [side]ON [base].[Id] = [side].[Id]
WHERE (
[side].[timestamp] > @sync_min_timestamp
AND ([side].[update_scope_id] <> @sync_scope_id OR [side].[update_scope_id] IS NULL)
)

The Evaluation_tracking CTE object is where the error is coming from, and it is not always this particular CTE.
It happens on other tables as well.

We are not mixing Sync Provider types. Both SQL servers have change tracking enabled.
This all normally works fine. This issue happens sporadically and we are trying to figure out why.

To recover we have just been dropping the db and resyncing which recreates all of this again.

Could the localDB be not spinning up quickly enough? is there a way to wake it before executing a sync?

Thank you for any ideas you might have!

Tim

[Mimetis]

Oh, sorry, I misunderstood your problem...
I don't know exactly why you have this kind of weird issue, to be honest.
It seems that there is some glitches with the Change Tracking on localDB...

If you think it's a spinning up problem, maybe you could make some sql work before trying to sync, on the first run ?

[Tlocke1]

Will give that a shot.
We cant determine if its a flakey VPN issue, or perhaps their laptops went to sleep then they went back online to sync...
What is really is strange is that if the db was not available, the app would not know about the Stored Procedure in order to create the CTE that the inline query is trying to reference. It has happened only a handful of times with the same error but different tables.
I cant imagine what would cause the CTE not to exist... Perhaps something in the CHANGETABLE() function is failing...
It works normally otherwise. Here is hoping that warming up the db will help.

Thanks for your thoughts,
Tim