Itgeneor823 fejlmeddelelse ved kørsel af script

Når jeg kører et script, får jeg følgende fejlmeddelelse i Invantive Query Tool 24.0.594, som jeg var forbundet til fra 24/11/2025 19:50:39 (UTC).

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
-- 
-- Fjern kommentering for at få kontokoden.
--
--,      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)

Refresh-tokenet skulle gerne blive fornyet, men et yderligere problem er, at et genereret SQL Server-statement, der står foran det, er ekstremt langsomt. Det drejer sig om cirka 24.000 linjer SQL, nemlig:

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

Hvis jeg fjerner not in, så kører forespørgslen på et par hundrede millisekunder og returnerer 174 rækker.

Den tilsvarende Invantive UniversalSQL, der genererer dette statement, er:

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 )

Den endelige køretid for SQL Server-forespørgslen er reduceret til under 1 sekund ved at bruge en left outer join, så den ikke længere sender en not in:

--
-- Importer alle allerede afsendte fakturaer fra SEDIS
-- til Exact Online.
--
-- Fakturanummeret i Exact Online er det samme som i SEDIS.
--
rollback transaction

set use-http-memory-cache@eol false

set use-http-disk-cache@eol false

local define DIVISION "123123"

use ${DIVISION}@eol

--
-- Ændring for inkrementel: se hvilke entry-numre endnu ikke er bogført.
--

local define INVOICENUMBER_FROM "25100503"

local define INVOICENUMBER_TO "25979999"

--
-- Hent fra Exact Online de entry-numre/fakturanumre, der allerede er kendt.
--
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}
--
-- Ikke tidligere indlæst.
--
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
-- 
-- Fjern kommentar for at få kontokoden.
--
--,      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
--
-- Fjern kommentar, når det er nødvendigt til analyse.
--
--,      ftr.Grootboek
from   InvoicesToLoad@inmemorystorage ftr
left
outer
join   GLAccountsBulk@eol gat
on     gat.Division = ${DIVISION}
and    gat.Code = 
       -- Oversæt gamle hovedbogskonti.
       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

Godt at scriptet kører videre igen. Dette er en god workaround for itgeneor823.

For at kunne analysere dette, bedes du downloade og installere en ny version fra https://download.invantive.com.

Denne nye version indeholder den nye tabel SystemOAuthFlowEvents@DataDictionary, som gør det muligt at analysere funktionsmåden bedre. Efter at fejlmeddelelsen itgeneor823 er opstået, bedes du hente alle rækker med et select * og tilføje dem til denne sag.

Dette spørgsmål er automatisk blevet lukket efter 1 uges inaktivitet. Det sidst givne svar er markeret som løsningen.

Hvis problemet opstår igen, bedes du oprette et nyt spørgsmål i et separat emne. Husk at inkludere et link til dette emne i det nye spørgsmål ved at indsætte URL’en i teksten.