• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2643
  • Last Modified:

dbf in sql

Hi,

How to open dbf in sql2008

i use this:

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

and i get this message

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

thx
0
hrvica5
Asked:
hrvica5
  • 19
  • 13
  • 4
  • +3
1 Solution
 
morguloCommented:
0
 
sumerdaiCommented:
What version of windows are you running? 32 or 64 bit?
0
 
hrvica5Author Commented:
32
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
hrvica5Author Commented:
I USE WIN 7
0
 
morguloCommented:
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
0
 
hrvica5Author Commented:
i installed but i have same problem

How can i install linked server Microsoft.Jet.OLEDB.4.0??
0
 
sumerdaiCommented:
Can you type this into the Run box and tell us what happens:

"regsvr32 C:\WINDOWS\system32\msjetoledb40.dll"
0
 
morguloCommented:
Do you use: SELECT *
 FROM OPENROWSET('provider=Microsoft.ACE.OLEDB.12.0','C:\plabos\ina\mjepri.dbf',
 'SELECT * FROM MyTable')
?
0
 
hrvica5Author Commented:
DIIRegisterServer in
c:\windows\system32\msjetoledb40.dll succeeded
0
 
hrvica5Author Commented:
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.
0
 
sumerdaiCommented:
ok - try the original command again now and see if the message has changed
0
 
hrvica5Author Commented:
again same

Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "provider=Microsoft.Jet.OLEDB.4.0" has not been registered.
0
 
sumerdaiCommented:
hmmm... try this:

 SELECT *
 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:\plabos\ina\mjepri.dbf',
 'SELECT * FROM MyTable')
0
 
hrvica5Author Commented:
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)".
0
 
sumerdaiCommented:
or this:
SELECT * FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="C:\plabos\ina\";Extended Properties=dBASE IV;')...MyTable
0
 
morguloCommented:
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')
0
 
sumerdaiCommented:
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
0
 
hrvica5Author Commented:
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)".
0
 
hrvica5Author Commented:
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)".
0
 
hrvica5Author Commented:
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?
0
 
Alpesh PatelAssistant ConsultantCommented:
Please make sure Office is installed or at least Provider is installed
0
 
hrvica5Author Commented:
Office is installed.
How can i check if provider is installed?
0
 
Alpesh PatelAssistant ConsultantCommented:
Office is there then provider is always there.
0
 
GhunaimaCommented:
Try
"SELECT *
 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0';  ''C:\plabos\ina\mjepri.dbf', MyTable)"
0
 
hrvica5Author Commented:
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)".
0
 
Anthony PerkinsCommented:
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.
0
 
sumerdaiCommented:
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?
0
 
hrvica5Author Commented:
no i didn't
it was ok
0
 
GhunaimaCommented:
Try this

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

Open in new window

0
 
hrvica5Author Commented:
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)".
0
 
sumerdaiCommented:
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.

 

0
 
sumerdaiCommented:
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.
0
 
hrvica5Author Commented:
I can't find solution
0
 
hrvica5Author Commented:
I can't find solution
0
 
sumerdaiCommented:
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.
0
 
hrvica5Author Commented:
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)".

0
 
sumerdaiCommented:
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?
      
0
 
sumerdaiCommented:
Two more quesations -

1) What version of Office do you have installed?
2) Can you open the file directly with FoxPro?
0
 
hrvica5Author Commented:
Thank you very much for your effort, but i find solution.


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

0
 
hrvica5Author Commented:
No
0
 
sumerdaiCommented:
I'm glad that you were able to resolve your issue. Thank you for posting the answer.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 19
  • 13
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now