Insert into OPENDATASOURCE with Microsoft.ACE.OLEDB.16.0 provider - failed

  • jirpun

    SSC Journeyman

    Points: 76

    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

  • Phil Parkin

    SSC Guru

    Points: 243790

    Are you attempting to write multiple files, or multiple sheets in the same file?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • jirpun

    SSC Journeyman

    Points: 76

    No, there are writing into multiple (different) files.

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

     

     

  • William Rayer

    Say Hey Kid

    Points: 676

    It can be difficult to link Excel and SQL Server and many different obscure errors are possible. The issue here is likely because you are running multiple imports at the same time. Microsoft documentation https://www.microsoft.com/en-US/download/details.aspx?id=13255  (point 4 under Details) hints the OLE DB drivers should not be used by multiple users concurrently. Can you change your process to loop through the files one at a time?

    On another note it has always amazed me that it's so tricky to integrate Microsoft SQL Server and Excel when both are flagship Microsoft products!

  • jirpun

    SSC Journeyman

    Points: 76

    William, you are right, the concurency seems to be the problem.

    It's not possible to change our application to create only one  excel at a time. So we decided to go another way - use the OpenXml SDK and use it in the SQLCLR procedure.

    Thanks to all.

    jirpun

     

     

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

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