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

Question

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?

Suggest that you address this to the DBA SE group for better response. — mdpc, Feb 08 '13 at 20:21
Is the problem related to the number of columns you select or to one particular, problematic data type? IOW, using your example, perhaps the driver has a problem with the data type of field3 and SELECT field1,field2,field4 ... might work. Does SELECT field3 by itself work OK? — Darin Strait, Feb 10 '13 at 16:30
@darin that is a wonderful point; however, after testing this, i am confident that it is not a data type issue — Alex Gordon, Feb 10 '13 at 19:50
@EvanAnderson im glad you feel my pain. what a piece of crap. not to say that i would be able to do any better, but it's frustrating that any application would pick pervasive to be their rdbms provider. you have to be a complete moron — Alex Gordon, Feb 10 '13 at 20:05
We'll just say that I've had some bad experiences w/ Pervasive (and the old versions when it still went by BTRIEVE). Have you tried the problematic query using the native Pervasive "Control Center" yet? That'll tell you if the "relational engine" is even capable of fulfilling the query. — Evan Anderson, Feb 10 '13 at 20:06
@EvanAnderson great point. yes, actually i was able to run the query from sql server 2005 32 bit (using pervasive as linked server).; however with the same exact configuration from sql server 2012 64 bit, NO LUCK — Alex Gordon, Feb 10 '13 at 20:10
im not going to say who it was, but i called tech support at a company that uses pervasive for one of their software products. i requested help getting connected to the db, and tech support guy, barely understandable, shoving a sandwhich in his mouth, was joking around with a colleague while making it look like he was providing valuable support to me. — Alex Gordon, Feb 10 '13 at 20:16
I see that you've got a couple of questions about this on Stack Overflow, too. The things that you're getting told there are the things that I'd tell you, too. Assuming those things aren't working I strongly suspect you're hitting a bug in the Pervasive ODBC driver. — Evan Anderson, Feb 10 '13 at 20:24
@EvanAnderson its an interesting point; however, im aware that other companies are using the driver without a problem (at least so they say) — Alex Gordon, Feb 10 '13 at 20:27
Another troubleshooting trick I use is to write a small program that I can run directly on the server (using vbscript or powershell; there are many examples to be found through google) that uses the driver to see if I can run the same query outside of the SQL Server Linked Server scheme. Sometimes, I find that the problems are login-related (ie. my account versus the SQL Server service account), sometimes the error messages are a little more detailed. — Darin Strait, Feb 11 '13 at 21:28
@darinstrait when you have a moment could you hook me up with an example — Alex Gordon, Feb 12 '13 at 22:06
1st hit from google: http://social.technet.microsoft.com/Forums/da/winserverpowershell/thread/49a06b9c-8317-4c27-ab23-356138cf8caf His powershell script uses System.Data.Odbc and tries whatever connection string you provide. You need to replace the values of the $connectstring and $sql variables with something that fits your situation. The $sql variable is up to you. Getting $connectionstring is a little trickier. http://www.connectionstrings.com/pervasive gives a "standard" Pervasive ODBC string as Driver={Pervasive ODBC Client Interface};ServerName=myServerAddress;dbq=@dbname; — Darin Strait, Feb 13 '13 at 13:29
@EvanAnderson - I didn't see this question till just now, but I still have Pervasive btrieve 8 databases :( Long live Btrieve! — Mark Henderson, Jun 17 '13 at 02:31
@АртёмЦарионов - hope not; we only have a very small number of them left. I checked to see if they were ISAM or SQL, but they're ISAM and there's no ODBC support, so I can't help with your problem here (which was what made me remember I even had these databases anyway). — Mark Henderson, Jun 17 '13 at 03:40

score 2 · Answer 1 · edited Jun 11 '20 at 10:02

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...

TTT · Answer 2 · 2013-02-14T05:09:44.480

Does this work: SELECT TOP 1 field1,field2,field3....field15.
If yes, how about SELECT TOP 10 field1,field2,field3....field15.
If yes, how about SELECT TOP 100 field1,field2,field3....field15.
Repeat, etc.

Assumming it breaks at some point, add an ORDER BY clause for a column (or set of columns) that are unique. Repeat and adjust the number of rows until you pinpoint the culprit. Say for example, SELECT TOP 12345 works but SELECT TOP 12346 does not. (A good ORDER BY is important here to make sure it returns the same set of data every time.) Now use a WHERE clause range to grab just a small amount of data that appears in the bottom portion of your "good" data, then increase the WHERE range a bit to include the bad row. If it works now, then this points to the amount of data, which at least proves your original guess, and if it still errors out, go look at the row that would be 12346 in that query and see if there's anything funky with it.

I can elaborate if it turns out this gets you somewhere.

score 1 · Answer 3 · edited Jun 17 '13 at 01:10

I know this question was asked long ago, but for people Google-ing and come across this post...

I assume the query is golden when you run it on the server you are linked to?

What is your exact query?

Are you doing:

Select * from "the linked server"

-or-

Select * from openquery("the linked server",'Select * from "the table"')

There is a difference:

The first pulls ALL the records from the remote server and then does the query on your local server.
The second runs the query on the remote server first. The remote server only sends back the result set.

There is a possibility of timing out but I don't think that is the issue you are having.

I ran into an issue doing a linked server to mysql. I tried #1 above and I seem to remember getting the same non-helpful error. It turned out, doing #1 only works (or works best) with other SQL servers. When linked to a non-SQL server, you have to use #2 (openquery).

very nice addition! thanks a lot i didnt know this – Alex Gordon Jun 17 '13 at 01:50 — Alex Gordon, Jun 17 '13 at 01:50

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

3 Answers3