locked
Update Excel spreadsheet using ACE driver (Microsoft.Ace.OLEDB.12.0) in 64bit environment. RRS feed

  • คำถาม

  • Hi all,

    Well, I finally thought I'd try to ditch the dogleg through 32bit SQL Express and try to update spreadsheets (XLS) using the Microsoft.Ace.OLEDB.12.0 driver. Well, what can I say? It selects from the spreadsheet fine but as soon as I try an update statement it falls over straight away with:

    (For xls as linked server)

    OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "test" returned message "Bookmark is invalid.".

    Msg 7346, Level 16, State 2, Line 2

    Cannot get the data of the row from the OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "test".

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

    For xls using OPENROWSET

    OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)" returned message "Bookmark is invalid.".

    Msg 7346, Level 16, State 2, Line 2

    Cannot get the data of the row from the OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)".

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

    Just for completeness I also tried using the MSDASQL driver but that failed miserably too.

    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=D:\filepath\;DBQ=D:\filepath\filename.xls'

    ,'SELECT * FROM [NamedRange]'

    )

    gives

    Msg 7390, Level 16, State 2, Line 2

    The requested operation could not be performed because OLE DB provider "MSDASQL" for linked server "(null)" does not support the required transaction interface.

    Linked server = null is interested as there is no problem if I use select.
    • แก้ไขโดย JCEH 20 พฤศจิกายน 2555 14:45
    20 พฤศจิกายน 2555 14:39

ตอบทั้งหมด

  • You can use the SSIS Import/Export Wizard to export/import data to/from Excel:

    http://www.sqlusa.com/bestpractices/ssis-wizard/

    It is a great tool! Very flexible! Reliable.


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    20 พฤศจิกายน 2555 21:44
  • Hi JCEH,

    Could you please tell us what's the version of your SQL Server and Excel? Since the SQL Server is 32 bit, please ensure that the ACE provider is 32 bit too. If not, you can download it from here:

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

    Since the "select" clause can be used, please check the MSDTC service is enabled in both the two sides(SQL and Excel)

    see: http://msdn.microsoft.com/en-us/library/ms190799.aspx

    http://msdn.microsoft.com/en-us/library/aa561924(v=bts.20).aspx


    Iric Wen

    TechNet Community Support

    • ทำเครื่องหมายเป็นคำตอบโดย Iric Wen 29 พฤศจิกายน 2555 9:51
    • ยกเลิกการทำเครื่องหมายเป็นคำตอบโดย JCEH 2 ตุลาคม 2561 12:35
    22 พฤศจิกายน 2555 6:54
  • I don't like SISS. It is fine if you are developing on a specific instance but not very flexible when you have to do the same for many instances and environments on lots of different client sites. The overhead is quite simply ridiculous. However, given the issues I keep coming across between 64bit/32bit and Jet/ACE drivers I can only assume it is pretty darn-clever under the skin. So, SISS isn't going to happen.

    I have the issue on 64bit Server 2008R2 running 64bit SQL with 64bit ACE and also 32bit SQL running 32bitit ACE. It seems to be an inherent issue with ACE. I also note that where client environments have a mix of 64bit SQL and 32bit SQL (for obvious reasons given JET functionality and historic applications) that 64bit ACE and 32bit ACE are mutually exclusive - you cannot have both loaded.

    Incidentally, I have applied service pack 1 for the ACE drivers.

    Cheers,

    J

    19 มิถุนายน 2556 10:09
  • This is NOT the answer. Please do not try to guess if it is the answer or not. If it is the answer then I will mark it as such. To lie or guess certainly won't help anyone else experiencing the issue.

    As it happens I still have the issue, this time using the ACE drivers to try and update an Excel spreadsheet as a linked server. In this instance it seems to have made some assumption on what the column names are and it is inconsistent between SELECT and UPDATE.


    JCEH

    2 ตุลาคม 2561 12:39
  • After...five years???
    2 ตุลาคม 2561 12:47
  • And a few years more. 

    I think the ACE drivers are buggier than a cockroach driving a VW Beetle. I'm pretty sure the legend that is Jeff Moden reported something to Microsoft and it just got dropped without being fixed. If anyone knows him maybe give him a nudge?!

    If it helps, I think the IMEX setting overrides some things and causes issues. The following tests demonstrate this.

    Make a spreadsheet called SQLtest with a tab called Test and save it as XLSB. In Row 1 put A in column A, B in column B and c in column c. I pit 3 rows of random numbers below these letters making 4 rows of data. Save it, in my case in D:\ where I know I have full control and so does SQL.

    --- Test SELECT
    SELECT *
    FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 
    'Excel 12.0;Database=D:\SQLtest.xlsb;HDR=YES;IMEX=0', 
    'SELECT * FROM [Test$]' -- OK
    ) as SLT
    GO

    --- Test update - Header and auto data type
    UPDATE SLT SET C='100' -- Works even though column numeric
    FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 
    'Excel 12.0;Database=D:\SQLtest.xlsb;HDR=YES;IMEX=0', 
    'SELECT * FROM [Test$]' 
    ) as SLT
    WHERE A='3' -- Works even though this is numeric
    GO
    /*
    RESULT - OK
    (1 row affected)
    */

    --- Test update - Header and force data type to text
    UPDATE SLT SET C='100' -- FAILS!
    FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 
    'Excel 12.0;Database=D:\SQLtest.xlsb;HDR=YES;IMEX=1', 
    'SELECT * FROM [Test$]' 
    ) as SLT
    /*
    RESULT - FAILURE
    OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)" returned message "Cannot update. Database or object is read-only.".
    Msg 7399, Level 16, State 1, Line 24
    The OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)" reported an error. The provider indicates that the user did not have the permission to perform the operation.
    Msg 7343, Level 16, State 4, Line 24
    The OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)" could not UPDATE table "[Microsoft.Ace.OLEDB.12.0]". 
    */

    --- Test update - no header and data type automatic
    UPDATE SLT SET C='100' -- Works!
    -- UPDATE SLT SET F3='100' -- Fails as F3 does not exist
    FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 
    'Excel 12.0;Database=D:\SQLtest.xlsb;HDR=NO;IMEX=0', -- IMEX=0 automatically overrides HDR=NO
    'SELECT * FROM [Test$]' -- Need single quotes and square brackets!
    ) as SLT
    WHERE A='3' -- Works even though this is numeric and column should be F1
    GO
    /*
    RESULT - OK with modification
    (1 row affected)

    */


    --- Test update - no header and data type automatic

    UPDATE SLT SET F3='100' -- FAILS!
    FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 
    'Excel 12.0;Database=D:\SQLtest.xlsb;HDR=NO;IMEX=1', 
    'SELECT * FROM [Test$]' -- Need single quotes and square brackets!
    ) as SLT
    WHERE F1='2'
    GO
    /*
    RESULT - FAILURE
    OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)" returned message "Cannot update. Database or object is read-only.".
    Msg 7399, Level 16, State 1, Line 54
    The OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)" reported an error. The provider indicates that the user did not have the permission to perform the operation.
    Msg 7343, Level 16, State 4, Line 54
    The OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)" could not UPDATE table "[Microsoft.Ace.OLEDB.12.0]". 
    */


    JCEH

    7 สิงหาคม 2563 16:14
  • And a few years more. 

    And you are still using the ACE drivers?

    I have never used them to talk to Excel, and judging from all problems I've seen about on the forums, I doubt that I ever will. Uusually when I want to get data from/to Excel to SQL Server I do that in a client language, in my case that is typically Perl or PowerShell.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    7 สิงหาคม 2563 20:59