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 table1It 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
October 17, 2019 at 12:33 pm
Are you attempting to write multiple files, or multiple sheets in the same file?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 17, 2019 at 1:40 pm
No, there are writing into multiple (different) files.
The exception is connected to the memory issue, not the access into the files.
October 28, 2019 at 11:28 am
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!
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply