灾难性尝试从链接服务器中选择失败 [英] catastrophic failure trying to select from linked server

问题描述

我已经从SQL SERVER 2012创建了到Pervasive SQL的链接oledb/odbc连接:

I have created a linked oledb/odbc connection to Pervasive SQL from SQL SERVER 2012:

USE [master]
GO

/****** Object:  LinkedServer [KSLAP208]    Script Date: 2/8/2013 10:38:55 AM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'KSLAP208', @srvproduct=N'Pervasive ODBC Interface', @provider=N'MSDASQL', @datasrc=N'C003', @location=N'localhost'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'KSLAP208',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N'KSLAP208', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'KSLAP208', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'KSLAP208', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'KSLAP208', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'KSLAP208', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'KSLAP208', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'KSLAP208', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'KSLAP208', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'KSLAP208', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'KSLAP208', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'KSLAP208', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'KSLAP208', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'KSLAP208', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

测试连接成功.

但是，当我尝试从数据库中选择时:

However, when I try to select from a database:

select * from [KSLAP208].[C003]..PA_Profile_BASE_1119 

我立即获得返回的字段名称，然后立即收到此错误:

I immdiately get just the field names returned and then immediately after that I 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".

我做错了什么?为什么我不能选择?我可以在受限制的服务器上看到所有数据库和表.

What am I doing wrong? Why Can I not select? I am able to see all the databases and tables on the linnked server.

如果我选择少量数据，则选择field1，field2可以正常工作.

推荐答案

我想我记得当我创建一个Postgresql链接服务器时这是一个问题.我认为您可能需要将其设置为false来重新创建链接服务器(或只需在链接服务器属性->服务器选项中对其进行更改):

I think I remember this being an issue when I created a postgresql linked server. I think you may need to recreate the linked server with this set to false (or just change it in the linked server properties->server options):

EXEC master.dbo.sp_serveroption @server=N'KSLAP208', 
    @optname=N'remote proc transaction promotion', @optvalue=N'false'

此外，尝试使用 OPENQUERY 对链接运行此操作

Additionally, try using OPENQUERY to run this against the link

SELECT * 
FROM OPENQUERY(KSLAP208,'SELECT * FROM PA_Profile_BASE_1119');

这篇关于灾难性尝试从链接服务器中选择失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！