5

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,

Community
  • 1
  • 1
Gimo Gilmore
  • 229
  • 9
  • 23
  • You need to start from basics. Use this link to open the ODBC administrator. http://windows.microsoft.com/en-au/windows7/using-the-odbc-data-source-administrator. Now create a system DSN to connect to your DBF file - does it work? – Nick.Mc Dec 05 '13 at 06:26
  • is this a one time or recurring data import? – Eccountable Dec 12 '13 at 01:38
  • Good question. I'm having the same problem. @Nick, I tried adding a system DSN but A) I and the people using the script I'm trying to create do no have admin privileges and, B) this would require every user of the script to add a driver and set up a system DSN which is beyond the level of understanding many of them have. Any ideas or pointers would be doubly appreciated, fellas (and ladies). – cfwschmidt Apr 22 '15 at 15:45
  • Considering you are creating an _application_ to do this, did you realise that in order to use `OPENROWSET` the driver has to be installed on the SQL Server? If you want to import a DB2 file into SQL Server via a 'portable' application, don't use OPENROWSET as this is a server side activity and you need for example to install DBF drivers on the SQL Server. Perhaps you could clarify exactly how you want this to work. – Nick.Mc Apr 22 '15 at 22:57
  • @cfwschmidt perhaps start a new question but... firstly this is just a test activity to test, it's not required to actually run it, and secondly this is a server side activity, every user would not have to do this anyway. It only needs to be tested on the server. – Nick.Mc Apr 22 '15 at 23:15

4 Answers4

2

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.

DougM
  • 2,808
  • 17
  • 14
Alan B
  • 4,086
  • 24
  • 33
0

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.

cfwschmidt
  • 85
  • 1
  • 1
  • 10
0

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).

cfwschmidt
  • 85
  • 1
  • 1
  • 10
0

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

Jeffrey Berezin
  • 1