January 20, 2005 at 5:11 am
I have a linked server setup that connects to a Visual Foxpro database via VFPOLEDB. Issuing SELECT * from <linkedserver>...<table> commands from query analyser works fine until it hits a table with decimal data fields in it at which point it complains about 'Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'vfpoledb.1' supplied inconsistent metadata for a column. Metadata information was changed at execution time'. If I exclude the decimal fields from the select statement then it will retrieve the correct rows.
Do you have to do something extra to extract decimal fields that I'm missing ?
The actual program that needs to issue the commands is written in VB.Net & that's having exactly the same problem - using query analyser was just a test to see if it was the program at fault.
I've downloaded the latest VFPOLEDB but that doesn't appear to resolve the problem.
Any ideas ?
Mike.
January 21, 2005 at 9:35 am
Hi Mike,
The best place to post your VFP related questions is http://www.universalthread.com.
I made quick search there and here it goes (from Jim Sanders, Microsoft)...
"We have seen this with numeric data. For instance, if the data type is n(7,4), Visual FoxPro allows you to programatically enter a value that is not really 7,4 but actually 7,3. An n(7,4) field should be 7 total digits, including the decimal point, so the max should be 99.9999. Visual FoxPro has always allowed you to programatically provide an equal number of digits, with fewer decimal places, like this:
replace nfield with 999.999Visual FoxPro itself allows updating and accessing data from a field containing this value, but the Visual FoxPro OLEDB provider does not allow either. The typing is stricter to ensure proper interoperability with ActiveX Data Objects, ActiveX Data Objects.Net, SQL Linked Servers, DTS, etc.
If you have a field like this, you will get the EFAIL error. The workaround is to increase the field size to properly accomodate the data it actually contains.
For example, a value of 123.456 would cause an error in the provider if the field datatype is n(7,4) but will work OK with a type of n(8,4)."
HTH
Oleg
January 21, 2005 at 3:52 pm
Hi Mike,
I've never had much luck with the VFP OLEdb provider when it comes to SQL Server linked server. I suggest that you use the ODBC driver instead. For more information on using VFP as a linked server see my article in FoxPro Advisor magazine:
January 24, 2005 at 2:08 am
The problem I've had with the ODBC driver is that it seems to return deleted records from the FoxPro database & I haven't found a way of either supressing this or identifying them in the returned dataset.
I'm currently setting up views in FoxPro & accessing these instead of the tables themselves, I've set the views to only return character fields - even to the point of converting all the decimals to characters.
At the moment it's a case of one step forward and one step back each time I try something with the linked server.
Mike.
January 24, 2005 at 8:02 am
You can suppress the deleted records in ODBC by configuring the ODBC driver for VFP. Try going to Start->Control Panel->Administrative tools->Data Sources (ODBC).
Select the Visual FoxPro Tables from the list and click on the configure button. At the bottom right is an Option>> button. Click on this button to exand the dialog for special options. There should be a few check boxes. Clear the DELTED checkbox. To make things speedy make sure that all your FoxPro tables have an index on the DELETED() function.
HTH,
-Ryan Jentzsch
January 24, 2005 at 10:26 am
Ryan,
Unfortunately the ODBC settings don't work when using an SQL linked server. If you issue a simple command SELECT * FROM [LINKEDSERVER].[DATABASE]..
from a program or from Query Analyser it brings back the deleted records regardless of the ODBC setting.
If you connect direct to the data via ODBC then the settings do work. However, I need to access the data from a server on a DMZ running IIS & aspx web pages so accessing a network share defeats the object of having a firewall hence using SQL to validate the connection & punch through the firewall using a specific open port.
Mike.
January 24, 2005 at 10:46 am
Mike,
Hmmm, sounds like a SQL Server bug (why would linked servers not respect the ODBC settings?) I wish I had something more to offer, but when the underlying linked server system ignores connectivity settings what can you do? Sounds like FoxPro views are the way to go.
Best of luck,
Ryan
January 24, 2005 at 12:07 pm
It may not be possible in your situation but if you can get the people on the FoxPro side to issue that nifty "PACK" command when they delete something, you should't get any of those deleted rows.
The other thing that I do when importing foxpro tables is to pass any foxpro decimal columns through the sqlserver isnumeric(column_in_question) function. We are using Fox 2.6.
Teague
January 25, 2005 at 2:09 am
As you indicated, packing the tables isn't an option because this requires exclusive access to the tables and this is a live production system. We are using Visual FoxPro for the client application.
Views are working relatively fine - it's just a lot of unneeded work on the data at both ends.
Mike
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply