June 22, 2007 at 2:32 am
Hi,
We have a fresh installed server with Windows 2003 64-bit and with SQL server 2005 64-bit.
Some stored procedures use Jet OLEDB to load data from excel files.
However, on the new system, those procedures doesn't work on the new server. We get the error: "Microsoft.Jet.OLEDB.4.0" has not been registered. However registering the DLL's doesn't resolve the problem.
And from information we have found on the net, we see there is no 64 bit version of the driver, and Microsoft will not port the driver to 64-bit.
We found some information on the net about this error, but no solution.
The statement we are now using looks like:
SELECT "Key", "Value" FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data source="\\SRV2005\Exchange\Config.xls";User ID=;Password=;Extended properties=Excel 5.0')...Config$
Also using MSDASQL is no option. Also the odbcjt32.dll is giving the same error.
So our question: is there someone who has a TSQL solution for the problem?
tnx.
June 25, 2007 at 3:29 am
As you said, there is no 64bit version of Jet.
A way to do it would be to use SSIS, under 32bit emulation, to import the info.
I don't know of a way to run SQL x64 in 32bit emulation for a certain query..
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 25, 2007 at 8:46 am
Just a thought, and I don't have SS2K5 to test it on...try using SQL Server Import with one of your excel files, and save as package (save as DTS in SS2K) - then examine connection properties to identify the oledb driver?
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 11, 2009 at 6:27 am
Ok all the final word on Jet
The Microsoft OLE DB Provider for Jet and the Microsoft Access ODBC driver are available in 32-bit versions only
http://support.microsoft.com/kb/957570
So frankly what this is telling me is if your using the 64 bit version of SQL server than you can not connect to excel files either via a linked server, OPENDATASOURCE, or reporting services. WOW.
Talk about a chink in the armor I'm stunned. There are articles on the web that show how to run SSIS in 32 bit mode to support Jet or a IIS server and other MS apps in 32 bit mode, but is that really a solution or a step backwards. [Crazy]
So lesson learned is step back and think about that for a second before you decide to upgrade your server from 32bit to 64bit. You can go out on the web and read the horror stories of people who did, only to break existing code (ouch).
Peace out...
February 11, 2009 at 6:38 am
Actually the ODBC to use with JET is in the WOW64 directory of the server so you ned to use that because as the others have said there is no x64 version of JET. If you cannot find it post again and I will show you the location.
Kind regards,
Gift Peddie
February 11, 2009 at 6:52 am
Mr. Peddie,
Are you stating that there is a provider that you can register that will work to get past the original posters error with OPENDATASOURCE
SELECT "Key", "Value" FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data source="\\SRV2005\Exchange\Config.xls";User ID=;Password=;Extended properties=Excel 5.0')...Config$
If so please enlighten me to the method of doing so and I will surely praise your glory from where to Wichita.
Peace out...
February 11, 2009 at 7:19 am
Yes the person needs to install Win2003 SP2 then you will find the X86 ODBC which is the 32bits version at the connection panel.
And here is the DSN directories.
To setup DSN for 32-bit application you must use:
%WINDIR%\SysWOW64\odbcad32.exe
and for 64-bit application you must use:
%WINDIR%\System32\odbcad32.exe
BTW it is MS Peddie.
Kind regards,
Gift Peddie
February 11, 2009 at 8:22 am
Gift Peddie,
Thanks for the quick reply will test.
February 11, 2009 at 8:34 am
It works the problem is when Windows 2003 was released it comes without the x86 version ODBC but SP2 added it and fixed many data related issues.
I have helped people for years at the MSDN forum using Access or Excel, the only requirement your application must be x86 which 32bits because JET is only 32bits.
This is what I call the dependent dll issue because you have a 32bits dll as part of your operation everything must run as 32bits. The IIS issue is fixed in IIS 7 you can run both 32bits and 64 bits application. Check below for all the bugs fixed by Win2003 SP2.
http://support.microsoft.com/kb/914962
Kind regards,
Gift Peddie
February 11, 2009 at 9:11 am
Gift Peddie,
I don't want to take up much more of your day, so one last post.
I have read a ton of facts today about JET, the new XML office format, OLEDB, and OPENDATASOURCE. All are as enlightening as they are confusing to me.
For me (maybe most people) if something works I do not question the why's I just plug it together and run with it. OPENDATASOURCE and spreadsheets where one of those things.
Now it seems to me like I'm stuck in a place where old technology is going away and new technology is here but not fully developed or maybe just not understood by me.
Bottom line is I want to be able to consume the contents of a spreadsheet in my T-SQL code. I don't want to make a SSIS package I simply want to bring in a spreadsheet and save it to a temp table then run the rest of my logic against the temp table.
From what I can see so far that is not an option.
I realize if I use a new version of Office it's now stored as XML back end instead of JET so maybe some day that will be an option for consumption via t-sql.
Ok so I guess the final though is I'm now off to write an SSIS package that will run in 32BIT mode instead of a SPROC (my preferred coding style)
Thanks for all the help Gift Peddie, if only for putting up with my ramblings.
Peace out.....
February 11, 2009 at 9:37 am
Here is code I use it uses the JET directly without OPENDATASOURCE try running in with the 32bits ODBC.
/* Excel as a linked server */
/* Assuming we have an Excel file 'D:\testi\Myexcel.xls'
with following data in the first sheet:
id name
1 a
2 b
3 c
*/
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'D:\testi\Myexcel.xls',
NULL,
'Excel 5.0'
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false'
EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource
SELECT *
FROM ExcelSource...Sheet1$
CREATE TABLE test_excel
(id int,
name varchar(255))
GO
INSERT INTO test_excel
SELECT *
FROM ExcelSource...Sheet1$
SELECT *
FROM test_excel
/* Now define two ranges in Excel on the 2nd sheet as tables */
/* Select the range, Insert->Name->Define */
/* Note: sp_tables_ex does not recognize the defined tables */
/* We can still refer to the tables explicitly */
EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource
SELECT *
FROM ExcelSource...Table1
SELECT *
FROM ExcelSource...Table2
Kind regards,
Gift Peddie
September 29, 2009 at 11:37 am
Hello Gift Peddie,
This code you posted didn't work in the 64bit SQL Server 2005 on Windows 2003 Server, Service Pack 2. The error was
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.
In this same post, in an earlier thread, you told the path to the odbccad32.exe, but it is the ODBC panel so we have to configure a DSN to each excel sheet? And if we do add a DSN to the ODBC panel, it won't work the way you told, because you will still be using the JET, which is not supported.
I have a situation where several xls files will be in a directory and the name is according to some internal pattern that can be matched by one stored procedure. But if I have to create a DSN to every xls I'm lost. I like to use OPENDATASOURCE the way MudLuck told.
So, is there a way to import an Excel Sheet, without using SSIS and without using a DSN to every file?
Thanks,
DBA Cabuloso
Lucas Benevides
________________
DBA Cabuloso
Lucas Benevides
September 29, 2009 at 12:31 pm
I have a situation where several xls files will be in a directory and the name is according to some internal pattern that can be matched by one stored procedure. But if I have to create a DSN to every xls I'm lost. I like to use OPENDATASOURCE the way MudLuck told.
When Excel workbooks comes with defined names that is complicated so your need SSIS package and I am not aware that ODBC x86 which is 32bits not covering JET because it covers both JET and ACE which is for Office 2007. I think you should create SSIS package and use stored procedure to call SQL Server Agent to run it. If you are still having problem then start a new thread thanks.
Kind regards,
Gift Peddie
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply