Link to home
Start Free TrialLog in
Avatar of kennethzxc
kennethzxc

asked on

how dbf to sql database ?

SELECT * FROM OPENROWSET('MSDASQL',
  'Driver={Microsoft dBase Driver (*.dbf)};DBQ=C:\Attendance\new_attendance_dbf\',
  'Select * from clkdata')

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

SELECT *                   
FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver;                                    
SourceDB=C:\Attendance\new_attendance_dbf\clkdata.dbf;                              
SourceType=DBF'            ,                  
'SELECT * FROM clkdata')
      
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)".

how to fix the error?
Avatar of kennethzxc
kennethzxc

ASKER

please help me huhu
Avatar of Olaf Doschke
You specified you want to use the "Microsoft Visual FoxPro Driver". Make sure it is installed.

You better use the VFP OLEDB Provider, though. And see http://fox.wikis.com/wc.dll?Wiki~VisualFoxProDataFromSQLServer

You have to: Install a driver, setup file access privileges in the folder with DBFs and then can use OPENROWSET. Also important: If MSSQL Server runs with a non domain systemaccount you can only access DBFs on local drives, local from the point of view of the server.

There are some settings, but that should be mentioned in the wiki. Please read and not just copy over the code part.

Bye, Olaf.
yeah I've installed the Microsoft visual FoxPro driver 9.0 sp2 but the error still persist. I use virtual machine to do this.

-- Example of using tables in a VFP DBC in SQL Server
sp_addlinkedserver @server = 'VFPoledbDBC',
@srvproduct = 'VFP',
@provider = 'VFPOLEDB.1',
@datasrc = 'C:\VFP9\Samples\Data\testdata.dbc'
GO
SELECT * FROM VFPoledbDBC...Products
GO
sp_dropserver @server = 'VFPoledbDBC'
GO


VFPOBLEDB has not been registered
cannot create an instance of OLE DB PROVIDER FPOLEDB for linked server
visual FoxPro ole db provider not detected by sql or excel T_T even though I installed it
Hi,

try to avoid DBF files in your SQL server. It seems Microsoft support to this format is frozen or becomes a history. MS Access 2013 removed DBF support completely...

You options in a near future are:
1) Pure DBF
2) SQL Server without DBFs
3) Visual FoxPro application with ODBC connection to MS SQL Server
okay thanks ill try to install 32bit sql
i've installed 32bit sql but it's not working :D
select *
from  openrowset('VFPOLEDB','C:\SomePath\CERTDATA.DBF';'';
    '','SELECT ACTUAL, CERTID,  FROM CERTDATA')

Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "VFPOLEDB" has not been registered.
i can't find vfpoledb.dll T_T
Did you download and install the latest VFP OLE DB provider? (http://www.microsoft.com/en-us/download/details.aspx?id=14839)

File location:
C:\Program Files (x86)\Common Files\System\Ole DB\

Does the VFP OLE DB provider works as a data source in VB.NET or C# application (32 bit)?

Do you see the VFP OLE DB provider in a list of providers when creating a Linked Server in your 32 bit SQL Server Management Studio?

Can you check your Registry entries (32 bit) for VFPOLEDB string occurrence?  VFPOLEDB.1 is necessary sometimes...

BTW, what SQL Server version are we talking?
sql version 2008 :D yeah i've installed vfpoledb but the dll is missing thanks :)
how to check the registry ?
i think i can't find the
vfpoelb when creating a linked server
but i can see msdasql in my linked server list but i can't query this one


SELECT *                  
FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver;                                    
SourceDB=C:\Attendance\new_attendance_dbf\clkdata.dbf;                              
SourceType=DBF'            ,                  
'SELECT * FROM clkdata')

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


it's so hard to query from dbf file the attendance system that is use in our company is .dbf then we need to convert it to sql and generate a report is there other way to do this stuff ? automated like every 5 minutes we need to check the dbf and update the database of the sql
MSDASQL is a driver to access an SQL Server, not DBFs nor DBC.

I point out again: You won't see the VFPOLEDB Provider from a 64bit instance of SQL Server, you have to have a separate 32bit instance of SQL Server to make use of the installed OLEDB Provider, it comes as 32bit driver only.

Bye, Olaf.
The VFPOLEDB.DLL seems not to be installed... Did you try to reinstall it?

And MSDASQL driver allows OLE DB consumer applications to utilize ODBC drivers to connect to a data source, so it should (theoretically) be possible to use it together with VFP ODBC driver. More info: http://blogs.msdn.com/b/selvar/archive/2007/11/10/msdasql-oledb-provider-for-odbc-drivers.aspx

What Microsoft Visual FoxPro ODBC Driver is on your PC? But ODBC isn't a good option probably...

The easiest way is to write a small VFP application (just a few lines) which will read the DBF data and create a table on SQL server where you can do whatever you need. FoxPro Timer can ensure the data retrieval and export every 5 minutes.
ASKER CERTIFIED SOLUTION
Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
In general for finding the DLLs, you have System32 and SYSWOW64. Master question: 32bit drivers are in what folder? Correct, in SYSWOW64.

Bye, Olaf.
i've been testing Microsoft visual FoxPro provider in opening .dbf files in excel but 2013 excel crashes
Excel 2013 is totally different, isn't it? For one, you can't do openrowset, that's T-SQL.

And again it's about 64bit vs 32bit, if you installed Office 64bit you can't use the 32bit VFP OLEDB Provider.

Bye, Olaf.