CODEWITHSUNDEEP
sql.netsql-serverfoxprodbf
Gimo Gilmore
Gimo Gilmore

Reputation: 229

import a DBF file in SQL Server using SQL Script

How can I import a .dbf file into SQL Server using a SQL script?

Found answers from this post, but unfortunately none of them work to me :( :

When I'm trying this code :

SELECT * 
INTO [APP_DB]..[BILLHEAD] 
FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver; SourceDB=D:\DBF; SourceType=DBF', 'SELECT * FROM BILLHEAD')

I get this error:

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

And also, when trying this :

SELECT * 
FROM openrowset('VFPOLEDB','D:\DBF\BILLHEAD.dbf';'';
                '','SELECT * FROM BILLHEAD')

I get this error :

Msg 7438, Level 16, State 1, Line 1
The 32-bit OLE DB provider "VFPOLEDB" cannot be loaded in-process on a 64-bit SQL Server.

I don't want to download any third party application. That's why I'm trying all the possible solution and I need your help now guys. I'm creating a small application to import .DBF files into SQL Server.

Regards,

Upvotes: 5

Views: 8009

Answers (4)

Jeffrey Berezin
Jeffrey Berezin

Reputation: 13

I have had similar problems where stuff just wasn't working trying to move legacy tables from VFP to SQL 2008R2 and used the following procedure:

  1. select table within VFP
  2. copy to blahblah xl5
  3. Step 2 results in an excel file
  4. Use SQL 2008 R2 or higher "Import and Export Data (32 bit)" to import the excel file.
  5. I was running Windows 7 64 bit and still had to use the 32 bit import to make it work smoothly.

This may explain why you need the 32 bit Import: https://msdn.microsoft.com/en-us/library/ms141209.aspx

Upvotes: 0

cfwschmidt
cfwschmidt

Reputation: 97

Our office SQL/GIS guru, Burce, solved a similar problem I was having. I'm not sure of all the details of how he did it, so while I am reluctant to enter this as an "Answer" (it is too many characters to enter as a Comment) I'll describe what I can in case it is helpful for anyone. First be aware that he has full permissions on the SQL Server, so this solution may not be feasible for all DB users to implement. Bruce set up a Linked Server that's connected to a directory ".../DBF/" on our LAN file server. He also set up a similar Linked Server & directory for CSV files. Anyone in our office can simply copy a DBF file to this directory and then access it in SQL Server as if it were a regular table in a SQL Server database. I access this in SSMS by connecting to the Database Engine then going to Server Objects > Linked Servers > "DBF" > Catalogs > default > Tables > file name . The Properties of the Linked Server say the following:

From General tab of Properties window enter image description here

From Security tab of Properties window enter image description here

From Server Options tab of Properties window enter image description here

Note that this may or may not be a secure configuration for all database server environments, but this is on a SQL Server that is on our internal network, only accessible within our office, with no endpoints or access outside our LAN (it's not used as a server for web, or other internet services).

Upvotes: 0

cfwschmidt
cfwschmidt

Reputation: 97

Gimo, I'm not sure this will work and I'm no MS SQL Server expert, but I've been wrestling with a similar problem lately and I have an idea. I think you may be able to get that first block of code from your question to work if you execute the following statements first:

EXEC sp_configure 'show advanced options', 1 RECONFIGURE; GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1 RECONFIGURE; GO

This may not work if you don't have adequate permissions (which happened in my situation), but it may be worth a shot.

Upvotes: 0

Alan B
Alan B

Reputation: 4288

You are using 64-bit SQL sever, but FoxPro OLE DB driver is 32-bit. You need to consult this article which discusses how to use the 32-bit OLE DB driver with 64-bit SQL Server.

Upvotes: 2

Related Questions