if I select more than 15 fields, I get catastrophic failure

l–''''''———'''''''''''' asked:

Using sql server 2012 I am connecting to a linked server with Pervasive SQL on it.

When I do select * or select field1,field2,field3....field15 I am getting 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".

I feel like there is some kind of memory issue? It will not allow me to select more than a certain amount of data?

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

What am I doing wrong?

My answer:

A post on Microsoft’s web site suggests that you can work around this problem by disabling query prefetching for the linked server.

In the ODBC Data Source Administrator panel on the server, configure your linked server from System DSN. Under the Performance tab, uncheck the box ‘Enable pre-fetch of data for queries’. OK, OK. Delete and re-create your linked server in SQL Management Studio.

You can disable the prefetch by clearing the “Enable pre-fetch of data for queries” check box in the Performance tab when creating a DSN or adding “PREFETCH=0” to the connection string when creating a DSN-less connection.

Since I don’t have access to Pervasive’s download area, (and SQL Server’s ODBC connection looks completely different on my Server 2012 installation) I wasn’t able to test this. It still might help you anyway…

View the full question and any other answers on Server Fault.

Creative Commons License
This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.