3

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?

18
  • Suggest that you address this to the DBA SE group for better response.
    – mdpc
    Feb 8, 2013 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? Feb 10, 2013 at 16:30
  • @darin that is a wonderful point; however, after testing this, i am confident that it is not a data type issue Feb 10, 2013 at 19:50
  • 2
    Check the logs on KSLAP208. Feb 10, 2013 at 20:00
  • 2
    @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 Feb 10, 2013 at 20:05

3 Answers 3

2

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

1
+250

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.

1

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:

  1. The first pulls ALL the records from the remote server and then does the query on your local server.
  2. 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).

1
  • very nice addition! thanks a lot i didnt know this Jun 17, 2013 at 1:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .