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
