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?
kennethzxcAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kennethzxcAuthor Commented:
please help me huhu
0
Olaf DoschkeSoftware DeveloperCommented:
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.
0
kennethzxcAuthor Commented:
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
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

kennethzxcAuthor Commented:
cannot create an instance of OLE DB PROVIDER FPOLEDB for linked server
0
kennethzxcAuthor Commented:
visual FoxPro ole db provider not detected by sql or excel T_T even though I installed it
0
Olaf DoschkeSoftware DeveloperCommented:
VFPOLEDB: 32Bit

SQL Server: 64bit?

Then that's the reason. See http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_26347063.html
In the end he had to install and use a seperate 32bit SQL Server instance.

Bye, Olaf.

Also see: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_26287509.html
0
pcelbaCommented:
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
0
kennethzxcAuthor Commented:
okay thanks ill try to install 32bit sql
0
kennethzxcAuthor Commented:
i've installed 32bit sql but it's not working :D
0
kennethzxcAuthor Commented:
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.
0
kennethzxcAuthor Commented:
i can't find vfpoledb.dll T_T
0
pcelbaCommented:
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?
0
kennethzxcAuthor Commented:
sql version 2008 :D yeah i've installed vfpoledb but the dll is missing thanks :)
how to check the registry ?
0
kennethzxcAuthor Commented:
i think i can't find the
vfpoelb when creating a linked server
0
kennethzxcAuthor Commented:
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
0
Olaf DoschkeSoftware DeveloperCommented:
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.
0
pcelbaCommented:
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.
0
Olaf DoschkeSoftware DeveloperCommented:
I see you installed MSSQL 32bit.

Follow advice here (as mentioned earlier):  http://fox.wikis.com/wc.dll?Wiki~VisualFoxProDataFromSQLServer

1st step

USE [master] 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'VFPOLEDB', N'AllowInProcess', 1 
GO

Open in new window

This is only needed once and is a Server setting allowin in process usage of the oledb provider.

2nd step

select * from openrowset ('VFPOLEDB.1', 'c:\vfp9\samples\data\testdata.dbc'; ' '; ' ','select * from products')

Open in new window


Adjust the path. When you installed the oledb provider you'll also find sample data in C:\Program Files (x86)\Microsoft Visual FoxPro OLE DB Provider\Samples\Northwind

And you may need VFPOLEDB.1 or VFPOLEDB, that seems to depend on the SQL Server version. It's also mentioned in the wiki.

Bye, Olaf.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Olaf DoschkeSoftware DeveloperCommented:
In general for finding the DLLs, you have System32 and SYSWOW64. Master question: 32bit drivers are in what folder? Correct, in SYSWOW64.

Bye, Olaf.
0
kennethzxcAuthor Commented:
i've been testing Microsoft visual FoxPro provider in opening .dbf files in excel but 2013 excel crashes
0
Olaf DoschkeSoftware DeveloperCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.