Itgeneor823 foutmelding bij draaien script

Vragen
, ,
1

Bij het draaien van een script krijg ik de volgende foutmelding in Invantive Query Tool 24.0.594, waar ik vanaf 24/11/2025 19:50:39 (UTC) mee was verbonden.

itgeneor823: Statement #12 'insert into e....InvoiceNumber' starting on line 50 caused an error.

The OAuth access token for Exact Online has expired or is currently not accepted.

The access token has expired.

The remote server returned an error: (401) Unauthorized.

Please check that Exact Online is online. Try again later when Exact Online is offline. Re-authenticate on Exact Online when online and the access token is still not accepted.

Message ID: ea49bd90-e937-4d3c-9286-478826f37829

Occurred (UTC): 24/11/2025 19:55:57

insert into exactonlinerest..SalesEntries@eol
( Division
, EntryNumber
, InvoiceNumber
, Customer
, Description
, Journal
, Status
, Type
, EntryDate
, DueDate
, Currency
) 
select /*+ http_disk_cache(false) http_memory_cache(false) */ 3935673 Division
,      ftr.InvoiceNumber EntryNumber
,      ftr.InvoiceNumber
,      act.Id Customer
,      'Factuur ' || invoiceNumber description
,      '70' Journal
,      50
,      case
       when AmountDC < 0 
       then 21 
       else 20 
       end 
       Type
,      to_date(ftr.InvoiceDate, 'DD-MM-YYYY') InvoiceDate
,      to_date(ftr.ExpiryDate, 'DD-MM-YYYY') DueDate
,      ftr.Currency
-- 
-- Uncomment to get account code.
--
--,      ftr.AccountCode
from   InvoicesToLoad@inmemorystorage ftr
left
outer
join   ExactOnlineREST..AccountsBulk@eol act
on     act.Division = 3935673
and    act.Code = to_char(ftr.AccountCode)
and    act.Status = 'C' /* Customer */
where  ftr.LineNumber = 1
identified by 'INVOICE' || ftr.InvoiceNumber

System.Net.WebException
ValidationException
OAuthException
ValidationException
   at System.Net.HttpWebRequest.GetResponse()
   at Invantive.Data.Providers.Http.HttpBasedProvider.DoRequest(GlobalState owner, ExecutionOptions executionOptions, HttpWebRequest request, String url, ObjectDefinition objectDefinition, QueryObject queryObject, String partitionCode, String callSafeNameOverrule, String anonymizedPostText, Boolean allowRetryOnNoConnectionMade, Boolean allowRetryOnConnectionLoss, ParameterList parameters, Guid nativeCallUid, Boolean throwExceptionOnNonSuccessfulStatusCode, Object requestBody, Int64 timeoutInitialMs, Int64 timeoutMaxOnRetryMs, String resultCacheId, String publicQueryParameters, SqlExecutionStep sqlExecutionStep, ExecutionStatistics& executionStatistics, ODataErrorProcessingInstructions& oDataErrorProcessingInstructions)
2

Het refresh token zou ververst moeten worden, maar bijkomend probleem is dat een gegenereerd SQL Server-statement wat er voor staat extreem traag is. Het gaat om circa 24.000 regels SQL zijnde:

select * from i8BI4ExactOnline_NWB.dbo.import20250115_ftr_sedis e where InvoiceNumber >= 25100503 and InvoiceNumber < 25979999 
and InvoiceNumber not in (25120002
, 25118137
, 25103932
, 25119950
, ... <23700+ regels meer
)

Als ik de not in weglaat, dan is de query in paar honderd milliseconden en geeft 174 rijen.

De bijbehorende Invantive UniversalSQL die dit statement genereert is:

create or replace table InvoicesToLoad@inmemorystorage
as
select *
from   import20250115_ftr_sedis@sql ftr
where  to_char(ftr.InvoiceNumber) like '2512%'
and    ftr.invoicenumber >= ${INVOICENUMBER_FROM}
and    ftr.invoicenumber < ${INVOICENUMBER_TO}
and    ftr.invoicenumber not in ( select EntryNumber from InvoicesLoaded@InMemoryStorage )
3

Uiteindelijk looptijd van de SQL Server-query teruggebracht naar minder dan 1 seconde, door een left outer join te gebruiken zodat hij de not in niet meer opstuurt:

--
-- Importeer alle reeds verzonden facturen uit SEDIS
-- in Exact Online.
--
-- Het factuurnummer in Exact Online is gelijk aan dat uit SEDIS.
--
rollback transaction

set use-http-memory-cache@eol false

set use-http-disk-cache@eol false

local define DIVISION "123123"

use ${DIVISION}@eol

--
-- Verandering voor incrementeel: kijken welke entrynumbers nopg niet doorgeboekt zijn.
--

local define INVOICENUMBER_FROM "25100503"

local define INVOICENUMBER_TO "25979999"

--
-- Retrieve from Exact Online the entry numbers / invoice numbers already known.
--
create or replace table InvoicesLoaded@InMemoryStorage
as
select /*+ http_disk_cache(true) http_memory_cache(true) */ 
       txn.EntryNumber
from   TransactionsIncremental@eol txn
where  txn.type in (20,21)
and    txn.entrynumber >= ${INVOICENUMBER_FROM}
and    txn.entrynumber <  ${INVOICENUMBER_TO}

create or replace table InvoicesToLoad@inmemorystorage
as
select ftr.*
from   import20250115_ftr_sedis@sql ftr
left
outer
join   InvoicesLoaded@InMemoryStorage ild
on     ild.EntryNumber = ftr.invoicenumber
where  ftr.invoicenumber >= ${INVOICENUMBER_FROM}
and    ftr.invoicenumber < ${INVOICENUMBER_TO}
--
-- Not previously loaded.
--
and    ild.EntryNumber is null

begin transaction

insert into exactonlinerest..SalesEntries@eol
( Division
, EntryNumber
, InvoiceNumber
, Customer
, Description
, Journal
, Status
, Type
, EntryDate
, DueDate
, Currency
) 
select /*+ http_disk_cache(false) http_memory_cache(false) */ ${DIVISION} Division
,      ftr.InvoiceNumber EntryNumber
,      ftr.InvoiceNumber
,      act.Id Customer
,      'Factuur ' || invoiceNumber description
,      '70' Journal
,      50
,      case
       when AmountDC < 0 
       then 21 
       else 20 
       end 
       Type
,      to_date(ftr.InvoiceDate, 'DD-MM-YYYY') InvoiceDate
,      to_date(ftr.ExpiryDate, 'DD-MM-YYYY') DueDate
,      ftr.Currency
-- 
-- Uncomment to get account code.
--
--,      ftr.AccountCode
from   InvoicesToLoad@inmemorystorage ftr
left
outer
join   ExactOnlineREST..AccountsBulk@eol act
on     act.Division = ${DIVISION}
and    act.Code = to_char(ftr.AccountCode)
and    act.Status = 'C' /* Customer */
where  ftr.LineNumber = 1
identified by 'INVOICE' || ftr.InvoiceNumber

insert into ExactOnlineREST..SalesEntryLines@eol
( Division
, AmountFC
, Description
, GLAccount
, Quantity
, linenumber
, VATCode
, VATAmountFC
)
select ${DIVISION} Division
,      ftr.AmountDC + 1 * ftr.VATAmountDC
,      ftr.Description
,      gat.ID GLAccount
,      1 Quantity
,      ftr.linenumber
,      ftr.VATCode
,      ftr.VATAmountDC
--
-- Uncomment when needed for analysis.
--
--,      ftr.Grootboek
from   InvoicesToLoad@inmemorystorage ftr
left
outer
join   GLAccountsBulk@eol gat
on     gat.Division = ${DIVISION}
and    gat.Code = 
       -- Vertaal oude grootboekrekeningen.
       case 
       when ftr.Grootboek = '8000' then '80000' 
       when ftr.Grootboek = '2081' then '15070' 
       when ftr.Grootboek = '7041' then '76000' 
       when ftr.Grootboek = '7802' then '76010' 
       when ftr.Grootboek = '7803' then '76020' 
       when ftr.Grootboek = '7804' then '76030' 
       when ftr.Grootboek = '7805' then '76050' 
       when ftr.Grootboek = '7806' then '76060' 
       when ftr.Grootboek = '7807' then '76070' 
       when ftr.Grootboek = '7808' then '76080' 
       when ftr.Grootboek = '8400' then '80316' 
       else ftr.Grootboek 
       end
where  1=1
attach to 'INVOICE' || ftr.InvoiceNumber

commit transaction
4

Fijn dat script weer doorloopt. Dit is een goede workaround voor itgeneor823.

Voor een analysemogelijkheid hiervan is verzoek om een nieuwe versie te downloaden en installeren vanaf https://download.invantive.com.

Deze nieuwe versie bevat de nieuwe tabel SystemOAuthFlowEvents@DataDictionary waarmee de werking beter te analyseren is. Na het optreden van de foutmelding itgeneor823 gelieve met een select * alle rijen op te halen en toe te voegen aan dit ticket.