June 20, 2006 at 7:46 am
Hi,
I have 2 separate clients experiencing this problem,
Both systems are
We use SQL Server to connect to FoxPro 2.6 Tables.
We are using OPENROWSET as follows:
Select * from openrowset('VFPOLEDB.1','C:\Client Data';;,'Select * from cm')
We are now getting this error:
Server: Msg 7330, Level 16, State 2, Line 1 Could not fetch a row from OLE DB provider 'vfpoleDB.1'. OLE DB error trace [OLE/DB Provider 'vfpoleDB.1' IRowset::GetNextRows returned 0x80040155].
The statement worked up until around 20 days ago, which made us think that it may be windows updates/sps/hotfixes, but i have installed the above on a test machine and installed all windows updates and the test machine works AOK.
Has anybody seen this error?
Thanks in advance
Q
June 20, 2006 at 9:27 am
Not TSQL
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 21, 2006 at 3:17 am
I found this under http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_78z8.asp
Its the Transact-SQL Reference for OpenRowset.
Its also under http://msdn2.microsoft.com/en-us/ms190312.aspx which is for SQL Server 2005:
So it is TSQL. I knew that but thought I'd point it out to you.
Which also means that this forum is a good place to put it.
So if you have a better place to put it rather say that, it doesnt take much more time if you put in more info. Or if you can't help rather don't reply.
June 21, 2006 at 3:32 am
I think Tim meant that it is not really a T-SQL problem. It's rather a problem with OPENROWSET usage.
Select * from openrowset('VFPOLEDB.1','C:\Client Data';;,'Select * from cm')
I can see that you try to select all data from table cm in directory c:\client data. But the question is, from which file? Shouldn't you write something like
Select * from openrowset('VFPOLEDB.1','C:\Client Data\MyFile.dbc';;,'Select * from cm')
instead?
Or, the error code [0x80040155] most often suggests that the interface is missing. Have you tried to reinstall the VFPOLEDB provider on the machine that runs the query and fails (SQL Server, SBS or client machine)?
N 56°04'39.16"
E 12°55'05.25"
June 21, 2006 at 3:35 am
OK good luck.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 21, 2006 at 4:00 am
Look I apologise if my last reply is a little rough, but I expected more than I got with that first reply. So apologies again.
Peter,
We do not use database containers (dbc) so no, that wont help. The cm specified in the select statement is in fact the table name as well. The statement doesnt work with any tables either. If I move one of the tables to my notebook it works 100% so its not the DBF file or the data.
The statement works, we have this product installed at 100s of users, but just recently, like 20/21 days ago this problem started on 2 servers running win2k server with sbs 2000.
We have tried to reinstall the provider, unregister and register the vfpoledb provider dll (vfpoledb.dll) but it makes no difference.
Something strange to note though is that if a DBF table has no rows, calling openrowset returns with no error or results. however having 1 or more records results in the error being returned.
I have created a linked server in EM to the data, which is created successfully, but when clicking on the tables node or querying the table in Query analyser the "could not fetch...' error is returned.
This is definitely something to do with the O/s. We've setup a test machine at the office similar to the production server, and installed all hotfixes for the last 2 months, but cannot reproduce this error.
June 21, 2006 at 4:02 am
So the tables are the files as well? You do not use .vfp or .dbf files either?
Ok, do you then have the right permissions on the server to query (read and edit) the files/tables and catalog?
Try to drop the .1 in Select * from openrowset('VFPOLEDB.1','C:\Client Data';;,'Select * from cm')
Also make sure you have the latest MDAC (currently 2.8 SP1) in addition to the latest VFPOLEDB provider (currently 9.0 SP1).
N 56°04'39.16"
E 12°55'05.25"
June 21, 2006 at 4:27 am
The tables are the files, but you are correct in that they are FoxPro 2.6 .dbf files.
The permissions on that folder are everybody, builtin\admin, and SYSTEM, all with full control, so that can't be it. My thinking is perhaps the security is related to SQL Server and where it gets the path or puts the temp file while building the openrowset result set. We know its using %tmp% environment variable, but the temp file is not being created in that path.
I've tried dropping the 1, which made no difference.
Lastly, they are running MDAC 2.7, but we cannot afford to try installing the latest vfpoledb provider without release testing. In any case, we are running VFPOLEDB Provider 8.0 and it's running live on a huge client base without any problem.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply