OPENROWSET shuting down SQL Server service, but only running on the server. ssms REMOTELY is ok.

Question

I've never seen such issue.

when i'm using SSMS from my own machine, normally conected to the server I want to run the query, I can run openrowset to select from an excel file with no problems:

SELECT top 10 * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=D:\path\ecelFile.xlsx', [Planilha1$])
it retrieves me the result.

BUT if I connect with for example remote desktop or mRemote to the server, and run this query inside it, the server just shuts down:

Msg 64, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

I've never seen this. even testing with an excel with 10 rows and 3 columns, the SQL server service just shut down. and running from my machine to the server via SSMS, it never fails.

What could this be? theres nothing useful in the server log.

Answer 1

It's not supported to load the Microsoft.ACE.OLEDB.12.0 driver in SQL Server's process.

The Access Database Engine 2016 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).
2. As a replacement for the Jet OLEDB Provider in server-side applications.
3. 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.)
4. 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.

https://www.microsoft.com/en-us/download/details.aspx?id=54920

You should have better luck using an SSIS package where the process is short-lived, you can run in either 32 or 64bit, and you can configure it to run using an account that has a profile loaded.

Also for SQL 2017/2019 and Azure Data Factory you can use the SSIS Power Query Source too.

Answer 2

Hi @RacerX_DBA ,

Could you please try to create a linked server to access Excel data using the Microsoft.ACE.OLEDB.16.0 OLE DB provider instead? Please refer to this tutorial and documentation which might help.
In addition, what the version of the SQL Server are you using? Please install the latest Cumulative Update or Service Pack for SQL Server.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet