Excel 64 bit driver

  • sathiyan00

    SSCommitted

    Points: 1879

    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

  • Dwain Camps

    SSC Guru

    Points: 86873

    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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • Koen Verbeeck

    SSC Guru

    Points: 258890

    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

  • Dwain Camps

    SSC Guru

    Points: 86873

    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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • sathiyan00

    SSCommitted

    Points: 1879

    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)

  • Koen Verbeeck

    SSC Guru

    Points: 258890

    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

  • Dwain Camps

    SSC Guru

    Points: 86873

    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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • sathiyan00

    SSCommitted

    Points: 1879

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

  • Dwain Camps

    SSC Guru

    Points: 86873

    sathiyan00 (1/15/2013)


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

    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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • sathiyan00

    SSCommitted

    Points: 1879

    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.

  • Mike.Hahn

    SSCarpal Tunnel

    Points: 4051

    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

  • Lowell

    SSC Guru

    Points: 323354

    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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Koen Verbeeck

    SSC Guru

    Points: 258890

    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

  • Lowell

    SSC Guru

    Points: 323354

    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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Koen Verbeeck

    SSC Guru

    Points: 258890

    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 20 total)

You must be logged in to reply to this topic. Login to reply