OPENROWSET using Visual FoxPro Provider error 'Could not fetch a row'

OPENROWSET using Visual FoxPro Provider error 'Could not fetch a row'

• kdub98

  Just to close this thread,

  We eventually got microsoft involved and it seems the ole*.dlls in system32 got unregistered for some reason. Registering them with regsvr32 solved the problem.

  
  

• gurpreet11

  Hi Cindy,

  Thanks for replying.

  We are running the VFPOLEDB Provider 8 on the server, i see that the latest version is 9, but i cannot implement that without testing. We have a huge client base all running on version 8.0 without any problems. It is only these 2 clients both running the Win 2k Server and SBS 2000.

  The DBF has not been corrupted, if I copy the DBF to my notebook I can open it 100% using openrowset and the foxpro 2.6 driver. I've copied a DBF from my notebook to the server and the same error appears.

  1. It is happening with all of the tables.

  Wierd thing is that if the DBF is empty and I call openrowset on it - in Query Analyzer - I get no errors at all. It returns nothing. But if the table, any table, has 1 or more rows then the error described above appears.

  2. I setup a linked server, which was setup successfully, but when querying the linked server the same error is returned. Even when trying to view the tables under the linked server in Enterprise manager the same message will be displayed.

  3. I have created a simple 2 column dbf in excel of type DBase 4, containing 2 rows of data. The same error occurs.

  Something else that I have been considering, is the possibilty of permissions on the temp directory which I'm sure SQL Server is using when querying the Foxpro tables. I have had the client look at the environment variable for %Temp% and it points to a valid path, however when entering %temp% in Windows explorer, the path becomes the path specified but has either a \2 or a \3 (both clients exhibit same issue). What I mean by this is that if the %temp% path in the environment variables says "C:\Temp", when I enter %temp% in Windows explorer the path is "C:\Temp\2" or "C\Temp\3". Is this something of concern SQL Server is using the %TMP% environment variable. We have just found this on a test machine, and it takes a reboot before SQL Server sees it. However unlike the production server, the test machine is not adding the \2 or \3 to the path.

  Any other ideas

  
  

• bbossi

  Hi Quintesv,

  I would use the VFP9 OLE DB data provider since there are some issues with the version 8 one that are corrected in the VFP9 provider.

  The account that SQL Server runs under (sometimes "local system account") needs to have permissions to the directory where the DBFs reside.

  Very strange that the temp directory has the "\2" or "\3" added to it. I've never seen anything like that. You'd think that with a simple path like C:\Temp with no spaces and less than 8 characters for each name you wouln't have any trouble at all.

  Since you only have the problem with the two customers with the same setup, the customers had no problems until "a month ago" I'd wonder what changed a month ago. Perhaps an update to either OS I'd also test with the VFP9 OLE DB data provider and see if that helps at all.

  
  
  

• wetheral

  Hi Quintesv,

  Please be sure you have the latest FoxPro and Visual FoxPro OLE DB data provider, downloadable from msdn.microsoft.com/vfoxpro/downloads/updates.

  There is a possibility that your DBF has been corrupted. Do you have access to FoxPro to try to open the table natively

  Does this happen with only one table or with all of them Can you set up a linked server to your table and open it that way Can you create a simple DBF in Excel and access it via OpenRowset or via a linked server

  
  
  

• Soe

  Hi Cindy,

  I've just downloaded the provider and the fixlist for VFPOLEDB 9.0, though SP1 for it is 35 MB - ill start that download tomorrow.

  I've checked the fixlist, and will have to meet with the stakeholders to see if we want to install this on a production machine at this stage of the game. Im sure you understand that installing anything new without testing could be troublesome and that has to be taken into consideration, and while this is an urgent issue, it will require an executive decision. Having the system work at such a large user base doesn't convince me that it is a bug in VFPOLEDB 8.0.

  We have added SYSTEM and give it full permissions on the directory where the DBFs are residing, and have given full control to everyone as well as adminstrator.

  Furthermore... the "\2" and "\3" thing... Our contact at the client was logging onto the server using remote desktop and that creates the \2 etc tmp variables, though it isn't made visible in the environment variables when doing so. So thats not it.

  The %TMP% folder is being used correctly it seems, its just that SQL Server is not creating a .tmp file when executing the openrowset. We've tested this ourside and it should definitely be created in the path specified by the %TMP% environment variable.

  The client says nothing has changed on the system, and its very difficult to see if anything has changed, however based on any decision made tomorrow we may need to go out there and ensure that all the latest hotfixes are installed and perhaps reinstall SQL Server.

  Thanks for your help, I will post any further info here when I can.

  
  
• OPENROWSET using Visual FoxPro Provider error 'Could not fetch a row'