We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

dbf in sql

hrvica5
hrvica5 asked
on
Medium Priority
2,927 Views
Last Modified: 2012-08-14
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
Comment
Watch Question

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

Author

Commented:
32

Author

Commented:
I USE WIN 7

Commented:
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

Author

Commented:
i installed but i have same problem

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

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

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

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

Author

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

Author

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.

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

Author

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.

Commented:
hmmm... try this:

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

Author

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

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

Commented:
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')

Commented:
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

Author

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

Author

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

Author

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?
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

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

Author

Commented:
Office is installed.
How can i check if provider is installed?
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
Office is there then provider is always there.
G Trurab KhanSnr. Development Manager
CERTIFIED EXPERT

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

Author

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)".
CERTIFIED EXPERT
Top Expert 2012

Commented:
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.

Commented:
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?

Author

Commented:
no i didn't
it was ok
G Trurab KhanSnr. Development Manager
CERTIFIED EXPERT

Commented:
Try this

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

Open in new window

Author

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

Commented:
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.

 

Commented:
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.

Author

Commented:
I can't find solution

Author

Commented:
I can't find solution

Commented:
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.

Author

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

Commented:
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?
      

Commented:
Two more quesations -

1) What version of Office do you have installed?
2) Can you open the file directly with FoxPro?
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
No

Commented:
I'm glad that you were able to resolve your issue. Thank you for posting the answer.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.