dbf in sql

Install MS Jet: http://support.microsoft.com/default.aspx?scid=kb;en-us;239114

What version of windows are you running? 32 or 64 bit?

32

I USE WIN 7

Jet OLEDB is depricated and is not installed with operating system since MDAC 2.6 (might be 2.5). You should use new ACE OLEDB provider instead of Jet. You can download ACE installation here:

http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

Change connection string to: Microsoft.ACE.OLEDB.12.0

i installed but i have same problem

How can i install linked server Microsoft.Jet.OLEDB.4.0??

Can you type this into the Run box and tell us what happens:

"regsvr32 C:\WINDOWS\system32\msjetoledb40.dll"

Do you use: SELECT *
 FROM OPENROWSET('provider=Microsoft.ACE.OLEDB.12.0','C:\plabos\ina\mjepri.dbf',
 'SELECT * FROM MyTable')
?

DIIRegisterServer in
c:\windows\system32\msjetoledb40.dll succeeded

morgulo:
if i use
 FROM OPENROWSET('provider=Microsoft.ACE.OLEDB.12.0','C:\plabos\ina\mjepri.dbf',
 'SELECT * FROM MyTable')

i get this message
Msg 7403, Level 16, State 1, Line 2
The OLE DB provider "provider=Microsoft.ACE.OLEDB.12.0" has not been registered.

ok - try the original command again now and see if the message has changed

again same

Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "provider=Microsoft.Jet.OLEDB.4.0" has not been registered.

hmmm... try this:

 SELECT *
 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:\plabos\ina\mjepri.dbf',
 'SELECT * FROM MyTable')

when i use:

 SELECT *
 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:\plabos\ina\mjepri.dbf',
 'SELECT * FROM MyTable')

this is the message

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

or this:
SELECT * FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="C:\plabos\ina\";Extended Properties=dBASE IV;')...MyTable

sumerdai has right openrowset not accept "provider=": http://msdn.microsoft.com/en-us/library/ms190312.aspx
If sumerdai query won't work try:
SELECT *
 FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','C:\plabos\ina\mjepri.dbf',
 'SELECT * FROM MyTable')

The message you are getting now, Msg 7303 implies that the account that SQLis running as does not have access to the TEMP folder. You need to assign the account permission to write to this folder. Here are some instructions on how to do that, if you need them.

http://blogs.msdn.com/b/spike/archive/2008/07/23/ole-db-provider-microsoft-jet-oledb-4-0-for-linked-server-null-returned-message-unspecified-error.aspx

if i use

if i use this:
SELECT *
 FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','C:\plabos\ina\mjepri.dbf',
 'SELECT * FROM MyTable')


i get this message
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

if i use from blog
SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Text;Database=C:\Temp\;','SELECT * FROM [Test.csv]')

i get thismessage
OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" returned message "'C:\Temp\test.csv' is not a valid path.  Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)".

if i use from blog
SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Text;Database=C:\Temp\;','SELECT * FROM [Test.csv]')

i got csv, but is tat solution of my problem?

Please make sure Office is installed or at least Provider is installed

Office is installed.
How can i check if provider is installed?

Office is there then provider is always there.

Try
"SELECT *
 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0';  ''C:\plabos\ina\mjepri.dbf', MyTable)"

if i try this

SELECT *
 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',  'C:\plabos\ina\mjepri.dbf', 'MyTable')


this is message what i get:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

In case it has not been stated, you do realize that JET needs to be installed on SQL Server, right?  Installing it on the workstation is not going to do you much good.  Secondly, the path to the dbf C:\plabos\ina\mjepri.dbf is relative to SQL Server and not to the workstation.

Okay, I am a little confused about the two post regarding this statement....

When you used
SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Text;Database=C:\Temp\;','SELECT * FROM [Test.csv]')

Did you get the error you listed or did it work?

no i didn't
it was ok

Try this

 

select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=C:\plabos\ina
SourceType=DBF',
'select * from mjepri')

Select allOpen in new window

for this:
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=C:\plabos\ina
SourceType=DBF',
'select * from mjepri'


 i get


OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Driver does not support this function".
Msg 7303, Level 16, State 1, Line 3
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

Okay, let's try a few more things:

1) Please try this command and let us know the results:

 SELECT *  FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:\plabos\ina',
 'SELECT * FROM mjepri.dbf')
2) If that does not work, please try this:
 SELECT *  FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','dbase IV; HDR=No;IMEX=2; database = C:\plabos\ina',  'SELECT * FROM mjepri.dbf')

3) If the problem still exists, let's check the permissions on the dbf file and the folder C:\plabos\ina. Please make sure that the account you are using for SQL Server has write access to the folder. If you made changes, please try the commands in one and two again.

4) If that doesn't work, please try changing the Sql Server service login to Local System and see restart the server. Then try the above commands again.

 

Hi - just wanted to check in and see if you were still experiencing this issue or if it has been resolved. Please let us know if we can continue to be of assistance.

I can't find solution

I can't find solution

Please let me know what the results of these steps are:

1) Please try this command and let us know the results:

 SELECT *  FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:\plabos\ina',
 'SELECT * FROM mjepri.dbf')

2) If that does not work, please try this and let me know the results:
 SELECT *  FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','dbase IV; HDR=No;IMEX=2; database = C:\plabos\ina',  'SELECT * FROM mjepri.dbf')

3) If the problem still exists, let's check the permissions on the dbf file and the folder C:\plabos\ina. Please make sure that the account you are using for SQL Server has write access to the folder. If you made changes, please try the commands in one and two again.

4) If that doesn't work, please try changing the Sql Server service login to Local System and see restart the server. Then try the above commands again.

Thank you for your help!!

1
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


2
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Invalid argument.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Can you please tell me what the permissions are on the dbf file and the folder C:\plabos\ina? Does the  account you are using for SQL Server have write access to the folder?

Where the errors the same after you changed the Sql Server service login to Local System and restarted the SQL server?

Also, are you looking to import this database on an ongoing basis or just this one time?
      

Two more quesations -

1) What version of Office do you have installed?
2) Can you open the file directly with FoxPro?

No

I'm glad that you were able to resolve your issue. Thank you for posting the answer.