1

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!

  • Because the Linked Server call isn't returning a complete error message, have you tried executing the UPDATE statement directly on the Pervasive database? Does that work? Does it give an error? – mirtheil Jul 5 at 11:31
  • 1
    @mirtheil Same update statement is executing successfully on ServerA. – Rajesh Ranjan Jul 5 at 11:38
  • Is that a valid update statement? have you tried using 4 part naming? <servername>.<database>.<schema>.<object> – Jonathan Fite Jul 5 at 13:01
  • @JonathanFite Yes I tried that. It failing with similar error referencing to the object name. I'm going to update my question. – Rajesh Ranjan Jul 5 at 13:05
  • If the exact same query works and accesses the exact same Pervasive server and database from Server A but fails on Server B, then the problem is most likely a configuration error on Server B. I have seen issues with SQL 2016 and Linked Servers (stackoverflow.com/questions/51619635/…) – mirtheil Jul 5 at 13:14
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.

  • 1
    Thanks! @Stradas. It sold the problem – Rajesh Ranjan Jul 15 at 17:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.