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

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

 

 

Answer 1

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]

Answer 2

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

 

Answer 3

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, [e-mail]

Answer 4

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.

Answer 5

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 [e-mail]

Answer 6

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.