none
How to extend Wizard generated Code to support row filters for NON Primary Keys? RRS feed

  • Question

  • Hi JuneT,

    based on your post http://jtabadero.wordpress.com/2010/03/17/adding-filter-to-local-database-cache-generated-sync/
    I added filters to the wizard generated code. This works fine as long as the filtered column is the primary key.

    For non Primary Keys I changed the filter form

      Dim myFilter As String = " (CT.PersonalNumber=@PersonalNumber) AND "
      to
      Dim myFilter As String = " (Invoice.PersonalNumber=@PersonalNumber) AND "
     
    as you suggested there.


    But this fails with an error:

    System.ServiceModel.FaultException`1[System.ServiceModel.ExceptionDetail]: Unable to enumerate changes at the DbServerSyncProvider for table 'Invoice' in synchronization group 'MainData'. <More...>

    in the blog-post, someboby had the same problem and you suggested to run sql profiler. I did this, with this result:

    exec sp_executesql N'IF @sync_initialized > 0  BEGIN SELECT dbo.Invoice.[InvoiceNumber], [PersonalNumber] FROM dbo.Invoice JOIN CHANGETABLE(CHANGES dbo.Invoice, @sync_last_received_anchor) CT ON CT.[InvoiceNumber] = dbo.Invoice.[InvoiceNumber] WHERE (Invoice.PersonalNumber=@PersonalNumber) AND  (CT.SYS_CHANGE_OPERATION = ''U'' AND CT.SYS_CHANGE_VERSION <= @sync_new_received_anchor AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N''dbo.Invoice'')) > @sync_last_received_anchor RAISERROR (N''SQL Server Change Tracking has cleaned up tracking information for table ''''%s''''. To recover from this error, the client must reinitialize its local database and try again'',16,3,N''dbo.Invoice'')  END ',N'@sync_initialized bit,@sync_last_received_anchor bigint,@sync_new_received_anchor bigint,@sync_client_id_binary varbinary(16),@PersonalNumber nchar(8)',@sync_initialized=1,@sync_last_received_anchor=3077033,@sync_new_received_anchor=3077034,@sync_client_id_binary=0x7C158FB1967F3B4795914159FFAF4F2E,@PersonalNumber=N'123456'

    Directly running this query in MMS returns an empty table, but no error.

    What am I doing wrong here?

    Thanks Christian
    Monday, August 29, 2011 4:54 PM

Answers

  • there you go...we should have seen this earlier :)

    dont add the filter on the SelectIncrementalDeletesCommand

     

    deletes only keeps track of the PK of the row deleted and there is nothing to join on the base table.

     

    • Marked as answer by chl-h Thursday, September 1, 2011 10:43 AM
    Thursday, September 1, 2011 10:29 AM
    Moderator

All replies

  • check the entire exception stack to see if there are any other information about the exception. the fact that you can run the query in SSMS means the query is valid and so is your filter.

    try enabling sync framework tracing as well.

    Tuesday, August 30, 2011 2:33 AM
    Moderator
  • HI JuneT, 

    the whole exception looks like:

    System.Reflection.TargetInvocationException: Ein Aufrufziel hat einen Ausnahmefehler verursacht. ---> System.ServiceModel.FaultException`1[System.ServiceModel.ExceptionDetail]: Unable to enumerate changes at the DbServerSyncProvider for table 'Invoice' in synchronization group 'MainData'.

    Server stack trace:
       bei System.ServiceModel.Channels.ServiceChannel.ThrowIfFaultUnderstood(Message reply, MessageFault fault, String action, MessageVersion version, FaultConverter faultConverter)
       bei System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc)
       bei System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)
       bei System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)
       bei System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)

    Exception rethrown at [0]:

       bei System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
       bei System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
       bei Sync_Test.ServiceReferenceMKServer.ILocalDataCache1SyncContract.GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession)
       bei Sync_Test.ServiceReferenceMKServer.LocalDataCache1SyncContractClient.GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession) in C:\Users\CH\Desktop\sync_stuff\Sync_Test-y\Sync_Test\Service References\ServiceReferenceMKServer\Reference.vb:Zeile 70.

       --- Ende der internen Ausnahmestapelüberwachung ---

       bei System.RuntimeMethodHandle._InvokeMethodFast(IRuntimeMethodInfo method, Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeType typeOwner)
       bei System.RuntimeMethodHandle.InvokeMethodFast(IRuntimeMethodInfo method, Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeType typeOwner)
       bei System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
       bei System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
       bei Microsoft.Synchronization.Data.ServerSyncProviderProxy.GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession)
       bei Microsoft.Synchronization.SyncAgent.DownloadChanges(SyncGroupMetadata groupMetadata)
       bei Microsoft.Synchronization.SyncAgent.DataSynchronize()
       bei Microsoft.Synchronization.SyncAgent.Synchronize()
       bei Sync_Test.Form1.ToolStripButton1_Click(Object sender, EventArgs e) in C:\Users\CH\Desktop\sync_stuff\Sync_Test-y\Sync_Test\Form1.vb:Zeile 54.

     

    the lines of code that are mentioned there are:

     

       Form1.vb:Zeile (=line) 54:  Dim syncStats As Microsoft.Synchronization.Data.SyncStatistics = syncAgent.Synchronize()
     
     Reference.vb:Zeile (=line) 70 in:
       Public Function GetChanges(ByVal groupMetadata As Microsoft.Synchronization.Data.SyncGroupMetadata, ByVal syncSession As Microsoft.Synchronization.Data.SyncSession) As Microsoft.Synchronization.Data.SyncContext Implements ServiceReferenceMKServer.ILocalDataCache1SyncContract.GetChanges
                Return MyBase.Channel.GetChanges(groupMetadata, syncSession)
        End Function

    This does not help me any further, I will try to enable sync tracing as you suggested, have not done that before so first I will have to see how this is done.

    Do you see anything in the exception that might help?

    Thanks

    Christian

     

     

     

     

     


    Tuesday, August 30, 2011 8:10 AM
  • have you tried changing your filter to:  " (dbo.Invoice.[PersonalNumber], =@PersonalNumber) AND "

    Tuesday, August 30, 2011 8:17 AM
    Moderator
  • tried this but threw the same error.(I removed the comma in " (dbo.Invoice.[PersonalNumber], =@PersonalNumber) AND " and changed that to  " (dbo.Invoice.[PersonalNumber] =@PersonalNumber) AND " )

    the sql-profiler looks shows now:

    exec sp_executesql N'IF @sync_initialized > 0  BEGIN SELECT dbo.Invoice.[InvoiceNumber],[PersonalNumber] FROM dbo.Invoice JOIN CHANGETABLE(CHANGES dbo.Invoice, @sync_last_received_anchor) CT ON CT.[InvoiceNumber] = dbo.Invoice.[InvoiceNumber] WHERE (dbo.Invoice.[PersonalNumber]=@PersonalNumber) AND  (CT.SYS_CHANGE_OPERATION = ''U'' AND CT.SYS_CHANGE_VERSION <= @sync_new_received_anchor AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N''dbo.Invoice'')) > @sync_last_received_anchor RAISERROR (N''SQL Server Change Tracking has cleaned up tracking information for table ''''%s''''. To recover from this error, the client must reinitialize its local database and try again'',16,3,N''dbo.Invoice'')  END ',N'@sync_initialized bit,@sync_last_received_anchor bigint,@sync_new_received_anchor bigint,@sync_client_id_binary varbinary(16),@PersonalNumber nchar(8)',@sync_initialized=1,@sync_last_received_anchor=3077033,@sync_new_received_anchor=3077038,@sync_client_id_binary=0x7C158FB1967F3B4795914159FFAF4F2E,@PersonalNumber=N'123456'

     

    I added the trace listener like this to my app.config:
      <!--http://msdn.microsoft.com/en-us/library/cc807160.aspx-->
      <system.diagnostics>
        <switches>
          <!--  0-off, 1-error, 2-warn, 3-info, 4-verbose. -->
          <add name="SyncTracer" value="4" />
        </switches>

        <trace autoflush="true">
          <listeners>
            <add name="TestListener" type="System.Diagnostics.TextWriterTraceListener" initializeData="D:\Trace.txt"/>
          </listeners>
        </trace>
      </system.diagnostics>
     
      The trace works for the other tables, but does not trace anything for the error synchoronizing the table Invoice.

    Somewhere at the beginning it shows:

    VERBOSE, Sync_Test.vshost, 7, 08/30/2011 09:12:35:060, Invoice: Read SentAnchor value: NULL
    VERBOSE, Sync_Test.vshost, 7, 08/30/2011 09:12:35:062, Invoice: Read ReceivedAnchor value: 3077033

    these are the only lines where the table invoice is mentioned. Seems that trace stops working after the error is thrown.

     

     


    Tuesday, August 30, 2011 9:01 AM
  • i think the trace youre running is on the client side code whereas the exception is occuring on the remote provider in the WCF service side.

    try enabling WCF tracing to get a more detailed exception or find the the GetChanges  call in the service side and put a break on it so you can debug and see what exception is thrown.

    Tuesday, August 30, 2011 10:49 AM
    Moderator
  • don't know how to set breakpoint at the web hosted service for debugging, but I went with the WCF tracing, and analysed the trace.svclog.

    Well I have never done that before, so I might miss something (if you wish I can sent you the file), but the most interesting info I found is where the error happens:

    <ExceptionString>Microsoft.Synchronization.Data.DataSyncException: Unable to enumerate changes at the DbServerSyncProvider for table 'Invoice' in synchronization group 'MainData'. ---&gt; System.Data.SqlClient.SqlException: The multi-part identifier "dbo.Invoice.PersonalNumber" could not be bound.
       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.Server.DbServerSyncProvider.EnumerateChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession, IDbTransaction transaction, EnumerateChangeType changeType, SyncSchema traceSchema)
       --- End of inner exception stack trace ---
       at Microsoft.Synchronization.Data.Server.DbServerSyncProvider.EnumerateChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession, IDbTransaction transaction, EnumerateChangeType changeType, SyncSchema traceSchema)
       at Microsoft.Synchronization.Data.Server.DbServerSyncProvider.GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession)
       at WcfSyncService.LocalDataCache1SyncService.GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession) in C:\Users\CH\Desktop\sync_stuff\Sync_Test-y\WcfSyncService\LocalDataCache1.Server.SyncContract.vb:line 123
       at SyncInvokeGetChanges(Object , Object[] , Object[] )
       at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]&amp; outputs)
       at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc&amp; rpc)
       at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc&amp; rpc)
       at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage31(MessageRpc&amp; rpc)
       at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)</ExceptionString>
      
       the mentioned WcfSyncService\LocalDataCache1.Server.SyncContract.vb:line 123 Line is where getchanges returns
        Return Me._serverSyncProvider.GetChanges(groupMetadata, syncSession)

    new is The multi-part identifier "dbo.Invoice.PersonalNumber" could not be bound. What does that tell me?

    according to http://stackoverflow.com/questions/206558/what-is-a-multi-part-identifier-and-why-cant-it-be-bound this is in most cases a typo, but I double checked and tried other columns as well.

       
    Thanks
    Christian


    Tuesday, August 30, 2011 2:04 PM
  • what happens if you change your filter to just PersonalNumber=@PersonalNumber?

    Tuesday, August 30, 2011 11:43 PM
    Moderator
  • the WCF-trace errormessage is now:

    <ExceptionString>Microsoft.Synchronization.Data.DataSyncException: Unable to enumerate changes at the DbServerSyncProvider for table 'Invoice' in synchronization group 'MainData'. ---&gt; System.Data.SqlClient.SqlException: Invalid column name 'PersonalNumber'.

     

     

     

    Wednesday, August 31, 2011 9:22 AM
  • Hi June, a little update:
     
    I tried another tables with columns of another data type (int).

    this time I want to filter on table FakturaPosition for Column Auftragposition.

    I am using this filter 

    Dim myFilter As String = " (dbo.FakturaPosition.[Auftragsposition]=@Auftragsposition) AND "

    the sql profiler shows THREE queries where Auftragsposition is passed in.

    Number One:

    exec sp_executesql N'IF @sync_initialized = 0 SELECT dbo.FakturaPosition.[ID], [RechnungsNummer], [PersonalNummer], [MaterialNummer], [Rechnungsposition], [Fakturierte_Menge], [Verkaufsmengeneinheit], [Nettowert], [Verkaufspreis], [Auftrag], [Auftragsposition], [Lieferung], [Lieferungsposition], [Bestellnummer_Des_Kunden], [Datum_Erstellung] FROM dbo.FakturaPosition LEFT OUTER JOIN CHANGETABLE(CHANGES dbo.FakturaPosition, @sync_last_received_anchor) CT ON CT.[ID] = dbo.FakturaPosition.[ID] WHERE (dbo.FakturaPosition.[Auftragsposition]=@Auftragsposition) AND  (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary) ELSE  BEGIN SELECT dbo.FakturaPosition.[ID], [RechnungsNummer], [PersonalNummer], [MaterialNummer], [Rechnungsposition], [Fakturierte_Menge], [Verkaufsmengeneinheit], [Nettowert], [Verkaufspreis], [Auftrag], [Auftragsposition], [Lieferung], [Lieferungsposition], [Bestellnummer_Des_Kunden], [Datum_Erstellung] FROM dbo.FakturaPosition JOIN CHANGETABLE(CHANGES dbo.FakturaPosition, @sync_last_received_anchor) CT ON CT.[ID] = dbo.FakturaPosition.[ID] WHERE (dbo.FakturaPosition.[Auftragsposition]=@Auftragsposition) AND  (CT.SYS_CHANGE_OPERATION = ''I'' AND CT.SYS_CHANGE_CREATION_VERSION  <= @sync_new_received_anchor AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N''dbo.FakturaPosition'')) > @sync_last_received_anchor RAISERROR (N''SQL Server Change Tracking has cleaned up tracking information for table ''''%s''''. To recover from this error, the client must reinitialize its local database and try again'',16,3,N''dbo.FakturaPosition'')  END ',N'@sync_initialized bit,@sync_last_received_anchor bigint,@sync_client_id_binary varbinary(16),@sync_new_received_anchor bigint,@Auftragsposition int',@sync_initialized=1,@sync_last_received_anchor=3077033,@sync_client_id_binary=0x7C158FB1967F3B4795914159FFAF4F2E,@sync_new_received_anchor=3077038,@Auftragsposition=1

    this works fine in SSMS

    Number two, some milliseconds later

    exec sp_executesql N'IF @sync_initialized > 0  BEGIN SELECT dbo.FakturaPosition.[ID], [RechnungsNummer], [PersonalNummer], [MaterialNummer], [Rechnungsposition], [Fakturierte_Menge], [Verkaufsmengeneinheit], [Nettowert], [Verkaufspreis], [Auftrag], [Auftragsposition], [Lieferung], [Lieferungsposition], [Bestellnummer_Des_Kunden], [Datum_Erstellung] FROM dbo.FakturaPosition JOIN CHANGETABLE(CHANGES dbo.FakturaPosition, @sync_last_received_anchor) CT ON CT.[ID] = dbo.FakturaPosition.[ID] WHERE (dbo.FakturaPosition.[Auftragsposition]=@Auftragsposition) AND  (CT.SYS_CHANGE_OPERATION = ''U'' AND CT.SYS_CHANGE_VERSION <= @sync_new_received_anchor AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N''dbo.FakturaPosition'')) > @sync_last_received_anchor RAISERROR (N''SQL Server Change Tracking has cleaned up tracking information for table ''''%s''''. To recover from this error, the client must reinitialize its local database and try again'',16,3,N''dbo.FakturaPosition'')  END ',N'@sync_initialized bit,@sync_last_received_anchor bigint,@sync_new_received_anchor bigint,@sync_client_id_binary varbinary(16),@Auftragsposition int',@sync_initialized=1,@sync_last_received_anchor=3077033,@sync_new_received_anchor=3077038,@sync_client_id_binary=0x7C158FB1967F3B4795914159FFAF4F2E,@Auftragsposition=1

    this works fine in SSMS

    Number Three some milliseconds later, the query that crashes.

    exec sp_executesql N'IF @sync_initialized > 0  BEGIN SELECT CT.[ID] FROM CHANGETABLE(CHANGES dbo.FakturaPosition, @sync_last_received_anchor) CT WHERE (dbo.FakturaPosition.[Auftragsposition]=@Auftragsposition) AND  (CT.SYS_CHANGE_OPERATION = ''D'' AND CT.SYS_CHANGE_VERSION <= @sync_new_received_anchor AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N''dbo.FakturaPosition'')) > @sync_last_received_anchor RAISERROR (N''SQL Server Change Tracking has cleaned up tracking information for table ''''%s''''. To recover from this error, the client must reinitialize its local database and try again'',16,3,N''dbo.FakturaPosition'')  END ',N'@sync_initialized bit,@sync_last_received_anchor bigint,@sync_new_received_anchor bigint,@sync_client_id_binary varbinary(16),@Auftragsposition int',@sync_initialized=1,@sync_last_received_anchor=3077033,@sync_new_received_anchor=3077038,@sync_client_id_binary=0x7C158FB1967F3B4795914159FFAF4F2E,@Auftragsposition=1

    SSMS shows this error

    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "dbo.FakturaPosition.Auftragsposition" could not be bound.


    As far as I understand sync_initialized = 0, means  initial sync. -> Query One, OK

    Two queries for subsequent syncs.   Why two?


    Any more ideas what I could try and what is actually wrong with that query?

    Thanks Christian

    Thursday, September 1, 2011 10:17 AM
  • there you go...we should have seen this earlier :)

    dont add the filter on the SelectIncrementalDeletesCommand

     

    deletes only keeps track of the PK of the row deleted and there is nothing to join on the base table.

     

    • Marked as answer by chl-h Thursday, September 1, 2011 10:43 AM
    Thursday, September 1, 2011 10:29 AM
    Moderator
  • Cool! That worked.

    Thank you so much, this was giving me a hard time.

    Thursday, September 1, 2011 10:43 AM