x

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?

Thanks.

more ▼

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

Leo gravatar image

Leo
1.6k 54 56 58

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

95![Error Message][2]95

[2]: /upfiles/System.Data.jpg
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][1]

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.

[1]: http://ask.sqlservercentral.com/questions/28523/importing-excel-data-into-sql-server-table?page=1#28524
more ▼

answered Nov 30, 2010 at 04:10 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

@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][1] otherwise you will probably need to set up an SSIS package to do the import with the right Jet drivers.

[1]: http://www.simple-talk.com/community/blogs/jonathanallen/archive/category/1043.aspx
more ▼

answered Nov 30, 2010 at 03:47 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1834
x115
x26
x24
x6

asked: Nov 30, 2010 at 03:44 AM

Seen: 1151 times

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