none
Insert into OPENDATASOURCE with Microsoft.ACE.OLEDB.16.0 provider - failed (EXCEPTION_ACCESS_VIOLATION) RRS feed

  • Question

  • Hi all,

    we export data into Excel file from SQL Server 2017 with a simple command (a part of a stored procedure): 

    The command looks like:

    INSERT INTO OPENDATASOURCE('Microsoft.ACE.OLEDB.16.0', 'Excel8.0;HDR=YES;IMEX=1;Database=\\some_folder\excel1.xls')...Sheet1
        SELECT * FROM table1

    It works fine. But only if just one export is processing at the same time.

    If more exports are processing simultaneously, the exports crashes, an exception is raised.

    "SqlDumpExceptionHandler: Process 68 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process."

    One process is terminated, the other ones are hanging and no other export is possible to start, unless restarting the sql server service.

    We are not sure, is it a problem of OLEDB driver or of SQL server service?

    Thank you for any advice, 

    jirpun

     

     

    Thursday, October 17, 2019 8:46 AM

Answers

  • Due to Microsoft declaration https://www.microsoft.com/en-us/download/details.aspx?id=54920:

    "The Access Database Engine 2016 Redistributable is not intended:

    To be used by a system service or server-side program where the code will run under a system account, or will deal with multiple users identities concurrently, or is highly reentrant and expects stateless behavior. "

    and others information sources investigations I realized that using ACE OLDEDB driver is a dead end a we will try to find any substitution.

    Thanks all for theirs replies.

    • Marked as answer by jirpun Friday, October 18, 2019 9:50 AM
    Friday, October 18, 2019 9:50 AM

All replies

  • We are not sure, is it a problem of OLEDB driver or of SQL server service?

    Neither nor. At one time only one process can have write access to a file, not more.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, October 17, 2019 8:54 AM
    Moderator
  • Of course, the command in my question is only a sample - in fact there are different target files. 

    The exception is connected to the memory access violation, not the access to files.

    jirpun

     
    Thursday, October 17, 2019 10:00 AM
  • We are not sure, is it a problem of OLEDB driver or of SQL server service?

    I'm inclined to blame Excel as such. Excel does not have a good behaviour when you work with multiple files. Say that I work with one Excel, but right now I have it minimized. For whatever reason I open a second Excel file. Now the other Excel window pops up. And once you have had this crash, there will be Excel processes around.

    Exactly how are you doing these exports? Would it be possible to package the export in to a stored procedure that everyone uses? In such cases, you could serialize access with an application lock.


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

    Thursday, October 17, 2019 9:12 PM
  • Exactly how are you doing these exports? Would it be possible to package the export in to a stored procedure that everyone uses? 

    These exports are parts of an application, users invoke them indirectly - call stored procedures, or in addition some SQL Server jobs call these procedures too. The names of target files are unique, there are no collisions in the file level.

    This application work for years, everything was right with SQL Server 2008R2 and driver Microsoft.ACE.OLEDB.12.0.

    This year we migrated to the SQL Server 2017 and driver Microsoft.ACE.OLEDB.16.0.
    For clarification - the driver is set "Allow inprocess" (SSMS - Server Objects Linked Servers - Providers).

    Once again I would like to say - one export at the same time works, more exports in parallel crash. 

    IMHO, there are many internal changes between SQL server 2008R2 and SQL server 2012+. 
    So, I realize something changed in memory management inside SQL server service and it's the reason for the problem. The OLEDB driver looks like NO-reentrant.

    Friday, October 18, 2019 5:54 AM
  • Hi jirpun,

     

    >>This year we migrated to the SQL Server 2017 and driver Microsoft.ACE.OLEDB.16.0.

    For clarification - the driver is set "Allow inprocess" (SSMS - Server Objects Linked Servers - Providers).

     

    I suggest you install the latest cu first, if you still have this problem. It seems to be a kown issue.This happens because it is not recommended to have external modules loaded into the SQL Server memory, as they can cause these types of unexpected behaviors. To try and troubleshoot this issue I propose the following action plan:

     

    In the SSMS, open “Server Objects” -> “Linked Servers” -> “Providers”

    Select the OLEDB provider, right click and go to “Properties”

    In the “Properties” window uncheck the “Allow InProcess” option

    Reboot the server

     

    For more details, please refer to  https://support.microsoft.com/en-us/help/2295405/sql-server-service-crashes-when-you-run-an-oracle-linked-server-query

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, October 18, 2019 7:43 AM
  • Due to Microsoft declaration https://www.microsoft.com/en-us/download/details.aspx?id=54920:

    "The Access Database Engine 2016 Redistributable is not intended:

    To be used by a system service or server-side program where the code will run under a system account, or will deal with multiple users identities concurrently, or is highly reentrant and expects stateless behavior. "

    and others information sources investigations I realized that using ACE OLDEDB driver is a dead end a we will try to find any substitution.

    Thanks all for theirs replies.

    • Marked as answer by jirpun Friday, October 18, 2019 9:50 AM
    Friday, October 18, 2019 9:50 AM