什么会导致链接服务器返回有限数量的数据? [英] what would cause a linked server to return a limited amount of data?

问题描述

我们链接到 sql server 2012 实例的服务器出现问题.

We are having an issue with a server that we linked to our sql server 2012 instance.

我们通过 odbc 或 oledb 连接链接的服务器是 Pervasive SQL.

The server that we linked through an odbc or oledb connection is Pervasive SQL.

只要结果集很小，就可以通过这种方式从链接服务器中进行选择:

select * from linked_server.database..mytable

如果结果集超过 2mb(可能略高或略低)，我们就会得到这个错误:

If the result set is more than around 2mb (might be slightly more or less) then we get this error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "KSLAP208" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "KSLAP208".

然而，愚蠢的是，例如 SSMS 2012 显示所有需要返回的列，然后立即显示错误.

However, the silly thing is that SSMS 2012 for instance displays all the columns that need to be returned and then immediatley errors out.

什么会导致 SSMS 2012 在过大的结果集上产生错误?(超过 1-2mb)??

这是我在 odbc 管理员中看到的:

here's what i see in the odbc administrator:

推荐答案

选项 1，禁用预取

有时，禁用预取可以解决此类问题.

Option 1, disable pre-fetch

Sometimes, disabling pre-fetch will solve problems like this.

您可以通过清除启用数据预取"来禁用预取查询"创建 DSN 或添加PREFETCH=0"创建无 DSN 连接时的连接字符串.

You can disable the prefetch by clearing the "Enable pre-fetch of data for queries" check box in the Performance tab when creating a DSN or adding "PREFETCH=0" to the connection string when creating a DSN-less connection.

更多相关信息:

在服务器上的 ODBC 数据源管理器面板中，配置来自 System DSN 的链接服务器.在性能选项卡下，取消选中为查询启用预取数据"框.好的好的.删除和在 SQL Management Studio 中重新创建链接服务器.

In the ODBC Data Source Administrator panel on the server, configure your linked server from System DSN. Under the Performance tab, uncheck the box 'Enable pre-fetch of data for queries'. OK, OK. Delete and re-create your linked server in SQL Management Studio.

来源

• 转到链接服务器
• 供应商
• MSOLAP(或类似)
• 属性
• 确保Allow inprocess"已启用.就算是，也要关掉.节省.然后重新打开它.有时重置此设置会有所帮助.

这篇关于什么会导致链接服务器返回有限数量的数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！