none
Microsoft.ACE.OLEBD.12.0

    Question

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

    SELECT * FROM OPENROWSET(

    'Microsoft.ACE.OLEDB.12.0'

    ,'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;
    SourceDB=D:\WebTest\ShapeFiles;
    SourceType=DBF',
    'SELECT * FROM [sppobs~4]')
    
    For more detail information, you can refer to the following link:

    Microsoft OLE DB Provider for Visual FoxPro 9.0
    http://www.microsoft.com/en-us/download/details.aspx?id=14839

    How to import a DBF file in SQL Server
    http://stackoverflow.com/questions/52822/how-to-import-a-dbf-file-in-sql-server

    Trouble importing .dbf file into SQL server 2008 express edition
    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/0abcaf7c-6df6-49ce-868d-afbede249cff/trouble-importing-dbf-file-into-sql-server-2008-express-edition


    Allen Li
    TechNet Community Support

    Sunday, July 28, 2013 12:57 AM
    Moderator
  • 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