A common problem is that the rows returned are not expected: sometimes no rows at all, sometimes too few or too many. This note helps you analyze this problem to find the cause.
Check query
First determine the query used. For Invantive Data Replicator is in your scripts; for Invantive Bridge Online this can be found in Invantive Bridge Online Monitoring.
Make sure that all cases use exactly the same query. An additional filter or join can alter the results.
Wrong Partition Selected
Invantive SQL can run queries on one or thousands of partitions such as Exact Online companies.
A wrong selection of partition(s) can lead to no rows at all or a different set of rows than expected.
You should forcefully select the partition to rule out the cause of selection of the wrong partition, such as in your script or in your Invantive Bridge Online database definition:
use <PARTITION>,...
User is not Authorized for Data
An authorization issue can lead to no rows at all or a different set of rows than expected.
Most drivers return no rows when the user is not authorized or licensed.
You can also query the Exact Online REST API whether a user is authorized on a API. This is done through the UserHasRights table function, but it is quite buggy since it returns HTTP 503 errors on invalid parameters.
A proven solution is to use the following query:
select sio.partition
, sio.path
, sio.action
, uhr.UserHasRights
from ( select distinct
regexp_replace(regexp_replace(sio.url, '.*/v1/([0-9]+/|)', ''), '\?.*', '') path
, case
when sio.action = 'READ'
then 'GET'
else 'POST'
end
action
, sio.partition
from SessionIOS@DataDictionary sio
where sio.url is not null
) sio
join UserHasRights(sio.partition, sio.path, sio.action) uhr
This query checks all your recent API activity, assumes it all concerns the REST API of Exact Online and then presents a list of the authorizations:
Results from a Previous Data Replicator Replication
For Data Replicator only.
When available, Invantive Data Replicator will use sufficiently fresh replicated data. However, changes after establishing the replica are not included. Especially during setup of an Exact Online environment, tables can be empty at start.
To load fresh results from Exact Online, you can specify a number of hints:
select /*+ ods(false) use_http_disk_cache(false) use_http_memory_cache(false) */ *from exactonlinerest..accountclassifications
Run the query again. The current results are displayed.
You can also check the outcome of the following query to check whether an actual I/O has been executed:
select *from sessionios@datadictionary
Analysis Operation of Exact Online Sync APIs (*Incremental tables)
There is a separate cache layer for the *Incremental tables on Exact Online (and Freshdesk). These are accompanied by a number of analytical views that give insight in the data update process. For more information please refer to Faster Exact Online download using incremental "sync API"-tables.
Compare Tables
Exact Online provides often multiple tables to retrieve the same data set. For instance, the list of General Ledger Accounts can be retrieved using:
ExactOnlineXML..GLAccountsExactOnlineREST..GLAccountsGLAccountsBulkGLAccountsIncrementalSyncGLAccounts
When data do not match the expectations please always compare the contents of the alternatives with the original table.
Check for Exact Online Server Move
Exact Online incidentally relocates an Exact Online company between servers. Each server has it’s own value for the timestamp/rowversion datatype. This value is crucial for *Incremental-tables to work correctly.
To assess the current approximate value of the datatype, execute a query such as:
select max(Timestamp)
from SyncAccounts
Replace SyncAccounts by another table where necessary.
The last seen value of the timestamp as used for the *Incremental-table can be found in IncrementalLoadStatuses@eol as “Last seen timestamp”.
This values should be close. A difference of more than 1.000.000.000 signals that the company might have been moved between servers.
In that case please also check DivisionMoveDate from SystemDivisions:
select DivisionMoveDate
from SystemDivisions
This value is the last cross-server move in the Exact Online data center. The value written as YYYYMMDDHH24MISS should be identical to the value of the TimestampEnvironment column in IncrementalLoadStatuses.
Need further assistance?
For further assistance, you can use Invantive consulting services. An appointment can be scheduled using Calendly - Guido Leenders. The invoice will have an amount for every started quarter of an hour.