SQL Linked Server Error – The OLE DB provider “VFPOLEDB” for linked server “MyServer” does not conta

Hello,

I’m attempting to access my VFP9 database as a SQL Server 2008 R2 linked server.

One table in particular is giving me problems. When I go to create a view against it in SSMS (notice I’m only selecting a single field), I get the following:

—————————
Microsoft SQL Server Management Studio
—————————
SQL Execution Error.

Executed SQL statement: SELECT JL_CLOCATION_ID FROM VCAL…csjudloc AS csjudloc
Error Source: .Net SqlClient Data Provider
Error Message: The OLE DB provider “VFPOLEDB” for linked server “VCAL” does not contain the table “csjudloc”. The table either does not exist or the current user does not have permissions on that table.
—————————
OK   Help  
—————————

The only hunch I have is that the table is relatively wide, and contains a number of memo fields, but RECSIZE() (in VFP) only returns 948 (I’m assuming this is in bytes). If I drop many of the fields, then the problem goes away (so far, I’ve just
brute forced deleted fields to test if this might be a width issue).

Ideally, I’d like to find out what the exact problem is so I’m not just “deleting fields until it works”. I may run into this issue in the future.

Any ideas?

Thanks.
Bill

Here is the table definition:

***** Table setup for CSJUDLOC *****
CREATETABLE'CSJUDLOC.DBF'NAME'CSJUDLOC' (JL_CJUDGE_ID C(3) NOTNULL, ;
            JL_CLOCATION_ID C(3) NOTNULL, ;
            JL_NDISPLAY_ORDER N(2, 0) NOTNULLDEFAULT 1, ;
            JL_LPRNADV_PARTIES L NOTNULLDEFAULT .F., ;
            JL_LPRNADV_ATTY L NOTNULLDEFAULT .F., ;
            JL_CVACATE_TERM C(15) NOTNULLDEFAULT"VACATED", ;
            JL_LPP_INCL L NOTNULLDEFAULT .T., ;
            JL_LPP_UPDATE L NOTNULLDEFAULT .T., ;
            JL_LPP_PRINT L NOTNULLDEFAULT .T., ;
            JL_LPPA_INCL L NOTNULLDEFAULT .T., ;
            JL_LPPA_UPDATE L NOTNULLDEFAULT .T., ;
            JL_LPPA_PRINT L NOTNULLDEFAULT .T., ;
            JL_LPPA_PRINTNUM L NOTNULL, ;
            JL_LPPOA_INCL L NOTNULLDEFAULT .F., ;
            JL_LPPOA_PRINT L NOTNULLDEFAULT .F., ;
            JL_LPPOA_PRINTNUM L NOTNULL, ;
            JL_LPD_INCL L NOTNULLDEFAULT .T., ;
            JL_LPD_UPDATE L NOTNULLDEFAULT .T., ;
            JL_LPD_PRINT L NOTNULLDEFAULT .T., ;
            JL_LPDA_INCL L NOTNULLDEFAULT .T., ;
            JL_LPDA_UPDATE L NOTNULLDEFAULT .T., ;
            JL_LPDA_PRINT L NOTNULLDEFAULT .T., ;
            JL_LPDA_PRINTNUM L NOTNULL, ;
            JL_LPDOA_INCL L NOTNULLDEFAULT .F., ;
            JL_LPDOA_PRINT L NOTNULLDEFAULT .F., ;
            JL_LPDOA_PRINTNUM L NOTNULL, ;
            JL_LSP_INCL L NOTNULLDEFAULT .F., ;
            JL_LSP_PRINT L NOTNULLDEFAULT .F., ;
            JL_LSPA_INCL L NOTNULLDEFAULT .F., ;
            JL_LSPA_PRINT L NOTNULLDEFAULT .F., ;
            JL_LSPA_PRINTNUM L NOTNULL, ;
            JL_LSPOA_INCL L NOTNULLDEFAULT .F., ;
            JL_LSPOA_PRINT L NOTNULLDEFAULT .F., ;
            JL_LSPOA_PRINTNUM L NOTNULL, ;
            JL_LSD_INCL L NOTNULLDEFAULT .F., ;
            JL_LSD_PRINT L NOTNULLDEFAULT .F., ;
            JL_LSDA_INCL L NOTNULLDEFAULT .F., ;
            JL_LSDA_PRINT L NOTNULLDEFAULT .F., ;
            JL_LSDA_PRINTNUM L NOTNULL, ;
            JL_LSDOA_INCL L NOTNULLDEFAULT .F., ;
            JL_LSDOA_PRINT L NOTNULLDEFAULT .F., ;
            JL_LSDOA_PRINTNUM L NOTNULL, ;
            JL_LT_INCL L NOTNULLDEFAULT .T., ;
            JL_LT_UPDATE L NOTNULLDEFAULT .T., ;
            JL_LT_PRINT L NOTNULLDEFAULT .T., ;
            JL_LT_PRINTNUM L NOTNULL, ;
            JL_LTA_INCL L NOTNULLDEFAULT .T., ;
            JL_LTA_UPDATE L NOTNULLDEFAULT .T., ;
            JL_LTA_PRINT L NOTNULLDEFAULT .T., ;
            JL_LTA_PRINTNUM L NOTNULL, ;
            JL_LDA_INCL L NOTNULLDEFAULT .T., ;
            JL_LDA_UPDATE L NOTNULLDEFAULT .T., ;
            JL_LDA_PRINT L NOTNULLDEFAULT .T., ;
            JL_LDA_PRINTNUM L NOTNULL, ;
            JL_LDOA_INCL L NOTNULLDEFAULT .F., ;
            JL_LDOA_PRINT L NOTNULLDEFAULT .F., ;
            JL_LDOA_PRINTNUM L NOTNULL, ;
            JL_LJDA_INCL L NOTNULLDEFAULT .F., ;
            JL_LJDA_UPDATE L NOTNULLDEFAULT .F., ;
            JL_LJDA_PRINT L NOTNULLDEFAULT .F., ;
            JL_LJDA_PRINTNUM L NOTNULL, ;
            JL_LJDOA_INCL L NOTNULLDEFAULT .F., ;
            JL_LJDOA_PRINT L NOTNULLDEFAULT .F., ;
            JL_LJDOA_PRINTNUM L NOTNULL, ;
            JL_LCA_INCL L NOTNULLDEFAULT .T., ;
            JL_LCA_PRINT L NOTNULLDEFAULT .T., ;
            JL_LCA_PRINTNUM L NOTNULL, ;
            JL_LPCA_INCL L NOTNULL, ;
            JL_LPCA_UPDATE L NOTNULL, ;
            JL_LPCA_PRINT L NOTNULL, ;
            JL_LPCA_PRINTNUM L NOTNULL, ;
            JL_LPMA_PRINT L NOTNULL, ;
            JL_LPMA_PRINTNUM L NOTNULL, ;
            JL_LMA_PRINT L NOTNULL, ;
            JL_LMA_PRINTNUM L NOTNULL, ;
            JL_LPRA_PRINT L NOTNULL, ;
            JL_LPRA_PRINTNUM L NOTNULL, ;
            JL_LRA_PRINT L NOTNULL, ;
            JL_LRA_PRINTNUM L NOTNULL, ;
            JL_LOA_PRINT L NOTNULL, ;
            JL_LOA_PRINTNUM L NOTNULL, ;
            JL_LOT_PRINT L NOTNULL, ;
            JL_LOT_PRINTNUM L NOTNULL, ;
            JL_LPM_PRINT L NOTNULL, ;
            JL_LM_PRINT L NOTNULL, ;
            JL_LPR_PRINT L NOTNULL, ;
            JL_LR_PRINT L NOTNULL, ;
            JL_CDEF_ECR C(8) NOTNULLDEFAULT"", ;
            JL_CDEF_DEP C(8) NOTNULLDEFAULT"", ;
            JL_LCNT_HOLIDAYS L NOTNULL, ;
            JL_LCNT_WEEKENDS L NOTNULL, ;
            JL_LMINUTES L NOTNULLDEFAULT .F., ;
            JL_LME_ADDRESS L NOTNULLDEFAULT .F., ;
            JL_LME_PHONE L NOTNULLDEFAULT .F., ;
            JL_LPRTHEA L NOTNULLDEFAULT .F., ;
            JL_LBOTHEA L NOTNULLDEFAULT .F., ;
            JL_LDOCKET L NOTNULLDEFAULT .F., ;
            JL_LTIMESP L NOTNULLDEFAULT .F., ;
            JL_LVACATE L NOTNULLDEFAULT .T., ;
            JL_LNGEN L NOTNULLDEFAULT .F., ;
            JL_LNCD L NOTNULLDEFAULT .F., ;
            JL_LNLAW L NOTNULLDEFAULT .F., ;
            JL_LNJUDGE L NOTNULLDEFAULT .F., ;
            JL_LNTENT L NOTNULLDEFAULT .F., ;
            JL_NHRGMSG I NOTNULLDEFAULT 1, ;
            JL_NTELE I NOTNULLDEFAULT 3, ;
            JL_NCOURT I NOTNULLDEFAULT 3, ;
            JL_NMSPACE I NOTNULLDEFAULT 0, ;
            JL_NHSPACE I NOTNULLDEFAULT 0, ;
            JL_LLSTMAT L NOTNULLDEFAULT .F., ;
            JL_NMATLST I NOTNULLDEFAULT 1, ;
            JL_LRESPAG L NOTNULLDEFAULT .F., ;
            JL_LSINGLE L NOTNULLDEFAULT .F., ;
            JL_LNEWPAG L NOTNULLDEFAULT .F., ;
            JL_NSTART I NOTNULLDEFAULT 1, ;
            JL_LNEWSLT L NOTNULLDEFAULT .F., ;
            JL_NJUMP I NOTNULLDEFAULT 100, ;
            JL_IDEFHRGTYPE I NOTNULLCHECKBETWEEN(jl_idefhrgtype,0,3) ERROR "Invalid default hearing type value."DEFAULT 0, ;
            JL_TSTARTCOURT T NOTNULLDEFAULT {^1899/12/30 09:00:00}, ;
            JL_TENDCOURT T NOTNULLDEFAULT {^1899/12/30 16:00:00}, ;
            JL_ISETDAYS I NOTNULLDEFAULT 30, ;
            JL_ICHECKDAYS I NOTNULLDEFAULT 7, ;
            JL_IROOMID I NOTNULL, ;
            JL_LPRINTNOHRGDAYS L NOTNULL, ;
            JL_LPRINTTENTSETTINGNOTES L NOTNULL, ;
            JL_LINCMAINATY L NOTNULLDEFAULT .T., ;
            JL_LNOPRTMAINATY L NOTNULLDEFAULT .F., ;
            JL_LFTRLOGSHEETS L NOTNULL, ;
            JL_CFTRLOCATIONNAME C(50) NOTNULL, ;
            JL_MFTRLOGSHEETPATH M NOTNULL, ;
            JL_IPRID I NOTNULL, ;
            JL_MECFEVENTS M NOTNULL, ;
            JL_CSIGPOTYPE C(100) NOTNULL, ;
            JL_CSIGPOSSDESC C(100) NOTNULL, ;
            JL_MSIGOUTPOFOLDER M NOTNULL, ;
            JL_MSIGOUTPOFOLDER2 M NOTNULL, ;
            JL_MSIGIN M NOTNULLDEFAULT"Docket", ;
            JL_MSIGDISCARD M NOTNULL, ;
            JL_MSIGDISCARD2 M NOTNULL, ;
            JL_MMANOUT M NOTNULL, ;
            JL_MMANIN M NOTNULL, ;
            JL_MMANDISGARD M NOTNULL, ;
            JL_NDEFBKCATEGORY N(8, 0) NOTNULL, ;
            JL_NDEFAPCATEGORY N(8, 0) NOTNULL, ;
            JL_LDEFVIRTUAL L NOTNULL, ;
            JL_MJUDGEPRIDS M NOTNULL, ;
            JL_LNOVACATEMINUTES L NOTNULLDEFAULT .F., ;
            JL_COFFICE C(1) NULLDEFAULT .NULL., ;
            JL_LDEFVACVIRT L NOTNULLDEFAULT .F., ;
            JL_LCOMBINEMATTERS L NOTNULLDEFAULT .T., ;
            JL_LIGNORETERM L NOTNULLDEFAULT .F., ;
            JL_LCOMBINEAP L NOTNULLDEFAULT .F., ;
            JL_LCOMBINEBK L NOTNULLDEFAULT .F., ;
            JL_LCOMBINEALLAP L NOTNULLDEFAULT .F., ;
            JL_LCHECKTERM L NOTNULLDEFAULT .T., ;
            JL_LNOPRTRELCASE L NOTNULLDEFAULT .F., ;
            JL_LSWAPBKAP L NOTNULLDEFAULT .F., ;
            JL_MFTRHTMLPATH M NOTNULL, ;
            JL_MCALPOSTPATH M NOTNULL, ;
            JL_LFTRLOGSUB L NOTNULL, ;
            JL_LFTRHTMLSUB L NOTNULL, ;
            JL_MFTRSEARCH M NOTNULL, ;
            JL_LCASENUM L NOTNULLDEFAULT .F., ;
            JL_LOVERWRITE L NOTNULLDEFAULT .T., ;
            JL_LPRTNUMC L NOTNULL, ;
            JL_LPRTNUMM L NOTNULL, ;
            JL_MPOSTCAL M NOTNULL, ;
            JL_ICHECKECF I NOTNULL, ;
            JL_IFTRVERSION I NOTNULLDEFAULT 1, ;
            JL_LAUTODOCKETOVERRIDE L NOTNULLDEFAULT .F., ;
            JL_LCASFLG L NOTNULLDEFAULT .F., ;
            JL_LTERMDL L NOTNULLDEFAULT .F., ;
            JL_LHRGDKT L NOTNULLDEFAULT .F., ;
            JL_LHTML L NOTNULLDEFAULT .F., ;
            JL_CSUBJ V(254) NOTNULLDEFAULT" AND ", ;
            JL_CCRLF V(50) NOTNULLDEFAULT"
"
, ; JL_CTAB V(50) NOTNULLDEFAULT"    ", ; JL_IATTYCAP I NOTNULLDEFAULT 1, ; JL_ICASESUBJECT I NOTNULLDEFAULT 1, ; JL_ICASEPARTIES I NOTNULLDEFAULT 1, ; JL_IFORCSUBJECT I NOTNULLDEFAULT 1, ; JL_IFORCPARTIES I NOTNULLDEFAULT 1) ***** Create each indexfor CSJUDLOC ***** INDEXON JL_CJUDGE_ID+STR(JL_NDISPLAY_ORDER) TAG DISP_ORDER COLLATE'MACHINE'INDEXON JL_NDEFAPCATEGORY TAG JL_NDEFAPC COLLATE'MACHINE'INDEXON JL_NDEFBKCATEGORY TAG JL_NDEFBKC COLLATE'MACHINE'INDEXON JL_CJUDGE_ID+JL_CLOCATION_ID TAG LOCATION COLLATE'MACHINE'INDEXON JL_CLOCATION_ID TAG LOC_ID COLLATE'MACHINE'INDEXON JL_IROOMID TAG ROOMID COLLATE'MACHINE'INDEXON JL_CJUDGE_ID TAG JUDGE_ID COLLATE'MACHINE'

VFP OLE DB provider handles varchar columns correctly when accessed via ADODB.Connection object, so the problem must be on SQL Server side…Please report this issue as a bug in SQL Server.I’ve been testing it and problematic seem to be the three varchar columns because the error in my case was:Msg 7356, Level 16, State 1, Line 1The OLE DB provider “VFPOLEDB” for linked server “FOXDB” supplied inconsistent metadata for a column. The column “jl_csubj” (compile-time ordinal 175) of object “csjudloc” was reported to have a “DBCOLUMNFLAGS_ISFIXEDLENGTH” of 16 at compile time and 0 at

Thank you Pavel.I have also tested this VarChar issue with Excel 2010 and Crystal Reports XI R2 and both are unable to “see” the table in the VFP dbc.Do you feel this is a bug in all of the products?Changing the VarChar field to Char worked as you describe.Thanks.

If we do have one product which is accessing data correctly (ADODB.Connection object) then all others having problems are not updated to the latest VFP state and it is a bug in these respective products.OTOH, the error message saying “DBCOLUMNFLAGS_ISFIXEDLENGTH of 16 at compile time and 0 at run time.” could mean I am not correct.