Itgeneor823 error message when running script

When running a script, I get the following error message in Invantive Query Tool 24.0.594, to which I was connected from 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
-- 
-- 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)

The refresh token should be refreshed, but an additional problem is that a generated SQL Server statement preceding it is extremely slow. It concerns around 24,000 lines of SQL, namely:

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

If I leave out the not in, the query runs in a few hundred milliseconds and returns 174 rows.

The corresponding Invantive UniversalSQL that generates this statement 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 )

Finally reduced the runtime of the SQL Server query to less than 1 second by using a left outer join so that it no longer sends a not in:

--
-- Import all invoices already sent from SEDIS
-- into Exact Online.
--
-- The invoice number in Exact Online is the same as that from SEDIS.
--
rollback transaction

set use-http-memory-cache@eol false

set use-http-disk-cache@eol false

local define DIVISION "123123"

use ${DIVISION}@eol

--
-- Change for incremental: check which entry numbers have not yet been posted.
--

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 = 
       -- Translate old general ledger accounts.
       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

Good to hear the script runs through again. This is a good workaround for itgeneor823.

To enable an analysis of this, please could you download and install a new version from https://download.invantive.com.

This new version includes the new table SystemOAuthFlowEvents@DataDictionary, which makes it possible to analyse the behaviour more thoroughly. After the error message itgeneor823 occurs, please retrieve all rows using a select * and add them to this ticket.

This question was automatically closed after 1 week of inactivity. The last answer given has been marked as the solution.

If the problem occurs again, please ask a new question in a separate topic. In the new question, please include a link to this topic by pasting its URL into the text.