Error itgenclr096: System.Data.SqlClient.SqlException - Teamleader

Vragen
, ,
1

Ik krijg sinds een aantal dagen onderstaande errormelding wanneer ik vanuit Teamleader de v1 deals probeer te syncen. Onderstaand de code die uitgevoerd wordt, en daaronder de errormelding.

Enig idee waar dit aan kan liggen? (zie ook de error helemaal onderaan)

SQL-code

local on error continue

local define AANTAL_SLOTS “5”

local define SLOT_SIZE “7”

local define DAYS_UPDATE “7”

select to_char(trunc(sysdateutc) - ${DAYS_UPDATE}, ‘YYYYMMDD’)

local define WATERSHED_DATE “${outcome:0,0}”

create or replace table deals@inmemorystorage
as
select *
from teamleader.v1.deals@tl del

create or replace table dealsnew@inmemorystorage
as
select *
from deals@inmemorystorage del
where del.latest_activity_date >= to_date(‘${WATERSHED_DATE}’, ‘YYYYMMDD’)

create or replace table deals_soll@inmemorystorage
as
select del.id
, deld.*
from dealsnew@inmemorystorage del
join teamleader.v1.deal(del.id)@tl deld

synchronize tl1_deals@sqlserver
from deals_soll@inmemorystorage
with insert
or
update all except rowid$
identified
by id

Errorlog

2021-08-18 09:57:30.747 Information itgendhb044: Run SQL statement: synchronize tl1_deals@sqlserver
from deals_soll@inmemorystorage
with insert
or
update all except rowid$
identified
by id
2021-08-18 09:57:30.749 Information itgensql264: itgen_invantive_sql_statement_started_dot
2021-08-18 09:57:39.435 Warning itgendhb173: itgen_context: C:\jobs\sql\replicate.test.4.sql: synchronize tl1_deals@sqlserver from deals_soll@inmemor… update all except rowid$ identified by id
2021-08-18 09:57:39.505 Error itgencun016: Error itgenclr096: itgenclr096: Multiple errors in Invantive Data Hub.
One or more errors occurred.
Incorrect syntax near the keyword ‘for’.
2021-08-18 09:57:41.515 Error itgenclr096: System.Data.SqlClient.SqlException
System.Data.SqlClient.SqlException
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Invantive.Data.AnsiSqlProviderBase.PassthroughSqlAction(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameterList, String loggingTableName) --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Invantive.Data.AnsiSqlProviderBase.PassthroughSqlAction(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameterList, String loggingTableName) at Invantive.Data.AnsiSqlProviderBase.Update(GlobalState owner, ExecutionOptions executionOptions, EntityFieldCollection entityFields, QueryObject queryObject, ParameterList parameterValues, ResultSet sourceContext) --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Invantive.Data.AnsiSqlProviderBase.Update(GlobalState owner, ExecutionOptions executionOptions, EntityFieldCollection entityFields, QueryObject queryObject, ParameterList parameterValues, ResultSet sourceContext) at Invantive.Sql.SynchronizeStatementTree.<Synchronize>d__72.MoveNext() System.Data.SqlClient.SqlException at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Invantive.Data.AnsiSqlProviderBase.PassthroughSqlAction(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameterList, String loggingTableName)
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Invantive.Data.AnsiSqlProviderBase.PassthroughSqlAction(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameterList, String loggingTableName)
at Invantive.Data.AnsiSqlProviderBase.Update(GlobalState owner, ExecutionOptions executionOptions, EntityFieldCollection entityFields, QueryObject queryObject, ParameterList parameterValues, ResultSet sourceContext)
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Invantive.Data.AnsiSqlProviderBase.Update(GlobalState owner, ExecutionOptions executionOptions, EntityFieldCollection entityFields, QueryObject queryObject, ParameterList parameterValues, ResultSet sourceContext)
at Invantive.Sql.SynchronizeStatementTree.d__72.MoveNext()
System.AggregateException
ValidationException
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Invantive.Data.AnsiSqlProviderBase.PassthroughSqlAction(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameterList, String loggingTableName)
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Invantive.Data.AnsiSqlProviderBase.PassthroughSqlAction(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameterList, String loggingTableName)
at Invantive.Data.AnsiSqlProviderBase.Update(GlobalState owner, ExecutionOptions executionOptions, EntityFieldCollection entityFields, QueryObject queryObject, ParameterList parameterValues, ResultSet sourceContext)
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Invantive.Data.AnsiSqlProviderBase.Update(GlobalState owner, ExecutionOptions executionOptions, EntityFieldCollection entityFields, QueryObject queryObject, ParameterList parameterValues, ResultSet sourceContext)
at Invantive.Sql.SynchronizeStatementTree.d__72.MoveNext()

Kan het wellicht iets te maken hebben met onderstaande errormelding? Deze komt voor bij het opstarten van de datahub v20.0

2021-08-18 09:55:13.722 Error itgencun016: Exclamation itgenclr068: Could not load file or assembly ‘System.Numerics.Vectors, Version=4.1.3.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ or one of its dependencies. The located assembly’s manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040).
2021-08-18 09:55:13.736 Error itgencun017: System.IO.FileLoadException
at System.SpanHelpers.IndexOf(Byte& searchSpace, Byte value, Int32 length)
at System.Text.Json.Utf8JsonReader.ConsumeStringAndValidate(ReadOnlySpan`1 data, Int32 idx)
at System.Text.Json.Utf8JsonReader.ConsumeString()
at System.Text.Json.Utf8JsonReader.ConsumeValue(Byte marker)
at System.Text.Json.Utf8JsonReader.ReadSingleSegment()
at System.Text.Json.Utf8JsonReader.Read()
at System.Text.Json.JsonSerializer.ReadCore(JsonSerializerOptions options, Utf8JsonReader& reader, ReadStack& readStack)
at System.Text.Json.JsonSerializer.ReadCore(Type returnType, JsonSerializerOptions options, Utf8JsonReader& reader)
at System.Text.Json.JsonSerializer.Deserialize(String json, Type returnType, JsonSerializerOptions options)
at System.Text.Json.JsonSerializer.Deserialize[TValue](String json, JsonSerializerOptions options)
at Invantive.Data.TranslationStore.FT(GlobalState , ExecutionOptions , String , String , String , Boolean )
at Invantive.Data.TranslationStore.Read(GlobalState owner, ExecutionOptions executionOptions, String text, String expectedLanguageCode, String originatingFileName)
at Invantive.Data.TranslationStore.LoadTranslations(GlobalState owner, ExecutionOptions executionOptions, String fileName, String expectedLanguageCode)
at Invantive.Data.DataContextBase.LoadTranslations(GlobalState owner, ExecutionOptions executionOptions, String translationStoreFileLocation, String expectedLanguageCode)
at Invantive.Producer.UtilityBaseCore.CreateAndLoadTranslations(GlobalState owner, ExecutionOptions executionOptions, String languageCode)
at Invantive.Producer.UtilityBaseCore.Initialize(GlobalState owner, ExecutionOptions executionOptions)

2

Ik zie in een andere topic nog een paar algemene controles. Bij deze de antwoorden daarop:

  • Welke versie van Windows draait op de server?
    Microsoft Windows Server 2012 R2 Standard, Versie 6.3.9600 Build 9600

  • Is het Microsoft.NET framework geïnstalleerd in versie 4.7.2 of nieuwer?
    Ja. 4.8 is geïnstalleerd.

  • Zijn alle Windows updates bijgewerkt tot/met ten hoogste 30 dagen geleden?
    Ja

3

Welke versie van de Invantive software exact geeft deze melding?

De tweede melding heeft er niet mee te maken naar verwachting. Het lijkt er op dat een intern opgesteld SQL Server statement niet ondersteund wordt op 2012R2.

4

Dit is Invantive Data Hub (20.0.135-PROD+1719), het heeft voorheen wel gewerkt op deze versie.

5

Het zou kunnen dat eerder gewerkt heeft maar in een iets andere situatie. Het achterliggende statement is:

update Teamleader.dbo.tl1_deals set for = @for0, for_id = @for_id1, optional_contact_person_id = @optional_contact_person_id2, source_id = @source_id3, total_price_excl_vat = @total_price_excl_vat4, date_last_activity = @date_last_activity5, date_last_activity_formatted = @date_last_activity_formatted6, c_aantal_personen = @c_aantal_personen7 where ( rowid$ = @w1rowid$ )

Waarschijnlijk is het gebruik van de kolom “for” incorrect. Advies is om de allerlaatste versie te installeren en nogmaals te proberen.

7

Thanks. Het lijkt er inderdaad op dat de kolom ‘for’ niet goed opgevat wordt door SQL Server, omdat ‘for’ ook een SQL statement is. Ik heb het voor nu gefixt door de data weg te schrijven naar een andere tabel in de database, ipv het gebruiken van het synchronize statement

Vervolgens draai ik op de SQL Server zelf een DELETE FROM en INSERT INTO, om het incrementeel bij te werken.

Hierbij heb ik de kolom ‘for’ expliciet aangeroepen als ‘[for]’. Dit omzeilt het probleem. Mochten anderen hetzelfde probleem ervaren, zie hieronder de volledige code.

local define DAYS_UPDATE "7"

select to_char(trunc(sysdateutc) - ${DAYS_UPDATE}, 'YYYYMMDD')

local define WATERSHED_DATE "${outcome:0,0}"

CREATE OR REPLACE TABLE deals@inmemorystorage
as
select *
from   teamleader.v1.deals@tl del

CREATE OR REPLACE TABLE dealsnew@inmemorystorage
as
select *
from   deals@inmemorystorage del
where  del.latest_activity_date >= to_date('${WATERSHED_DATE}', 'YYYYMMDD')

CREATE OR REPLACE TABLE tl1_deals_temp@sqlserver
as
select del.id
,      deld.*
from   dealsnew@inmemorystorage del
join   teamleader.v1.deal(del.id)@tl deld


DELETE FROM dbo.tl1_deals@sqlserver
WHERE id IN (
	SELECT id 
	FROM dbo.tl1_deals_temp@sqlserver
)

INSERT INTO dbo.tl1_deals@sqlserver
SELECT id
    ,id_1
    ,title
    ,responsible_user_id
    ,[for]
    ,for_id
    --alle andere kolommen--       
FROM dbo.tl1_deals_temp@sqlserver
8

Deze vraag is automatisch gesloten na 2 weken inactiviteit. Het laatste gegeven antwoord is gemarkeerd als oplossing.

Gelieve een nieuwe vraag te stellen via een apart topic als het probleem opnieuw optreedt. Gelieve in de nieuwe vraag een link naar dit topic op te nemen door de URL er van in de tekst te plakken.