从Exact Online检索联系人时出现错误 [英] Getting error when retrieve contacts from Exact Online

问题描述

我想从精确在线"中检索一家公司的所有联系人，并收到以下错误:

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

select * from AccountContacts

错误:

itgenusg026:不支持请求的3.308列数.将请求的列数限制为最多1.000列.

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

推荐答案

错误'不支持请求的3.308列数.将请求的列数限制为最多1.000列."当数据库的结果集具有超过1.000列时，Invantive Query Tool会引发该错误.

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:

• 选项1:使用旧的Exact Online(XML)提供程序确定列列表.
• 选项2:在表名称上使用F4(描述列).
• 选项3:在网格工具栏中使用显示/隐藏空列".

此问题的原因是Exact Online的XSD描述了所有可能的输出格式.但是实际上，有时甚至数十万个可能的字段中最多只能返回200个.没有文档说明在某些情况下哪些字段可用，因此SQL开发人员只需要分析相关字段及其值的输出即可.

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.

类似的问题也出现在其他ERP平台上，例如Twinfield或其他具有广泛XSD且在XSD中各个级别的节点之间具有引用的其他东西.

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.

选项1要求您首先使用旧的Exact Online(XML)提供程序登录.它使用 Invantive SQL语法版本中所述的Invantive SQL v1.此SQL版本不完全与ANSI兼容.如果为空，则从结果集中删除没有值的字段.这样会将字段数减少了约1.000.

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.

然后，您只需在单元格上单击鼠标右键或使用导出格式"SQL select"复制字段名称，然后将查询中的"*"替换为相关字段即可.

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.

之后，您可以切换回XML和REST API的新的Exact Online组合提供程序，然后继续工作.

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

或者，您也可以获取可用字段的列表.这些在数据字典视图systemtablecolumns中可用.或者，将光标放在表名称上并按F4或从编辑器"菜单中选择描述"后，会在弹出窗口中显示更多用户友好信息:

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:

作为最后一种选择，您可以指示网格仅显示某些行具有非空值的列.结果网格具有一个如下所示的按钮.请记住在运行查询之前将其激活.

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.

这篇关于从Exact Online检索联系人时出现错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！