Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
Suggest that you address this to the DBA SE group for better response. –  mdpc Feb 8 '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 –  Yuck Feb 10 '13 at 19:50
2  
Check the logs on KSLAP208. –  Falcon Momot Feb 10 '13 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 –  Yuck Feb 10 '13 at 20:05
show 13 more comments

3 Answers 3

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
    
very nice addition! thanks a lot i didnt know this –  Yuck Jun 17 '13 at 1:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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