I have created a linked server on SQL Server 2008 instance using Provider Advantage OLE DB to connect to a database consisting on .DBF files. I right-clicked and hit test connection and it succeeded. I also tested the connection using .UDL and it worked. But when I try to access a .dbf file using the query it returns empty results
Select * from OpenQuery(DBFFILESERVER, 'SELECT * from MyTable')
It is throwing the following message
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Advantage.OLEDB.1" for linked server "DBFFILESERVER" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "Advantage.OLEDB.1" for linked server "DBFFILESERVER".
The same DBF file Database can be accessed using Advantage OLE from a SQL Server 2005 instance that we have on a different server (windows 2003). I am just trying to set up an environment on our new Windows 2008 Server but facing this issue.