Post reply

OPENROWSET issue with 64bit vs 32bit

  • July 10, 2012 at 6:03 am

    #263499

    I have windows 7-64bit/ SQL Server 2008 R2-64bit; but my MS office is 32 bit/ ODBC Drivers 32 bit.

    I have a situation to use OPENROWSET function as below:

    SELECT * From OpenRowset('Microsoft.Jet.OLEDB.4.0',';Database=C:\data\Testdb.mdb;','SELECT * from table1') as b

    But I get error msg as below:

    “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 do have 1 for ‘Ad Hoc Distributed Queries'.

    I have been working on this issue last 2 days using many online tips, but no luck. Pls help.

    Thanks in advance

  • July 10, 2012 at 7:16 am

    This was removed by the editor as SPAM

  • July 10, 2012 at 7:32 am

    #1510909

    MS office 2007 32 bit, is installed.

    Could you give more detail; what SQL Express engine? do i have to use SQL express..I have deverloper 2008 R2 64bit

  • July 10, 2012 at 7:34 am

    #1510910

    try openrowset like this, using the aCe driver;

    this is a tested working example for me:

    SELECT * FROM OPENROWSET('MSDASQL',

    'DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);

    UID=admin;

    UserCommitSync=Yes;

    Threads=3;

    SafeTransactions=0;

    ReadOnly=1;

    PageTimeout=5;

    MaxScanRows=8;

    MaxBufferSize=2048;

    FIL=excel 12.0;

    DriverId=1046;

    DefaultDir=C:\Data\BlockGroups_2010;

    DBQ=C:\Data\BlockGroups_2010\AKblockgroup.xls',

    'SELECT * FROM [AK$]')

    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

    'Excel 12.0;Database=C:\Data\BlockGroups_2010\AKblockgroup.xls',

    'SELECT * FROM [AK$]')

    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!

  • July 10, 2012 at 7:43 am

    #1510916

    lowell,

    I get err msg respectively:

    -----------------------------------------------

    OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".

    Msg 7303, Level 16, State 1, Line 1

    Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

    -----------------------------------------------

    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.

    -----------------------------------------------

    thanks for your hlep

  • July 10, 2012 at 7:44 am

    #1510917

    lowell,

    where is aCe driver?

    I understand what is ACE driver, I too installed it from

    http://www.microsoft.com/en-us/download/details.aspx?id=23734

    thanks

  • July 10, 2012 at 7:50 am

    #1510925

    Joe-420121 (7/10/2012)

    lowell,

    I get err msg respectively:

    -----------------------------------------------

    OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".

    Msg 7303, Level 16, State 1, Line 1

    Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

    -----------------------------------------------

    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.

    -----------------------------------------------

    thanks for your hlep

    i meant the ACE driver, sorry about the case thing; i have to watch my fingers type, and not the screen 🙂

    based on the first error, I'm thinking the error is in the path to the file itself. Data source name not found to me means bad path to teh file?

    For the second error message, it looks like the ACE drivers are already installed; I would have to google the error message to track down why it failed.

    the drivers are on the microsoft site for reference;

    http://www.microsoft.com/en-us/download/details.aspx?id=13255

    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!

  • July 11, 2012 at 6:06 am

    #1511465

    lowell,

    could you tell me your enviroment?

    what is windows version? 32bit or 64bit?

    what is sql version ? 32 bit or 64 bit?

    what is MS access version ? 32bit or 64bit?

    what is ODBC version ? 32bit or 64bit?

    thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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