Getting error when retrieve contacts from Exact Online

Question

I want to retrieve all contact persons from one company in Exact Online and get the following error:

select * from AccountContacts

Error:

itgenusg026: The requested number of 3.308 columns is not supported. Restrict the number of requested columns to at most 1.000 columns.

Type: Invantive.Configuration.ValidationException
   bij Invantive.Configuration.ValidationException..ctor(String errorCode, String errorMessage, String kindRequest, String localStackTrace, String nk, Exception innerException)
   bij Invantive.Producer.Windows.Forms.UltraGridExtensionMethods.AddColumnsToDataGrid(UltraDataColumnsCollection dataBandColumns, ResultSet results, Dictionary`2& columnNameIdMap, String[] excludedColumnNames, Func`2 allowEdit)
   bij Invantive.Producer.Windows.Forms.QueryTool.ExecuteStatement(IProgressNotifier notifier, String statement, ParameterList bindVariables, Boolean showResultsInGrid, Boolean showStatistics, Boolean memorizeStatisticsInSqlHistory, Boolean allowPaging)
   bij Invantive.Producer.Windows.Forms.QueryTool.FetchResultsFromSql()
   bij Invantive.Producer.Windows.Forms.QueryTool.<>c__DisplayClass107_0.<FetchData>b__0()
   bij System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   bij System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   bij System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   bij System.Threading.ThreadHelper.ThreadStart()
   bij Invantive.Producer.Windows.Forms.UltraGridExtensionMethods.AddColumnsToDataGrid(UltraDataColumnsCollection dataBandColumns, ResultSet results, Dictionary`2& columnNameIdMap, String[] excludedColumnNames, Func`2 allowEdit) in File969:regel 368
   bij Invantive.Producer.Windows.Forms.QueryTool.ExecuteStatement(IProgressNotifier notifier, String statement, ParameterList bindVariables, Boolean showResultsInGrid, Boolean showStatistics, Boolean memorizeStatisticsInSqlHistory, Boolean allowPaging) in File948:regel 2847
   bij Invantive.Producer.Windows.Forms.QueryTool.FetchResultsFromSql() in File948:regel 2437

Answer 1

The error 'The requested number of 3.308 columns is not supported. Restrict the number of requested columns to at most 1.000 columns.' is raised by the Invantive Query Tool when the result set from the database has more than 1.000 columns.

The grid used becomes extremely slow when there are multiple thousands of columns.

However, you still will want to see the contents. There are currently three options:

• Option 1: determine the column list using the old Exact Online (XML) provider.
• Option 2: use F4 (Describe columns) on the table name.
• Option 3: use 'Show/Hide empty columns' in the grid tool bar.

The reason of this problem is that the XSD of Exact Online describes all possible output formats. But in facts only at most 200 of the sometimes hundred thousands of possible fields are ever returned. There is no documentation what fields are available in some scenarios, so a SQL developer just needs to analyze the output for the relevant fields and their values.

Similar problems also occur for other ERP platforms such as Twinfield or anything else with an extensive XSD with references between nodes at various levels in the XSD.

Option 1: determine column list

Option 1 requires you to first log on using the old Exact Online (XML) provider. It uses Invantive SQL v1 as described on Invantive SQL grammar versions. This SQL version is not completely ANSI compatible; when empty it removes fields from the result set which have no value. This cuts down the number of fields by approximately a factor of 1.000.

Then you just copy the field names using right click on the cell or using export format 'SQL select' and replace the '*' in the query by the relevant fields.

After that you can switch back to the new Exact Online combined provider for XML and REST APIs and continue working.

Option 2: use F4 (Describe columns) on the table name.

As an alternative, you can also get a list of available fields. These are available in the data dictionary view systemtablecolumns. Or more user friendly in the pop up you get after positioning the cursor on the table name and pressing F4 or choosing 'Describe' from the Editor menu:

Option 3: use 'Show/Hide empty columns' in the grid tool bar.

As a last alternative, you can instruct the grid to only shown columns where some row has a non-empty value. The results grid has a button as shown below for that. Remember to activate it before running the query.