Microsoft.Jet

Question

Microsoft.Jet

whitesql

Ten Centuries

Points: 1237
More actions
June 18, 2014 at 1:36 am

#294704

Hi guys,
I'm running an openrowset command in SSMS on my machine which is 64 bit.
select * from OpenRowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0; IMEX=1;Database=\\192.168.1.20\upload\data.xls', 'select * from [data1$]' )
Our database server has 32 bit SQL Server 2005 installed.
When I execute openrowset on my machine it gives me an error:
"Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error."
But when I run my openrowset command in our database server, openrowset executed.
Could anyone please give me information why this is happening?
Thank you!!

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 1

The JET provider only has a 32-bit version.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

whitesql Ten Centuries Points: 1237 More actions · Answer 2

Thanks!

so when I access my databass on my machine using ssms and run queries, It uses my machine's Jet and not the one on database server?

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 3

whitesql (6/18/2014)
Thanks!
so when I access my databass on my machine using ssms and run queries, It uses my machine's Jet and not the one on database server?

To be honest, I'm not sure. It seems that way.

edit: what if you try the query with the 32-bit SSMS?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

whitesql Ten Centuries Points: 1237 More actions · Answer 4

it executed successfully on a one 32 bit machine and didn't run on another 32 bit machine.

Maybe something to configure? or add on that machine to make it work?

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 5

The JET OLE DB provider may not be present by default on any machine, so it's possible you need to install it.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

whitesql Ten Centuries Points: 1237 More actions · Answer 6

there is also an oledb on that machine...

My machine which I remotely access databases that has SSMS is 64 bit.

Database server is 32 bit. SQL Server is 32 bit

Office installed on database server is version 2010 32 bit

Excel file I'm trying to import is .xls which is version 2003.

What do you think I should change or uninstall?

Thanks!

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 7

Maybe the error is different on other machines?

With Excel and JET OLE DB, 32-bit is usually the key. If SSMS is 64-bit, that could likely cause an issue.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

whitesql Ten Centuries Points: 1237 More actions · Answer 8

I read somewhere that SSMS is 32 bit even if you have 64 bit of SQL Server so I guess it's not the culprit.

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 9

I think the problem must be with the access to network path

Database=\\192.168.1.20\upload\data.xls

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

whitesql Ten Centuries Points: 1237 More actions · Answer 10

I tried creating a file on my local drive and getting this error..

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\Temp\Products.xls;HDR=No IMEX=1',

'SELECT * FROM [ProductList$]')

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'C:\Temp\Products.xls'. Make sure the object exists and that you spell its name and the path name correctly.".

Msg 7303, Level 16, State 1, Line 2

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 11

Is it on the same machine where the SQL instance installed against which you are running the query ?

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

whitesql Ten Centuries Points: 1237 More actions · Answer 12

Yes... here is now the error..

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "External table is not in the expected format.".

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

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 13

"External table is not in the expected format." typically occurs when trying to use an Excel 2007 file with a connection string that uses: Microsoft.Jet.OLEDB.4.0 and Extended Properties=Excel 8.0

http://stackoverflow.com/questions/1139390/excel-external-table-is-not-in-the-expected-format

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

whitesql Ten Centuries Points: 1237 More actions · Answer 14

Thank you! I used ACE for that one.

Now, I'm getting an error using this code:

select * from OpenRowset('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0; IMEX=1;Database=\\sysdev\data\data.xls', [NPADataToUpload$])

Error:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine cannot open the file '\\sysdev\data\data.xls'. It is already opened exclusively by another user, or you need permission to view its data.".

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

It works fine when I run it on database server. I'm getting that error when I execute it on may laptop remotely accessing instance using SSMS.