what would cause a linked server to return a limited amount of data?
Option 1, disable pre-fetch
Sometimes, disabling pre-fetch will solve problems like this.
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.
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.
Option 2, Allow inprocess
- Go to Linked Servers
- Providers
- MSOLAP (or similar)
- Properties
- Make sure "Allow inprocess" is enabled. Even if it is, turn it off. Save. And then turn it back on. Sometimes resetting this can help.
Alex Gordon
Check out my YouTube channel with videos on Azure development.
Updated on July 02, 2022Comments
-
Alex Gordon about 2 years
We are having an issue with a server that we linked to our sql server 2012 instance.
The server that we linked through an odbc or oledb connection is Pervasive SQL.
Selecting from the linked server this way works as long as the result set is small:
select * from linked_server.database..mytable
If the result set is more than around 2mb (might be slightly more or less) then we get 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".
However, the silly thing is that SSMS 2012 for instance displays all the columns that need to be returned and then immediatley errors out.
What would cause SSMS 2012 to produce an error on a result set that is too big? (more than 1-2mb)??
here's what i see in the odbc administrator:
-
Alex Gordon over 11 yearsis that the same thing as array fetch?
-
Alex Gordon over 11 yearsi just disabled array fetch and got the same result
-
Brian Webster over 11 yearsadded a bit more detail about disabling pre-fetch
-
Alex Gordon over 11 yearsbrian thank you again for such a thorough answer. i've added a picture to my question
-
Brian Webster over 11 yearsI'm at a loss, but maybe my answer will help others with a similar problem. If I were in your shoes, I would setup another database server and install SQL Server Express R2 on it and hopefully a different driver for the linked server. This may seem like a lot of trouble, but it really won't take you that long, and you can rule out an awful lot of suspects by doing this (whether it works or not). As you probably know, fixing problems like this is about iteratively removing half of the suspects. Eventually you are left with only one. Good luck!
-
Alex Gordon over 11 yearsthank you brian. i turned off ALLOW IN PROCESS and got this error OLE DB provider "MSDASQL" for linked server "KSLAP208" returned message "Specified driver could not be loaded due to system error 1114: A dynamic link library (DLL) initialization routine failed. (Pervasive ODBC Interface, C:\Program Files\Pervasive Software\PSQL\bin\odbcci64.dll).". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "KSLAP208".
-
Alex Gordon over 11 yearsalso we have a sql server 2005 that uses the same linked server without a problem
-
Brian Webster over 11 yearsThat's good, I mention in my answer that you need to turn it off then turn it back on. Sometimes doing this will cause the problem to go away. I'm not sure why, but some have had luck with it.
-
Alex Gordon over 11 yearsyes i have also read this, i did turn it back on and started to get the original error.
-
Brian Webster over 11 yearsWell, at least we have ruled things out. Sorry this didn't fix it in your case. If you have time, I do recommend attempting what I mentioned in a previous comment regarding a new temporary server and separate driver (if possible)
-
Alex Gordon over 11 yearswhat do you mean by separate driver>