• With 64 bit SQL 2008 on Server 2008 I read .dbf table with a long file name using:



    ,'dbase IV;Database=D:\WebTest\ShapeFiles'

    ,'SELECT * FROM [sppobs~4]')

    because I get an error

    "The Microsoft Access database engine could not find the object ...

    when I use the entire name in the FROM clause.  Is there a syntax allowing me to specify a table name longer than 8 character length?
    • Edited by abjones3 Friday, July 26, 2013 7:20 PM
    Friday, July 26, 2013 7:19 PM

All replies

  • Hi abjones3,

    Please make sure you have installed Microsoft OLE DB Provider for Visual FoxPro and try the following commands:

    SELECT * FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver;
    'SELECT * FROM [sppobs~4]')
    For more detail information, you can refer to the following link:

    Microsoft OLE DB Provider for Visual FoxPro 9.0

    How to import a DBF file in SQL Server

    Trouble importing .dbf file into SQL server 2008 express edition

    Allen Li
    TechNet Community Support

    Sunday, July 28, 2013 12:57 AM
  • Using  'Microsoft.ACE.OLEDB.12.0' returns exactly what I want, but I cannot understand why it does not support long file names. I thought perhaps there was some other secret handshake with this method to have it perform in a modern way using file names not restricted to 8.3.  So, Microsoft wrote 16-bit DOS file name convention into the driver for Access 2012?

    I have used VFPDBOLE for several years with 32 bit SQL 2005. I have been told 32 bit drivers such as Visual FoxPro are not compatible with 64 bit SQL.  The response by 64 bit SQL 2008 from the suggested query is:

    OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

    In searching Microsoft Download Center, I can only find MSDASQL for Server 2003.

    • Edited by abjones3 Wednesday, July 31, 2013 2:08 PM
    Monday, July 29, 2013 6:25 PM
  • I created a linked-server TEST

    • provider: Microsoft Ofice 12.0 Access Database Engine OLE DB Provider
    • Product Name: Microsoft Access DB
    • Data source: d:\WebTest\ShapeFiles
    • Provider string: dbase IV

    When I click on Object Explorer - Server Objects - Linked Servers - TEST - Catalogs - default - tables

    I get an appropriate-for-me long list of table names. When I right-click a name - Script Table as - SELECT To - Clipboard

    for any name that is 8 characters or less a "select" statement is created but

    for any name longer than 8 characters I get message - Cannot obtain the schema rowset "DBSCHEMA_COLUMNS" for OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TEST". The provider supports the interface, but returns a failure code when it is used. (Microsoft SQL Server, Error:7311)

    Monday, August 05, 2013 5:13 PM