January 14, 2013 at 11:51 pm
Hi 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 error
Msg 7308, Level 16, State 1, Line 1
OLE 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 code
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
but still its showing the same error...
any help....
thanks and regards
Sathiyan R
January 15, 2013 at 2:03 am
Check 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 http://www.sqlservercentral.com/Forums/Topic1393640-1292-1.aspx
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.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 15, 2013 at 2:07 am
dwain.c (1/15/2013)
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.
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 15, 2013 at 2:32 am
Koen Verbeeck (1/15/2013)
dwain.c (1/15/2013)
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.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.
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.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 15, 2013 at 2:32 am
In 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)
January 15, 2013 at 2:58 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 15, 2013 at 3:04 am
Koen Verbeeck (1/15/2013)
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.
To read Excel 2007 (.xlsx files), I believe you need this as the first two arguments to OPENROWSET:
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;HDR=NO;Database=filename.xlsx,'SELECT * FROM [Sheet1$]')
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 15, 2013 at 3:30 am
its showing error
Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
January 15, 2013 at 3:42 am
sathiyan00 (1/15/2013)
its showing errorMsg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
Chances are pretty good that you either haven't specified the right path to the file or you still have it open in Excel.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 15, 2013 at 3:51 am
no 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', 1
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
After that it shows
Msg 7308, Level 16, State 1, Line 1
OLE 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.
January 15, 2013 at 1:14 pm
Looks like someone got it to work. Here's the link.
I had similar issues trying to link an Access database to the sql server.
http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/ace
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
January 15, 2013 at 2:29 pm
I'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:
myself(Lowell)
a couple of prerequisites:install the AccessDatabaseEngine_x64.exe from microsoft:
http://www.microsoft.com/en-us/download/details.aspx?id=13255
make sure you open an Administrative command prompt window, and run it with the c:\Downloads\AccessDatabaseEngine_x64.exe /passive
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:
--Required settings for the provider to work correctly as a linked server
EXEC 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
and then the code for the linked server:
--#################################################################################################
--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,@provstr
EXEC 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$;
Lowell
January 15, 2013 at 2:36 pm
That's great stuff Lowell, I didn't know you could force the installation of the provider.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 15, 2013 at 2:49 pm
Koen Verbeeck (1/15/2013)
That's great stuff Lowell, I didn't know you could force the installation of the provider.
Thanks Koen!
I've bean my head against that 64 bit installation rock a few times, and was glad when i discovered that, myself.
Lowell
April 23, 2013 at 5:51 am
Lowell (1/15/2013)
Koen Verbeeck (1/15/2013)
That's great stuff Lowell, I didn't know you could force the installation of the provider.Thanks Koen!
I've bean my head against that 64 bit installation rock a few times, and was glad when i discovered that, myself.
I found this solution so great I made a blog post out of it.
Force installation of 64-bit ACE OLE DB provider
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply