Login or Sign Up to become a member!
LessThanDot Sit Logo

LessThanDot

Data Management

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot friendfeed Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Your profile

    Tools

    Tag cloud

    azure book business intelligence ctp database dates denali dmv functions gotcha high availability how to howto indexing mirroring performance sql sql friday sql server sql server 2000 sql server 2005 sql server 2008 sql server 2008 r2 sql server 2012 sql server denali sql server integration services ssis t-sql tip trick

    Authors

    Main Categories

    Search

    XML Feeds

    What is RSS?

    Google Ads

    « When should you store @@ROWCOUNT into a variable?SQL Server Types - Numeric vs Int »
    10
    comments

    Dealing with Microsoft.Ace and OPENROWSET Errors

    by remou on Sep 02, 2010 in categories Microsoft SQL Server
    Instapaper

    I wanted to run a query from SQL Express against Excel, and I found this:

    tsql
    Line number Off | Hide | Select all
    1. SELECT *
    2. FROM OPENROWSET('Microsoft.Ace.OLEDB.14.0',
    3.    'Excel 8.0;DATABASE=c:\docs\testdata.xls', 'Select * from [Sheet1$]')


    Which was just what I wanted, a way to select data from Excel. But it did not work.

    Msg 7403, Level 16, State 1, Line 1
    The OLE DB provider "Microsoft.Ace.OLEDB.14.0" has not been registered.

    It appears that Microsoft.Ace.OLEDB.14.0 is registered as Microsoft.Ace.OLEDB.12.0. Well, I could deal with that:

    tsql
    Line number Off | Hide | Select all
    1. SELECT *
    2. FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0',
    3.    'Excel 8.0;DATABASE=c:\docs\testdata.xls', 'Select * from [Sheet1$]')

    But I still got an error:

    Msg 15281, Level 16, State 1, Line 1
    SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

    I looked a little further and found I needed to enable Ad Hoc distributed queries. It is an advanced option, so:

    tsql
    Line number Off | Hide | Select all
    1. sp_configure 'show advanced option', 1
    2. reconfigure

    Then I must enable ad hoc queries:

    tsql
    Line number Off | Hide | Select all
    1. sp_configure 'Ad Hoc Distributed Queries', 1
    2. reconfigure

    The result of which can be seen with:

    tsql
    Line number Off | Hide | Select all
    1. sp_configure

    But all I got was an error when I ran my query:

    Msg 7399, Level 16, State 1, Line 1
    The OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
    Msg 7330, Level 16, State 2, Line 1
    Cannot fetch a row from OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)".

    I looked some more and I found:

    tsql
    Line number Off | Hide | Select all
    1. EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
    2. GO
    3. EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
    4. GO

    And now I am in business.

    This was all in the past, so I hope I rewound the steps properly, to reproduce the errors.

    64 Bit

    Apparently there is a problem when running 32 bit Office on a 64 bit machine, you will get the following error if you try to run OPENROWSET query:

    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.

    However, I am reliably informed by Denis that 64 bit ACE on a 64 bit machine will work if you follow the steps above.

    9206 views
    Read More Posts by remou
    Instapaper

    10 comments

    Comment from: SQLDenis [Member] Email
    SQLDenis I had no problem on 64 bit windows 7 and 64 bit Office 2010 with the ACE 64 bit drivers.

    On 64 bit windows 7 and 32 bit Office, you can only install 32 bit ACE





    And then you get the error as indicated in your post

    The ACE drivers for both 64 and 32 bit can be downloaded from here: Microsoft Access Database Engine 2010 Redistributable
    09/02/10 @ 11:07
    Comment from: Naomi Nosonovsky [Member]
    Naomi Nosonovsky A bit unrelated info, but you can not send e-mails from Adobe Acrobat if you have installed Office 2010 64 bit.
    Apparently, 32bit software can’t see the mapi client running in x64.
    09/02/10 @ 18:21
    Comment from: na [Visitor]
    na to remou:

    Well, this looked like great information because it delved into some sp_ procedures that are obscure. Unfortuneatly, the ACE debacle was not-so-easily fixed on a WindowsServer2008/SQLServer2008 (64bit) cooperative. Office 2007 SP2 MSO (12.0.6535.5002) components don't correspond to the drivers which are sited here. Any sane user of any of these products would never knowingly go to MS site to find information on the use of MS products and this great fix was ... not so great. ON IT GOES ...

    09/15/10 @ 14:11
    Comment from: RJ Roberts [Visitor]
    RJ Roberts I tried on a Windows 2003 R2 x64 platform using the x64 version of the Access Database Engine 10.0 with no luck.

    SELECT * Into #TempTable From OpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 8.0;Database=\\Servername\Folder\filename.xls;HDR=NO;IMEX=1', 'select * from [Main$]')


    After setting the sp_MSset_oledb_prop properties mentioned above it now returns the very generic error:

    The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
    10/25/10 @ 10:12
    Comment from: SQLDenis [Member] Email
    SQLDenis RJ, can you check with a local file instead..it could be a permission issue on the other server
    10/25/10 @ 11:02
    Comment from: RJ Roberts [Visitor]
    RJ Roberts Thanks!

    Your comment led me down a path that works.

    I tried a local file and it worked but I knew there was no security issue per se. It occurred to me that all my attemps were through management studio from my own desktop connected to the 64 bit server. When I remote desktoped (is that word?) to the server, BOTH the local file and the network file worked. Somehow in the classic double jump scenario OLEDB was getting lost.
    I am a happy camper!
    10/25/10 @ 11:44
    Comment from: Naomi Nosonovsky [Member]
    Naomi Nosonovsky This is a relevant thread with more info.
    01/05/11 @ 12:38
    Comment from: Justin Dearing [Visitor] · http://www.justaprogrammer.net
    Justin Dearing Also be aware if you have a 64-bit SQL instance on a machine without Office installed and you want to query excel spreadsheets from it you can also use the Microsoft Access Database Engine 2010 Redistributable http://www.microsoft.com/download/en/details.aspx?id=13255

    This has the 64 bit OLEDB JET drivers for Excel and Access.
    09/16/11 @ 06:43
    Comment from: Naomi Nosonovsky [Member]
    Naomi Nosonovsky On SQL Server 2008 R2 64 with installed office 2010 64 bit
    I'm trying the following:
    use AdventureWorks

    INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;Database=c:\Test\TestExcel2.xlsx;',
    'SELECT DepartmentID FROM [Sheet1$]')
    SELECT TOP 10 DepartmentID FROM HumanResources.Department
    GO

    and getting this

    OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1$' is not a local object, check your network connection or contact the server administrator.".
    Msg 7350, Level 16, State 2, Line 22
    Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

    ------------
    However, the files do get created, but I'm unable to open them or delete them as they are locked by COM interface.

    What should I do?


    UPDATE Once I closed the query window (and connection) the files disappeared from the directory. Anyway, what should I do to be able to run the query above without an error?
    10/31/11 @ 13:25
    Comment from: Deepak [Visitor] Email
    Deepak Hi all,

    I am running 64-Bit Windows 7, 32-Bit Office 2010 and 64-Bit SQL 2008.
    I tried your step and still getting the error.. :(

    --------------------------------------------------------
    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.
    --------------------------------------------------------

    Any help, guide, tips or tricks...

    Thanks,
    Deepak.
    05/16/12 @ 03:20

    Leave a comment


    Your email address will not be revealed on this site.

    Your URL will be displayed.
    (Line breaks become <br />)
    (Name, email & website)
    (Allow users to contact you through a message form (your email will not be revealed.)

    Powered by B2Evolution
    Valid XHTML | Valid CSS