step one. Put the DBF file under any system directory, I put it in the D:\db directory
Step Two: Need to install AccessDatabaseEngine.exe (ACE)
Website:
https://www.microsoft.com/en-us/download/details.aspx?id=13255
After installation, you will see the following interface in the "Linked server", "Provider", under the instance of SQL Server.
Step Three: Execute the SQL query statement in SSMS
Select*from OPENROWSET (' microsoft.ace.oledb.12.0 ', ' dBase IV; hdr=no;imex=2;database=d:/db ', ' select * from [2015.DBF] '
There will be an error, similar
Execute the following statement:
exec sp_configure ' show advanced options ', 1
Reconfigure
exec sp_configure ' Ad Hoc distributed Queries ', 1
Reconfigure
EXEC master.dbo.sp_MSset_oledb_prop n ' microsoft.ace.oledb.12.0 ', n ' allowinprocess ', 1
EXEC master.dbo.sp_MSset_oledb_prop n ' microsoft.ace.oledb.12.0 ', n ' dynamicparameters ', 1
Step Four: Execute the SQL query command again
Select*from OPENROWSET (' microsoft.ace.oledb.12.0 ', ' dBase IV; hdr=no;imex=2;database=d:/db ', ' select * from [2015.DBF] '
Prompt as follows
You need to install FoxPro and enter the following command in the FoxPro command box
Use ' d:\db\2015.dbf ' EXCLUSIVE;
COPY to ' d:\db\2015new.dbf ' TYPE fox2x;
Once again, the new DBF file 2015new.dbf should be selected, and finally the DBF file content is successfully read. Courier is still relatively fast, more than 1 million of the data of the full table for about 1 minutes.
Select*from OPENROWSET (' microsoft.ace.oledb.12.0 ', ' dBase IV; hdr=no;imex=2;database=d:/db ', ' select * from [2015new. DBF] ')
Summary of Knowledge points:
1. The following command functions
exec sp_configure ' show advanced options ', 1
Reconfigure
exec sp_configure ' Ad Hoc distributed Queries ', 1
Reconfigure
EXEC master.dbo.sp_MSset_oledb_prop n ' microsoft.ace.oledb.12.0 ', n ' allowinprocess ', 1
EXEC master.dbo.sp_MSset_oledb_prop n ' microsoft.ace.oledb.12.0 ', n ' dynamicparameters ', 1
2. Why do you want to duplicate a copy of
Use ' d:\db\2015.dbf ' EXCLUSIVE;
COPY to ' d:\db\2015new.dbf ' TYPE fox2x;
SQL Server 2012 Import DBF file