With 64 bit SQL 2008 on Server 2008 I read .dbf table with a long file name using:
because I get an error
SELECT * FROM OPENROWSET(
,'SELECT * FROM [sppobs~4]')
"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
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
How to import a DBF file in SQL Server
Trouble importing .dbf file into SQL server 2008 express edition
TechNet Community Support
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
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)