InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 15:59:49 GMT20http://www.sqlservercentral.com/Forums/Topic1407044-391-1.aspxKudos to Lowell for revealing the secret to using the command line to force installation of the 64-bit ACE driver on a machine with 32-bit Office apps. I ran into that wall a couple weeks ago and gave up fighting it - Lowell's solution solved my problem, so I can now run openrowset queries against Excel files. Thanks!Thu, 25 Apr 2013 09:56:10 GMTJohn Brauerhttp://www.sqlservercentral.com/Forums/Topic1407044-391-1.aspx[quote][b]Lowell (1/15/2013)[/b][hr][quote][b]Koen Verbeeck (1/15/2013)[/b][hr]That's great stuff Lowell, I didn't know you could force the installation of the provider.[/quote]Thanks Koen!I've bean my head against that 64 bit installation rock a few times, and was glad when i discovered that, myself.[/quote]I found this solution so great I made a blog post out of it.[url=http://blogs.lessthandot.com/index.php/DataMgmt/ssis/force-ace-installation]Force installation of 64-bit ACE OLE DB provider[/url]Lowell, I gave you credit for this solution in the blog post, however I did not found a blog/website/linkedin/whatever of you where I could point to.If you want me to link to something of you to give proper credit, let me know.Tue, 23 Apr 2013 05:51:54 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1407044-391-1.aspx[quote][b]Koen Verbeeck (1/15/2013)[/b][hr]That's great stuff Lowell, I didn't know you could force the installation of the provider.[/quote]Thanks Koen!I've bean my head against that 64 bit installation rock a few times, and was glad when i discovered that, myself.Tue, 15 Jan 2013 14:49:47 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1407044-391-1.aspxThat's great stuff Lowell, I didn't know you could force the installation of the provider.Tue, 15 Jan 2013 14:36:55 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1407044-391-1.aspxI've been in on a few other ACE /EXCEl threads, and have my comments saved from when i thouroughly tested this and got it to work:[quote][b] myself(Lowell)[/b][hr]a couple of prerequisites:install the [b] AccessDatabaseEngine_x64.exe [/b] from microsoft:[b][url]http://www.microsoft.com/en-us/download/details.aspx?id=13255[/url][/b]make sure you open an Administrative command prompt window, and run it with the c:\Downloads\AccessDatabaseEngine_x64.exe [b]/passive[/b]command line flag;this will force the install of the drivers, even if you have 32 bit office installed;otherwise you get some error about 32 bit Office preventing the install.After that is isntalled:[code]--Required settings for the provider to work correctly as a linked serverEXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 GO EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 [/code]and then the code for the linked server:[code]--#################################################################################################--Linked server Syntax for Excel with ACE 64 driver--#################################################################################################DECLARE @srvproduct nvarchar(256), @provider nvarchar(256), @location nvarchar(100), @provstr nvarchar(100), @catalog sysname, @sql varchar(1000)--add an excel spreadsheet as a linked server.SET @server = 'MyExcelACE'SET @srvproduct = ''SET @provider = 'Microsoft.ACE.OLEDB.12.0'set @provstr = 'Excel 12.0'SET @datasrc ='C:\Data\BlockGroups_2010\AKblockgroup.xls'EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,NULL,@provstrEXEC dbo.sp_AddLinkedSrvLogin @server, FALSE, NULL, Admin, NULL--what spreadsheets (table equivilents are available?EXEC sp_tables_ex 'MyExcelACE'--you MUST know the name of the spreadsheet; --spreadsheet name has a dollar sign at the end of it!--I've personally never gor a spreadsheet that has a space in it--for example "Activity Data" =ActivityData$ never got [Activity Data$] or [Activity Data]$ to work--to work, so I end up editing the spreadsheet to remove spaces if that happens.select * from MyExcelACE...ActivityData$;select * from MyExcelACE...Sheet1$;[/code][/quote]Tue, 15 Jan 2013 14:29:05 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1407044-391-1.aspxLooks like someone got it to work. Here's the link.I had similar issues trying to link an Access database to the sql server.[url]http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/ace[/url]Tue, 15 Jan 2013 13:14:02 GMTWant a cool Sighttp://www.sqlservercentral.com/Forums/Topic1407044-391-1.aspxno excel is not open,and the path also correct.i run the following code EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1GOEXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1GOAfter that it shows Msg 7308, Level 16, State 1, Line 1OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.Tue, 15 Jan 2013 03:51:38 GMTsathiyan00http://www.sqlservercentral.com/Forums/Topic1407044-391-1.aspx[quote][b]sathiyan00 (1/15/2013)[/b][hr]its showing errorMsg 7302, Level 16, State 1, Line 1Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".[/quote]Chances are pretty good that you either haven't specified the right path to the file or you still have it open in Excel.Tue, 15 Jan 2013 03:42:08 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1407044-391-1.aspxits showing errorMsg 7302, Level 16, State 1, Line 1Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".Tue, 15 Jan 2013 03:30:41 GMTsathiyan00http://www.sqlservercentral.com/Forums/Topic1407044-391-1.aspx[quote][b]Koen Verbeeck (1/15/2013)[/b][hr]I think you can use the 2010 ACE OLE DB driver to read from 2007 workbooks. It's possible you'd have to change the Excel version in the connection string.[/quote]To read Excel 2007 (.xlsx files), I believe you need this as the first two arguments to OPENROWSET:[code="sql"]SELECT *FROM OPENROWSET([b]'Microsoft.ACE.OLEDB.12.0', [/b] '[b]Excel 12.0[/b];HDR=NO;Database=filename.xlsx,'SELECT * FROM [Sheet1$]')[/code]Tue, 15 Jan 2013 03:04:35 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1407044-391-1.aspxI think you can use the 2010 ACE OLE DB driver to read from 2007 workbooks. It's possible you'd have to change the Excel version in the connection string.Tue, 15 Jan 2013 02:58:45 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1407044-391-1.aspxIn some websites i seen that 64 bit is only from MSoffice 2010.But I'm using 2007 so it's 32 bit right.and OS windows 7 (64 bit)SQL SERVER 2008 R2(64 bit)Tue, 15 Jan 2013 02:32:18 GMTsathiyan00http://www.sqlservercentral.com/Forums/Topic1407044-391-1.aspx[quote][b]Koen Verbeeck (1/15/2013)[/b][hr][quote][b]dwain.c (1/15/2013)[/b][hr]You must have all 3 running in the same mode. The driver won't allow you to install 32 bit in a 64 bit environment.[/quote]You can install a 32-bit JET provider in a 64-bit environment. However, if you have 64-bit Office installed (which you shouldn't on a server), then you can't install the 32-bit provider or vice versa.[/quote]Koen - I confess you caught me speculating a bit. When I was doing this, I was doing it on my laptop and I couldn't sort the problem until all were in sync. Thanks for setting the record straight.Tue, 15 Jan 2013 02:32:06 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1407044-391-1.aspx[quote][b]dwain.c (1/15/2013)[/b][hr]You must have all 3 running in the same mode. The driver won't allow you to install 32 bit in a 64 bit environment.[/quote]You can install a 32-bit JET provider in a 64-bit environment. However, if you have 64-bit Office installed (which you shouldn't on a server), then you can't install the 32-bit provider or vice versa.Tue, 15 Jan 2013 02:07:00 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1407044-391-1.aspxCheck to make sure MS Office, your Windows OS and SQL Server are all either 32 bit or 64 bit and then download the correct set of Jet drivers from the MS site [url]http://www.sqlservercentral.com/Forums/Topic1393640-1292-1.aspx[/url]You must have all 3 running in the same mode. The driver won't allow you to install 32 bit in a 64 bit environment.Tue, 15 Jan 2013 02:03:13 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1407044-391-1.aspxHi all, I am running the following query in sql server 2008 r2(64-bit),MS office 2007 excel file.SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=<loc>', 'SELECT * FROM [Sheet1$]')its showing errorMsg 7308, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.i run the following codesp_configure 'show advanced options', 1;GORECONFIGURE;GOsp_configure 'Ad Hoc Distributed Queries', 1;GORECONFIGURE;GObut still its showing the same error...any help....thanks and regardsSathiyan RMon, 14 Jan 2013 23:51:14 GMTsathiyan00