1

Our SQL server 2014 (has all the latest Service packs & cumulative updates) has been experiencing sporadic memory dumps/lockups (which I posted in another thread). Runs on Windows Server 2012 R2.

Our server often uses SQL OPENROWSET statements for importing XLSX/CSV/TXT files. Part of the recommendations to prevent memory dumps, was to update The Microsoft ACE (Access Database engine drivers) and SQL ODBC Drivers to the latest versions.

Ever since the driver update 3 days ago, queries with OPENROWSET statements sporadically hang endlessly, this happens every 3-4 hours. Our only workaround is to restart the SQL service. These hangups do NOT generate any memory dumps. And they only affect queries that leverage OPENQUERY (the server works fine otherwise).

I've included below:

(A) list of all the installed 64-but ODBC drivers and their versions, as well as (B) Some sample OPENROWSET statements that are used, showing the different providers that are used.

Any idea if/how we should treat this matter and/or whether any of these driver updates should be rolled back? If so, which ones? Could there be an overlap of the new/old ones causing an issue?

SELECT * FROM OPENROWSET ('MSDASQL','DRIVER={MICROSOFT access TEXT DRIVER (*.TXT, *.CSV)};', 'SELECT * FROM F:\IMPORTS\MyFile.CSV')

Using ACE 12.0 (2010) provider:

SELECT * FROM OPENROWSET('MICROSOFT.ACE.OLEDB.12.0','TEXT;DATABASE=F:\IMPORTS\;HDR=YES;', 'SELECT * FROM MyFile.CSV')

Using ACE 16.0 (2016) provider:

SELECT * FROM OPENROWSET('MICROSOFT.ACE.OLEDB.16.0','TEXT;DATABASE=F:\IMPORTS\;HDR=YES;', 'SELECT * FROM MyFile.CSV')

ODBC Drivers installed:

Name                                            Version             Company
Microsoft Access dBASE Driver ".dbf. ".mdx)     16.00.4999.1000     Microsoft Corporation 
Microsoft Access Driver (".mdb. '.accdb)        6.00.4999.1000      Microsoft Corporation 
Microsoft Access Text Driver *.txt, ".csv)      16.00.4999.1000     Microsoft Corporation 
Microsoft Excel Driver (".xlsx, ".xlsm, ".xlsb) 16.00.4999.1000     Microsoft Corporation 
ODBC Driver 11 for SQL Server                   2014.120.6439.10    Microsoft Corporation 
ODBC Driver 18 for SQL Server                   2018.181.01.01      Microsoft Corporation 
Oracle in Oraaientllg_home2                     11.02.00.01         Oracle Corporation
SQL Server                                      6.03.9600.17415     Microsoft Corporation
SQL Server Native Client 11.0 Version           2011.110.7462.06    Microsoft Corporation

enter image description here

2
  • If you read the ACE Driver information (though it's kind of hidden at the bottom) it'll tell you that it's only supported for SQL Server in the use of Import/Export wizard and SSIS. "The Office System Drivers are only supported under certain scenarios, including: 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)." Commented Sep 16, 2022 at 13:14
  • Thanks Sean - We've been using OPENROWSET for quite sometime, without issues (well, on the "most" part LOL). We can't pivot (nor have the resources) to using SSIS for importing office files just like that. I'm hoping some sort of ACE/ODBC driver rollback might bring it back to a somewhat stable state. Commented Sep 16, 2022 at 18:45

1 Answer 1

1

We solved this! When then drivers got updated, the ACE & MSDASQL provider settings got reset to the default, which had the Allow Inprocess function enabled. We simply unchecked it for the providers that use OPENROWSET, and has been working fine since.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.