Excel openrowset works on 32 bit but not 64 bit?!?!

Question

Excel openrowset works on 32 bit but not 64 bit?!?!

Ninja's_RGR'us

SSC Guru

Points: 294069
More actions
August 25, 2010 at 12:27 pm

#237096

I've been around all the usuall suspects, I got the right file path, the command works on another server, it's not permissions issues...
SELECT *
from openrowset('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\TireGuideImport\tgpro_oe_1002b_excel;HDR=YES',
'select * from [tgpro_oe_1002b_excel$]')
I got this error and apparently there's no plan to have a 64 bit version of the driver (which is why the driver is missing on windows 2008 server 64 bit).
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.
Any easy way around this without learning and installing SSIS?

Viewing 11 posts - 1 through 10 (of 10 total)

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

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 1

Ah, yes, the old Jet issue. Not only did I have to use SSIS, I had to run it at the command prompt using the 32 bit version of SSIS because the 64 bit version couldn't do it either. AFAIK there is no other way but should someone else have a way I don't know of I would GREATLY appreciate adding it to my toolkit. Once burned a week trying to solve that.

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 2

There wouldn't be a 64-bit Jet Driver. Perhaps with the latest Office2010-64bit edition

With SSIS

http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/70636/

WayneS SSC Guru Points: 95460 More actions · Answer 3

You won't be able to do this with the Jet engine on 64-bit.

I thought I had read somewhere about a month ago that there is now a 64-bit driver for Excel, but I can't find anything with Google.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 4

You might consider writing this out as a CSV file - Excel will directly import it.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

It is kind of convoluted when you search for it..

I believe this will get you what you want:

Access Database Engine 2010

Follow the link..

CEWII

WayneS SSC Guru Points: 95460 More actions · Answer 6

Elliott Whitlow (8/25/2010)
It is kind of convoluted when you search for it..
I believe this will get you what you want:
Access Database Engine 2010
Follow the link..
CEWII

Thanks Elliott - I KNEW I had seen this somewhere recently.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Thanks you all, I'll try this out next monday.

Have a great week-end

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 8

This functionality is only about 5 years late.

They should have had this ready in 2005...

You guys have a good week.

CEWII

WayneS SSC Guru Points: 95460 More actions · Answer 9

Elliott Whitlow (8/25/2010)
This functionality is only about 5 years late.
They should have had this ready in 2005...
You guys have a good week.
CEWII

Agreed - it should have been ready when 64-bit SQL started really being pushed. One of the things that users want in most companies is excel spreadsheets - and not being able to generate it is absurd.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 10

Well we could generate them but having to jump through hoops because a provider wasn't available is absurd.

When we switched from 32 to 64-bit sql this was one of the very first problems I experienced and about the only advice at the time was run it with the 32-bit DTExec. There were NO other methods.

I am hoping these new drivers will fully resolve the issue with JET based drivers,

CEWII