catastrophic failure trying to select from linked server

Question

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

Test Connection is succesful.

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.

if I select a small amount of data select field1,field2 it works without problems.

Is your sql server 32 bit or 64 bit? What bit is the pervasive server? — Taryn, Feb 11 '13 at 00:15
@bluefeet the pervasive is 64 bit and the sql server 2012 is 64 whereas another sql server 2005 that had no problems adding pervasive as linked server is 32 bit\ — Alex Gordon, Feb 11 '13 at 02:49

score 14 · Accepted Answer · answered Feb 08 '13 at 18:57

14

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'

Additionally, try using OPENQUERY to run this against the link

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

answered Feb 08 '13 at 18:57

swasheck

4,644
2
29
56

You may also need to ensure that you're using the correct drivers, and this assumes that you've linked to the correct database. – swasheck Feb 08 '13 at 18:58
so it looks like select individual fields works but i cannot select * – Alex Gordon Feb 08 '13 at 19:11
can you select all columns (essentially select * without the *)? what is the error that is given? – swasheck Feb 08 '13 at 20:09
no i cannot i get this error when i select more than around 15 fields: The provider reported an unexpected catastrophic failure – Alex Gordon Feb 08 '13 at 20:12
sql server error logs showing: SqlDumpExceptionHandler: Process 55 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. * Access Violation occurred reading address 0000000009400090 * Input Buffer 128 bytes - – Alex Gordon Feb 08 '13 at 20:13
1

That's starting to sound a lot like a driver issue. – Data Masseur Feb 08 '13 at 21:20
In general if you get an access violation I would expect a bug in some binary module somewhere, and drivers would be a major suspect. – Chris Travers Feb 15 '13 at 15:49
@ChrisTravers I'm almost certainly in agreement that there's something going on at the driver level given the thread [here](http://dba.stackexchange.com/questions/34436/selecting-more-fields-causes-catastrophic-failure). – swasheck Feb 15 '13 at 15:57

Gordon Linoff · Answer 2 · 2013-02-08T19:15:55.810

2

When I access remote tables, I need to have complete 4-part naming. Try this:

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

I've never investigated why. I've just gotten in the habit of including all parts.

You can get the list of columns using:

select column_name
from [KSLAP208].[C003].INFORMATION_SCHEMA.COLUMNS
where table_name = 'PA_Profile_BASE_1119'

(and schema_name = whatever if you need that).

edited Feb 08 '13 at 19:15

answered Feb 08 '13 at 18:47

Gordon Linoff

1,242,037
58
646
786

Msg 7312, Level 16, State 1, Line 1 Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "KSLAP208". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema. – Alex Gordon Feb 08 '13 at 18:48
so it looks like select individual fields works but i cannot select * – Alex Gordon Feb 08 '13 at 19:11
That is really interesting. I can definitely `select *`, but need the schema. Different security settings somewhere along the way. – Gordon Linoff Feb 08 '13 at 19:14
i think it might be a memory issue. once i start selecting more than around 15 fields, it gives that erro msg – Alex Gordon Feb 08 '13 at 19:20

score 1 · Answer 3 · answered Feb 08 '13 at 18:47

1

Cannot answer why but you could try like this;

--link server and login
EXEC master.sys.sp_addlinkedserver N'KSLAP208',N'SQL Server';
EXEC master.sys.sp_addlinkedsrvlogin @rmtsrvname='KSLAP208', 
                  @useself='false',
                  @rmtuser='username',
                  @rmtpassword='password';

        --DO YOUR JOB HERE 
        SELECT  TOP (10) * FROM  [KSLAP208].dbName.dbo.[tableName]

--drop server link and login
EXEC sp_droplinkedsrvlogin 'KSLAP208', NULL
EXEC sp_dropserver 'KSLAP208', NULL;

answered Feb 08 '13 at 18:47

Kaf

33,101
7
58
78

Honestly, I haven't tried that but here is a [similar question](http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/afcb16e8-c0d3-4f9f-8ea7-b8ee06bb779f) and answer to it. – Kaf Feb 08 '13 at 18:51
thank you again. the thing is my connection seems to be working i just cant select, but i will follow the lllink! – Alex Gordon Feb 08 '13 at 18:53
Also some details about `Windows authentication` mode in the [Remarks Section](http://msdn.microsoft.com/en-us/library/ms189811.aspx) – Kaf Feb 08 '13 at 18:57
so it looks like select individual fields works but i cannot select * – Alex Gordon Feb 08 '13 at 19:13
I checked `select * ...` works for me using this method. but I'm *not* on windows authentication mode. – Kaf Feb 08 '13 at 19:17
i think it might be a memory issue. once i start selecting more than around 15 fields, it gives that erro msg – Alex Gordon Feb 08 '13 at 19:20

catastrophic failure trying to select from linked server

3 Answers3

Linked