OPENROWSET shutting down SQL Server service, but only running on the server
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:pathecelFile.xlsx', [Planilha1$]) it retrieves me the result.BUT if I connect with for example remote desktop or remote 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? there is nothing useful in the server log. What is sql server openrowset?
OPENROWSET is a T-SQL function that allows for reading data from many sources including using the SQL Server's BULK import capability.
Go check your provider (Microsoft.ACE.OLEDB.12.0) settings in SSMS.
Server Objects -> Linked Servers -> Providers -> Microsoft.ACE.OLEDB.12.0 -> Properties
If "Allow in-process" is ticked off, the provider code is running inside the SQL Server process. This can cause the entire process to shut down.