Configure user to solve itgenexl029 on SQL query on Exact Online

Question

When I run the following query using Invantive Data Hub on Exact Online (Netherlands):

create or replace table spn@inmemorystorage
as
select /*+ ods(false) */ 
       spn.item_code_attr
,      spn.warehouse_code_attr
,      spn.item_description
,      spn.currentquantity
,      spn.planning_in
,      spn.planning_out
,      spn.currentquantity + spn.planning_in - spn.planning_out plannedquantity
from   exactonlinexml..StockPositions spn
--
-- Filter out no stock nor planned.
--
where  ( spn.currentquantity !=0
         or     
         spn.planning_in != 0
         or     
         spn.planning_out != 0
       )

I receive the following error:

Error itgencun016: Uitroep itgenexl029: U heeft onvoldoende rechten om deze actie uit te voeren.

with call stack:

Error itgencun017: Type: Invantive.Data.ServiceException
   at Invantive.Data.ValidationException..ctor(String messageCode, String messageText, String kindRequest, String localStackTrace, String nk, Exception innerException)
   at Invantive.Data.InvantiveSystemException..ctor(String validationCode, String errorMessage, String kindRequest, String stackTrace, String nk, Exception innerException)
   at Invantive.Data.ExactOnlineXmlProvider.FailOnAnswerWithErrors(XmlDocument document, String topic, String& responseErrorsOnly, String& responseXmlPayloadRetry, Exception& ex, Int32& countSuccess, Int32& countWarning, Int32& countError, Int32& countFatalError)
   at Invantive.Data.ExactOnlineXmlProvider.GetDataFromWebService(String url, String cacheId, String partitionCode, String topic, XmlObjectDefinition objectDefinition, Int32 pageCounter, String previousTimeStampLastRecord, String callSafeNameOverrule, Int32& pageSize, Int32& count, String& timeStampLastRecord, Int64& documentSizeCharacters, Int64& documentSizeBytes)
   at Invantive.Data.ExactOnlineXmlProvider.<GetRowsDataFromWebService>d__232.MoveNext()
   at Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__2.MoveNext()
   at Invantive.Data.CachedEnumerable`1.TryGetNext(Int32 index, T& item)
   at Invantive.Data.CachedEnumerable`1.<GetEnumerator>d__6.MoveNext()
   at System.Linq.Enumerable.<SelectManyIterator>d__16`2.MoveNext()
   at Invantive.Sql.DataSourceOrFunctionTree.<GetDataFromDataContainer>d__50.MoveNext()
   at Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__2.MoveNext()
   at Invantive.Data.CachedEnumerable`1.TryGetNext(Int32 index, T& item)
   at Invantive.Data.CachedEnumerable`1.<GetEnumerator>d__6.MoveNext()
   at Invantive.Sql.FirehoseResultSet.<Iterator>d__57.MoveNext()
   at Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__2.MoveNext()
   at Invantive.Data.CachedEnumerable`1.TryGetNext(Int32 index, T& item)
   at Invantive.Data.CachedEnumerable`1.<GetEnumerator>d__6.MoveNext()
   at Invantive.Sql.FirehoseResultSet.<Iterator>d__57.MoveNext()
   at Invantive.Sql.FilterIterator.<Iterator>d__3.MoveNext()
   at Invantive.Sql.ChainedFirehose.<Iterator>d__11.MoveNext()
   at Invantive.Sql.SelectListIterator.<Iterator>d__7.MoveNext()
   at System.Linq.Enumerable.Count[TSource](IEnumerable`1 source)
   at Invantive.Sql.DdlStatementTree.OnExecute(ParameterList parameters, ExpressionParser expressionParser)
   at Invantive.Sql.QueryTree.Execute(ParameterList parameters, ExpressionParser expressionParser)
   at Invantive.Sql.QueryPlan.ForcePlan()
   at Invantive.Sql.QueryPlan.get_Fields()
   at Invantive.Sql.QueryPlan.get_FieldCount()
   at Invantive.Data.QueryPlanCacheEntry.<>c.<.ctor>b__12_0(IQueryPlan qp)
   at System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at Invantive.Data.QueryPlanCacheEntry..ctor(String sqlStatement, IQueryPlan[] queryPlans, ParameterList parameters)
   at Invantive.Sql.SqlEngine.ExecuteAndFetch(IProviderManager manager, String sqlStatement, ParameterList parameters, Boolean allowSelect)
   at Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(String actionSql, ParameterList parameters, String& handlingPath)
   at Invantive.Data.ConnectionManager.PassthroughSqlActionTable(String actionSql, ParameterList parameters)
   at Invantive.Data.ActionProceduresBase.PassthroughSqlActionTable(String actionSql, ParameterList parameters)
   at Invantive.Producer.QueryEngine.QueryEngine.ProcessStatement(String sqlStatement, ResultSet& resultSet, Action`1 fetchMoreRows, Action`7 executeStatement, Boolean isInInteractiveMode, ScriptingLanguageNextStepSpecification& nextStepSpecification)
   at Invantive.Producer.QueryEngine.QueryEngine.ProcessStatements(Action`1 fetchMoreRows, Action`7 executeStatement, IEnumerable`1 statements, ScriptingLanguageNextStepSpecification& exitSpecification)
   at Invantive.Producer.QueryEngine.QueryEngine.Run(String[] arguments)
   at Invantive.Producer.QueryEngine.Program.Main(String[] arguments)
   at Invantive.Data.ExactOnlineXmlProvider.GetDataFromWebService(String url, String cacheId, String partitionCode, String topic, XmlObjectDefinition objectDefinition, Int32 pageCounter, String previousTimeStampLastRecord, String callSafeNameOverrule, Int32& pageSize, Int32& count, String& timeStampLastRecord, Int64& documentSizeCharacters, Int64& documentSizeBytes) in File348:line 7573
   at Invantive.Data.ExactOnlineXmlProvider.<GetRowsDataFromWebService>d__232.MoveNext() in File348:line 6871
   at Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__2.MoveNext() in File275:line 3987
   at Invantive.Data.CachedEnumerable`1.TryGetNext(Int32 index, T& item) in File275:line 4163
   at Invantive.Data.CachedEnumerable`1.<GetEnumerator>d__6.MoveNext() in File275:line 4112
   at System.Linq.Enumerable.<SelectManyIterator>d__16`2.MoveNext()
   at Invantive.Sql.DataSourceOrFunctionTree.<GetDataFromDataContainer>d__50.MoveNext() in File54:line 420
   at Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__2.MoveNext() in File275:line 3987
   at Invantive.Data.CachedEnumerable`1.TryGetNext(Int32 index, T& item) in File275:line 4163
   at Invantive.Data.CachedEnumerable`1.<GetEnumerator>d__6.MoveNext() in File275:line 4112
   at Invantive.Sql.FirehoseResultSet.<Iterator>d__57.MoveNext() in File25:line 598
   at Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__2.MoveNext() in File275:line 3987
   at Invantive.Data.CachedEnumerable`1.TryGetNext(Int32 index, T& item) in File275:line 4163
   at Invantive.Data.CachedEnumerable`1.<GetEnumerator>d__6.MoveNext() in File275:line 4112
   at Invantive.Sql.FirehoseResultSet.<Iterator>d__57.MoveNext() in File25:line 598
   at Invantive.Sql.FilterIterator.<Iterator>d__3.MoveNext() in File24:line 93
   at Invantive.Sql.ChainedFirehose.<Iterator>d__11.MoveNext() in File22:line 82
   at Invantive.Sql.SelectListIterator.<Iterator>d__7.MoveNext() in File30:line 120
   at System.Linq.Enumerable.Count[TSource](IEnumerable`1 source)
   at Invantive.Sql.DdlStatementTree.OnExecute(ParameterList parameters, ExpressionParser expressionParser) in File55:line 195
   at Invantive.Sql.QueryTree.Execute(ParameterList parameters, ExpressionParser expressionParser) in File33:line 174
   at Invantive.Sql.QueryPlan.ForcePlan() in File32:line 89
   at Invantive.Sql.QueryPlan.get_Fields() in File32:line 66
   at Invantive.Sql.QueryPlan.get_FieldCount() in File32:line 77
   at Invantive.Data.QueryPlanCacheEntry.<>c.<.ctor>b__12_0(IQueryPlan qp) in File242:line 68
   at System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at Invantive.Data.QueryPlanCacheEntry..ctor(String sqlStatement, IQueryPlan[] queryPlans, ParameterList parameters) in File242:line 68
   at Invantive.Sql.SqlEngine.ExecuteAndFetch(IProviderManager manager, String sqlStatement, ParameterList parameters, Boolean allowSelect) in File37:line 538
   at Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(String actionSql, ParameterList parameters, String& handlingPath) in File74:line 4546
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(String actionSql, ParameterList parameters, String& handlingPath) in File74:line 4669
   at Invantive.Data.ConnectionManager.PassthroughSqlActionTable(String actionSql, ParameterList parameters) in File74:line 2487
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Invantive.Data.ConnectionManager.PassthroughSqlActionTable(String actionSql, ParameterList parameters) in File74:line 2501
   at Invantive.Data.ActionProceduresBase.PassthroughSqlActionTable(String actionSql, ParameterList parameters) in File64:line 134
   at Invantive.Producer.QueryEngine.QueryEngine.ProcessStatement(String sqlStatement, ResultSet& resultSet, Action`1 fetchMoreRows, Action`7 executeStatement, Boolean isInInteractiveMode, ScriptingLanguageNextStepSpecification& nextStepSpecification)

Allegedly this is a permissions issue for the current Exact user through the Exact XML APIs. This used to work before, but after disabling/enabling the user the StockPositions API is no longer reachable.

How should I configure the user to ensure that the StockPositions API is available?

Answer 1

The authorization model of Exact Online is not really suitable for complex security setups; it has no Role Based Access Control or a simple mapping from security settings to forms and APIs.

In this case, you are lucky. Since StockPositions is an XML API-based table, you can see in the web user interface of Exact Online whether the user has any privileges.

To check this:

• Log on as the user running the query using the same user name and password for Exact Online.
• Click on the name of the company top-left.
• Click on option Import/Export.
• Click on XML method.
• All APIs available are shown like this:
• For downloading, you should check whether the 'Export' option is shown. For uploading, check whether 'Import' is present.

When the privileges on the API are missing, a power user which can set up user privileges should open Exact Online.

Then:

• Click on Company
• Click on Master data
• Click on Users -> Overview
• Select the user.
• In the Monitor group click on Rights.
• You will see an extensive list of hundreds of check boxes:
• Each checkbox represents some privileges of the user on hand.
• In general, look at the tab for the main topic the user need access to and try the effects for the user. Or contact Exact support for advice.