question

Leo avatar image
Leo asked

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.
sql-server-2008excelopenquery64-bitjet
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
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.
1 Like 1 ·
Leo avatar image Leo commented ·
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][1] 95![Error Message][2]95 [1]: /upfiles/Using_Distributed_Queries_-_AskSSC_-_Google_Chrome.jpg [2]: /upfiles/System.Data.jpg
0 Likes 0 ·

2 Answers

·
WilliamD avatar image
WilliamD answered
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
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leo avatar image Leo commented ·
@William Thanks. Sorted. By the way I don't understand why my screen shorts are disappeared.
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
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.
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered

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.

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leo avatar image Leo commented ·
Yes, I did create the SSIS at the end. Thanks.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.