2

MSSQL 2016 link to a Pervasive server. It can see the DBs on the Pervasive database, and tables but when you do a select 

SELECT *
        FROM 
        OPENQUERY(LinkedServer, 'SELECT * FROM DB.Table')

I get Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for linked server "LinkedServer" 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 "LinkedServer". I checked MSDASQLprovider allows in line process, and port. Still nothing Thoughts folks. ??

JeffS
  • 21
  • 1
  • 3
  • What version of Pervasive are you using? Are you using a 64 bit ODBC driver for Pervasive? MSSQL 2016 is 64 bit only. Pervasive had multiple versions including some that were 32 bit only. – mirtheil Jul 31 '18 at 18:45
  • I tried 64 bit client and the 32 bit. The link sees the Catalogs, and DB and tables. So it's connecting to the server. The issue is when you issue a SELECT you get that error. I have tried various settings for the MSDASQL Provider . Allow Leading zeros and Allow In process are checked. – JeffS Aug 01 '18 at 11:01
  • Do all SELECT statements fail? Are you able to issue a SELECT from the Pervasive Control Center or other ODBC tool? – mirtheil Aug 01 '18 at 11:16
  • Also when I right-click and select Script table as SELECT I get . "contains no columns that can be selected or the current user does not have permissions on that object." – JeffS Aug 01 '18 at 11:32
  • 1
    Yes, the Control center works fine. Just added the Link to my local SQL Server which is SQL Server 2008 R2 and all works So, ... it seems its a 2016 issue – JeffS Aug 01 '18 at 12:08

1 Answers1

0

Had this issue when upgrading SQL Server from SQL2014 to SQL2016 or later. Also if you install it on a new machine. The root cause is that the Microsoft OLE DB Provider for ODBC Drivers has changed. It now has more ISO and other ODBC features supported. This is a breaking change for some ODBC connections.

This driver (MSDASQL) is used to wrap an ODBC connection on the server, in this case, to the pervasive database and allow it to act as a linked server. The wrapper allows both OPEN Query functionality as well as standard linked query calls.

The issue with the change is that the new wrapper is sending extra data that is not being handled if you are using ODBC driver versions 11 or below for query activity. It can not handle the additional information and will return an error on execution. It will be able to connect, however. I could see all of the tables but couldn't query them.

Version 12 of the pervasive ODBC driver will work for query only, but Open Query will not work. You will continue to get "unexpected catastrophic failure" on open query updates.

I recommend you upgrade to version 13.3. This release from Actian will support both selects and updates and will solve the Open Query error. I do not know if you will need support from Actian for the licensing of the driver.

Stradas
  • 1,768
  • 16
  • 17