Visual Foxpro Linked Server Fails with inconsistent metadata

• Jim Schwan

  SSC Journeyman

  Points: 85

  More actions

  August 24, 2004 at 2:55 pm

  #102172

  I have some Visual FoxPro (VFP) applications with data I need to access in SQL Server Stored Procedures.  I added a linked server that points to the VFP tables using VFPOLEDB.  The problem I am having is that when ever I try to SELECT against a VFP table that contains numeric data I get the following message:

  OLE DB provider 'vfpoledb.1' supplied inconsistent metadata for a column. Metadata information was changed at execution time.

  OLE DB error trace [Non-interface error:  Column 'overhead' (compile-time ordinal 4) of object 'vw_prodcl' was reported to have a PRECISION of 8 at compile time and 7 at run time].

  SELECT statements against VFP table that contain charactor or logical data types work fine.  I get the same results if I access VFP free tables, VFP databases, or local views in VFP databases that exclude the numeric data.

  Anyone know what is causing this and a simple work around?

  Thanks,

  ...JS

   

   

   

• August 27, 2004 at 8:00 am

  This was removed by the editor as SPAM

• Anthony Ordner

  Grasshopper

  Points: 14

  More actions

  September 28, 2004 at 2:29 pm

  #524697

  You will need to create a new linked server and hit the "Provider Options" box, I enable ALL options -- I am not completely certain what they all specifically do but once I do this that error message goes away and queries return results.

   

  thanks

  tony

• Jim Schwan

  SSC Journeyman

  Points: 85

  More actions

  September 28, 2004 at 2:38 pm

  #524700

  

  I found the solution myself.  You need to use the following format:

  SELECT     *

  FROM         OPENQUERY(workshop, 'SELECT * FROM prodcl') Rowset_1

  By adding the OPENQUERY you move the issue to the linked server and the problem goes away! The only thing to keep in mind is that the SQL within the OPENQUERY statement must be in the linked server's syntax, not SQL Server's syntax.  It makes a difference if you are linking to VFP.

  Jim Schwan

  Schwan Consulting

  jeschwan@qwest.net

   

• Marc Girardbille

  Mr or Mrs. 500

  Points: 518

  More actions

  January 3, 2005 at 3:49 am

  #535584

  I had the same kind of problem in ORACLE.

  I solved it by explicitly converting the column to number, in the ORACLE view. In the SELECT clause, use TO_NUMBER(overhead).

  Best regards,

  Marc Girardbille

• Marc Massé

  SSC Enthusiast

  Points: 111

  More actions

  January 28, 2005 at 2:42 pm

  #539623

  I found It and I will share my friends as I did not found the solution on the web....

  SELECT * 

  FROM OpenDataSource( 'VFPOLEDB.1', 'DRIVER={Microsoft FoxPro Driver (*.dbf)};

  Data Source="\\MyServername\database\dbf";User ID=;Password=;Extended properties=;')...Supplier

  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.

  OLE DB error trace [Non-interface error:  Column 'handchrg' (compile-time ordinal 19) of object 'Supplier' was reported to have a PRECISION of 6 at compile time and 5 at run time].

  DO this instead ...

  EXEC sp_addlinkedserver

     @server = 'OttawaDbfVFP',

     @srvproduct = '',

     @provider = 'VFPOLEDB.1',

     @provstr = 'DRIVER={Microsoft FoxPro Driver (*.dbf)};Data Source="\\MyServername\database\dbf";User ID=;Password=;Extended properties=;'

  GO

  Magic lies here....

  USE master

  EXEC sp_serveroption 'OttawaDbfVFP', 'lazy schema validation', 'true'

  GO

  Then  use open query on the new LinkedServer

  SELECT *

  FROM OPENQUERY(OttawaDbfVFP, 'SELECT * FROM Supplier')

  GO

  It not enough to learn , one must become...

  Marc Massé