catastrophic failure trying to select from linked server
Solution 1
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');
Solution 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).
Solution 3
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;
Alex Gordon
Check out my YouTube channel with videos on Azure development.
Updated on June 29, 2022Comments
-
Alex Gordon almost 2 years
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.
-
Alex Gordon over 11 yearsMsg 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.
-
Kaf over 11 yearsHonestly, I haven't tried that but here is a similar question and answer to it.
-
Alex Gordon over 11 yearsthank you again. the thing is my connection seems to be working i just cant select, but i will follow the lllink!
-
Kaf over 11 yearsAlso some details about
Windows authentication
mode in the Remarks Section -
swasheck over 11 yearsYou may also need to ensure that you're using the correct drivers, and this assumes that you've linked to the correct database.
-
Alex Gordon over 11 yearsso it looks like select individual fields works but i cannot select *
-
Alex Gordon over 11 yearsso it looks like select individual fields works but i cannot select *
-
Alex Gordon over 11 yearsso it looks like select individual fields works but i cannot select *
-
Gordon Linoff over 11 yearsThat is really interesting. I can definitely
select *
, but need the schema. Different security settings somewhere along the way. -
Kaf over 11 yearsI checked
select * ...
works for me using this method. but I'm not on windows authentication mode. -
Alex Gordon over 11 yearsi think it might be a memory issue. once i start selecting more than around 15 fields, it gives that erro msg
-
Alex Gordon over 11 yearsi think it might be a memory issue. once i start selecting more than around 15 fields, it gives that erro msg
-
swasheck over 11 yearscan you select all columns (essentially select * without the *)? what is the error that is given?
-
Alex Gordon over 11 yearsno i cannot i get this error when i select more than around 15 fields: The provider reported an unexpected catastrophic failure
-
Alex Gordon over 11 yearssql 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 -
-
Data Masseur over 11 yearsThat's starting to sound a lot like a driver issue.
-
Chris Travers over 11 yearsIn general if you get an access violation I would expect a bug in some binary module somewhere, and drivers would be a major suspect.
-
swasheck over 11 years@ChrisTravers I'm almost certainly in agreement that there's something going on at the driver level given the thread here.