Sample Header Ad - 728x90

SQL Lockups after ACE/ODBC Drivers Updated

1 vote
1 answer
505 views
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
Asked by Depth of Field (233 rep)
Sep 16, 2022, 01:49 AM
Last activity: Sep 19, 2022, 01:04 AM