UPDATE to linked server (Pervasive Database) is failing using OPENQUERY

Question

I have 2 database servers:

ServerA : Windows Server 2012 R2 Standard, SQL Server 2014 (SP3) (KB4022619) Standard Edition.

ServerB : Windows Server 2016 Datacenter, SQL Server 2016 (SP2-CU6) (KB4488536) Enterprise Edition

Both are x64 servers located at different DC, under different domain.

Purpose:

We are planning to migrate production databases from ServerA to ServerB.

Action Taken: Prior to migrating production database, I wanted to validate it on development environment first.So,

All of the databases, linked server (with similar settings), logins etc. have been copied properly from ServerA to ServerB.

Installed ODBC driver of Pervasive database on ServerB (similar to ServerA).

Exported all the system DSNs from ServerA to ServerB.

Updated MSDTC settings on ServerB (similer to ServerA).

Note: linked server is referencing to pervasive database.

Problem Facing;

After all these users are able to login, databases are accessible, reports are running and the server (ServerB) is performing well, users are able to SELECT tables from Pervasive database using linked server.

But the problem is, "users are not able to execute update on pervasive database using the linked server on ServerB.

The update queries 1st & 2nd are failing with same error:

--1------------------------------------------------------------
UPDATE OPENQUERY (LinkedServerName, 'SELECT Column_1,Column_2,Column_3,Column_4 FROM TableName WHERE Column_3 = 5910 AND Column_4 = 2')
SET Column_1='2019-02-01',Column_2='2019-07-01';
--2------------------------------------------------------------
UPDATE OQ
SET Column_1='2019-02-01',Column_2='2019-07-01'
FROM OPENQUERY (LinkedServerName, 'SELECT * FROM TableName') OQ
WHERE Column_3 = 5910 AND Column_4 = 2;

Error1:

Msg 7399, Level 16, State 1, Line 4
The OLE DB provider "MSDASQL" for linked server "LinkedServerName" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7343, Level 16, State 4, Line 4
The OLE DB provider "MSDASQL" for linked server "LinkedServerName" could not UPDATE table "[MSDASQL]". Unknown provider error.

Error for 3rd query is quit different.

UPDATE LinkedServerName.DBName..TableName
SET Column_1='2019-02-01',Column_2='2019-07-01'
WHERE Column_3 = 5910 AND Column_4 = 2;

Error 2:

Msg 7399, Level 16, State 1, Line 26
The OLE DB provider "MSDASQL" for linked server "LinkedServerName" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7343, Level 16, State 4, Line 26
The OLE DB provider "MSDASQL" for linked server "LinkedServerName" could not UPDATE table "[LinkedServerName].[Database]..[TableName]". Unknown provider error.

Same query is executing successfully on ServerA.

Solution will be appreciated.

Thanks!

Answer 1

This issue is caused by upgrading SQL Server from SQL2014 to SQL2016 or later. The Microsoft OLE DB Provider for ODBC Drivers has changed. It now has more ISO and other ODBC features support. 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.

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.