Microsoft ACE OLEDB 12.0 X64 Driver freezing using open rowset
-
Hi I am running SQL Server 2005 x64 sp4 on Windows Server 2003sp2 x64 in production. (8gb sql server configured
min 100mb max 6400mb)
We have recently moved an application and database to this server and upgraded to using the ms ace oledb x64 driver
for an automated process that uses openrowset to import excel files.
It works for a while and then hangs. No error messages the process just keeps running and does not stop.
The only way to resolve it is to restart the sql service.
I have setup a test machine like for like and scheduled a job to run the command below every minute. After about 500 runs it freezes as well. Restart the sql service and it stars running fine
DECLARE @cmd VARCHAR(MAX)
SET @cmd='SELECT * FROM OPENROWSET(
''Microsoft.ACE.OLEDB.12.0'',
''Excel 12.0;Extended Properties=Excel 12.0;Database=D:\testme.xls;HDR=NO;IMEX=1'',
''SELECT * FROM [Sheet1$]'')'
EXEC (@cmd)
go
-
Also D is a local to the server.
I have also killed of the thread using process manager however this causes the SQL Server and the openrowset works again.
-
Just discovered that a service pack exists.
http://support.microsoft.com/kb/2460011
Not sure if this will resolve my issues only one way to find out. 🙂
-
Even with service pack installed get the same behavior.
Does anyone have any suggestions/ideas. Looks like going to x64 is not a good idea
-
I have now performed the same test on a x86 SQL Server build using the 32 bit drivers and I have not been able to repeat it.
I will assume ms x64 ace drivers are rubbish and are not ideal when calling openrowset on a regular basis.
So 32 bit world we stay.
-
Having the same issue with the x64 edition of Access Database Engine (Microsoft.ACE.OLEDB.12).
Running a query or stored procedure with OPENROWSET or OPENDATASOURCE works 99 times out of a hundred. Then all of the sudden (whenever it is feeling temperamental) it either throws up an error or just simply hangs. I managed to capture the actual error once in testing while logged in through SSMS:
SELECT
*
FROM
OPENDATASOURCE( 'Microsoft.ACE.OLEDB.12.0',
'Data Source="C:\dir\test.xlsm";
Extended properties=Excel 8.0')...Sheet1$
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider reported an unexpected catastrophic failure.
If you examine sysprocesses you see that it leaves the respective command (ie. SELECT, INSERT, EXECUTE, etc.) open in a "RUNNABLE" status with a WaitSource of "Microsoft.ACE.OLEDB.12.0" and the process cannot be killed..... If you attempt to kill the process it simply puts the process into an infinite Rollback. Attempting to kill the rollback process yields:
SPID [####]: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
The ONLY solution I have found is to restart SQL or Reboot the Server - which is almost never an option for us. Any further calls made to the Provider queue up and hang indefinitely.
Have been searching for a solution for x64 that works consistently - as all of our SQL Server instances run in 64-bit environments. Its a shame, but we are actually considering the deployment of an x86 server on our network which would use DTS Jobs to replicate data to/from our ERP Databases just so that this Provider will work without hanging up. :pinch:
-
Experienced the same problem when moving from sql server 32 bit to sql server 64 bit.
Stored procedures that have run fine as Jet 4.0 also run fine in 64 bit ACE 12.0. Well that is until some unknown executions of the stored procedures throws up the nasty messages which you have documented before this post.
Microsoft was kind enough to enlighten me in my paid technical support call that using it in stored procedures was not supported by them.
Symptom:
You have some stored procedures in SQL Server 2008 that used to run without issue. You have upgraded SQL Server to the 64 bit version and you are now seeing the following error message: "The OLE DB provider 'Microsoft.ACE.OLEDB.12.0' for linked server "(null)" reported an error. The provider reported an unexpected catastrophic failure."
Cause:
This is an unsupported scenario.
Resolution:
We were able to provide you with the following information:
Here is the article talking about supported scenarios.
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=13255
Here is the information I want to call out.
In the Overview section number 4 talks system services using ACE not being supported. My understanding is that linked servers fit this scenario exactly.
In the additional information section, bullet number 2, we give a recommendation on a supported scenario involving SQL Server. You can use the import wizard or SSIS. In order to use SSIS the jobs need to run in the context of a logged-on user with a valid HKEY_CURRENT_USER registry hive.
Overview
This download will install a set of components that facilitate the transfer of data between existing Microsoft Office files such as Microsoft Office Access 2010 (*.mdb and *.accdb) files and Microsoft Office Excel 2010 (*.xls, *.xlsx, and *.xlsb) files to other data sources such as Microsoft SQL Server. Connectivity to existing text files is also supported. ODBC and OLEDB drivers are installed for application developers to use in developing their applications with connectivity to Office file formats.
The Access Database Engine 2010 Redistributable is not intended:
As a general replacement for Jet (If you need a general replacement for Jet you should use SQL Server Express Edition).
As a replacement for the Jet OLEDB Provider in server-side applications.
As a general word processing, spreadsheet or database management system -To be used as a way to create files. (You can use Microsoft Office or Office automation to create the files that Microsoft Office supports.)
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. Examples would include a program that is run from task scheduler when no user is logged in, or a program called from server-side web application such as ASP.NET, or a distributed component running under COM+ services.
Additional information
The Office System Drivers are only supported under certain scenarios, including:
Desktop applications which read from and write to various files formats including Microsoft Office Access, Microsoft Office Excel and text files.
To transfer data between supported file formats and a database repository, such as SQL Server. For example, to transfer data from an Excel workbook into SQL Server using the SQL Server Import and Export Wizard or SQL Server Integration Services (provided the SSIS jobs run in the context of a logged-on user with a valid HKEY_CURRENT_USER registry hive).
If SSIS is not an option, you can try a bulk insert since you are using text and CSV files. I have added articles on doing that below. If you need help with the articles, please reply all to this email and my co-worker Dennis will be able to assist you in getting in touch with the SQL Team who can assist with that.
BULK INSERT (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms188365.aspx
BCP Utility
http://msdn.microsoft.com/en-us/library/ms162802.aspx
Based on this being an unsupported scenario we went ahead and non-decremented the case.
Given the lack of current support by Microsoft in this area and not feeling comfy with them supporting it in the future I am currently moving ahead with this path:
In areas where I can control the interfaces and quality of data, I have moved onto BCP to provide a solution. This will be an enormous undertaking given the volume of modifications/testing needed.
In areas where the interface and quality of data I do not have control of, I will move these items back to a 32 bit environment.
-
Were you able to find a resolution for this issue?
We have multiple production servers, all on SQL Server 2014 latest SP and running ACE OLEDB 12.0
Only on one of the servers, excel import queries get stuck once every 3-4 weeks. When this happens, I restart the service to make it work again.
Killing the process results in a rollback that never completes. I have tried closing the connections using TCPView, that has not worked.
Any pointer will help a great deal. Thanks.
https://sqlroadie.com/
-
I have struggled with this on-and-off over a number of years. Even though SQL Server and Excel are best-of-breed products that have been around for 20 years or more, it is a cumbersome and error-prone process to import data from Excel into SQL Server. There are many pages of information on the web (probably 1000's) that attest to the difficulties that folks encounter when importing into SQL Server, and it is very disappointing that Microsoft cannot make the effort to ensure these products work together.
Anyway I have found some things that help:
* Apply the SQL Server configuration settings to allow ad-hoc distributed queries
* Use the Microsoft.Ace.OLEDB.12.0 drivers for xls and xlsx files
* Install the Microsoft Access Database Engine 2010 Redistributable (64 bit version), this requires there to be no 32 bit version of Office on the computer
* Use sp_MSset_oledb_prop to set AllowInProcess = 1 (without this option, OPENROWSET sometimes causes the SQL Server Service to stop running)
* Add the -g256 or -g512 startup parameter to the SQL Server Service (this gives more memory for the ACE drivers)It would be really useful if Microsoft could fully support importing Excel files into SQL Server.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply