Home / server / Questions / 477003
In Process
Alex Gordon
Asked: 2013-02-09 11:28:57 +0800 CST

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

  • 772

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?

sql-server

3 Answers

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

    • 2

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

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