Post reply

OPENROWSET vs OPENDATASOURCE Issues

  • June 27, 2020 at 4:12 pm

    #3765339

    Hi All,

    I've recently been working on importing Excel sheets through Ad hoc queries and there is something I found very odd. Below is an OpenRowSet query to extract Sheet1 from TEST.xlsx:

    SELECT * 
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
                    'Excel 12.0 XML;
                     HDR = Yes;
                     Database = "F:\SQLTEST\TEST.xlsx";',
                    [Sheet1$])

    Now we have an almost identical query using OpenDataSource:

    SELECT * 
    FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
                        'Data Source = "F:\SQLTEST\TEST.xlsx"; 
                         Excel 12.0 XML; 
                         HDR = Yes;')
                        ...[Sheet1$]

    The OpenDataSource query works perfectly fine and displays my test sheet, however OpenRowSet produces the following error:

    Msg 7399, Level 16, State 1, Line 3
    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 7303, Level 16, State 1, Line 3
    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

    I've tested this on both SQL Server 2008 R2 10.50 64bit (which is what I mainly work on) and have also tested on SQL Server 2017 14.0 64bit that are both on completely different systems and both produce the same results. I've also tried Microsoft.ACE.OLEDB.16.0, however that either produces the same error or completely crashes SQL Server.

    I have full access to the document, all the required permissions and settings enabled for my account and for the ACE provider, so in this case why does OpenDataSource work but OpenRowSet does not? (Document is completely closed and there are no stale processes in the background). Am I doing something wrong with the OpenRowSet connection string? From the dozens of forums, articles, questions and documentation I have trawled through, I see that same format used everywhere. I've tried changing it up (both order of operations and what's actually included), however nothing seems to work. I see some users have Database first but this produces an error with the ISAM not being at the beginning.

    Kind Regards,

    Bailey.

  • June 28, 2020 at 5:10 pm

    #3765378

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 1 (of 1 total)

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