Using Distributed Queries

I am trying to use OpenRowSet to import XLS file into Database in SQL 2008 and I got the following error -

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.

Here is my queries -

 SELECT * INTO dbo.Table1
 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel
 8.0;DataSource=C:\TEST.xls','SELECT * FROM [sheet1$]')

I am using 64bit Windows 2003 and SQL Server 64bit version... According from the Microsoft, Jet Driver is only runs on 32 bit version. Is that correct?
Do we have any other work around to make it work?


more ▼

asked Nov 30, 2010 at 03:44 AM in Default

avatar image

1.6k 55 59 62

Where is that option to change 64 bit to 32 bit run time in SSIS package?

Also - getting error in SSIS pacakge Project Properties Pages

95Error Message95

Nov 30, 2010 at 04:44 AM Leo

In the first picture you supply there is the debug option "Run64BitRuntime" - set this to false.

Please read through the answers in the link I provided, Oleg and Pawel explain it quite well there.

Nov 30, 2010 at 05:20 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Just for completeness, please take a look at this previous question about Excel Imports in a 64Bit environment

Jet 4.0 was never ported to 64 Bit (it caused a huge outcry but MS never did anything about it!). @Oleg and @Pawel both provide great info into how it can still be done.

more ▼

answered Nov 30, 2010 at 04:10 AM

avatar image

26.2k 18 38 48

@William Thanks. Sorted.

By the way I don't understand why my screen shorts are disappeared.

Nov 30, 2010 at 07:11 AM Leo

Your answer was turned into a comment and that removed pictures (they are not part of comments). That seems to be a current "feature" of OSQA.

Nov 30, 2010 at 07:39 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

Personally, if this is an infrequent/one-off task I would save the Excel data as a csv file and use LogParser - http://www.simple-talk.com/community/blogs/jonathanallen/archive/category/1043.aspx otherwise you will probably need to set up an SSIS package to do the import with the right Jet drivers.

more ▼

answered Nov 30, 2010 at 03:47 AM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

Yes, I did create the SSIS at the end. Thanks.

Nov 30, 2010 at 03:54 AM Leo
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 30, 2010 at 03:44 AM

Seen: 1383 times

Last Updated: Nov 30, 2010 at 04:12 AM

Copyright 2018 Redgate Software. Privacy Policy