Read from Excel for O365 in SQL Server 2019

Question

Post reply

Read from Excel for O365 in SQL Server 2019

pietlinden

SSC Guru

Points: 63503
More actions
June 27, 2020 at 8:58 pm

#3765352
I'm trying to read the contents of an Excel file, so I'm attempting to follow the instructions from this article: https://www.mssqltips.com/sqlservertip/6178/read-excel-file-in-sql-server-with-openrowset-or-opendatasource/
and when I try to run this:
```
use Testdb;
GO

EXEC master.[sys].[sp_MSset_oledb_prop] N'Microsoft.ACE.OLEDB.12.0', N' 'DisallowAdHocAccess'', 1

EXEC sp_configure 'show advanced option', '1';
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

EXEC master.[sys].[sp_MSset_oledb_prop] N'Microsoft.ACE.OLEDB.16.0', N'AllowInProcess', 1;

-- this next line fails
/*
Msg 109, Level 20, State 0, Line 12
    A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.)

Completion time: 2020-06-27T16:43:56.7641811-04:00
*/
SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.16.0',
  'Excel 16.0 Xml;Database=C:\Users\User\Documents\MikeyData.xlsx;', Sheet1$);
```
I get the following error:
Msg 109, Level 20, State 0, Line 12
A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.)
Am I missing something silly? I guess I could use SSIS to do this, but that seems like using a sledgehammer on an ant... Is there a bit of information I left out? I'm perplexed, because I thought I was following the instructions, and still no joy.
<update>
Okay, I tried this:
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=C:\Users\User\Documents\MikeyData.xlsx;Extended Properties=EXCEL 12.0')...[Sheet1$];
and for some super weird reason that I don't understand, the SQL Server service stops?
Before the service was set to Automatic, and status is blank instead of "Running" (What gives?)
- This topic was modified 4 years, 4 months ago by pietlinden. Reason: more information

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

WebWizardry Grasshopper Points: 11 More actions · Answer 2

I'm getting the exact same issue with both Microsoft.ACE.OLEDB.12.0 and Microsoft.ACE.OLEDB.16.0. I have 2 setups:

Live: Windows Server Essentials 2012 R2 with SQL Server Express 2016 and NO office installation.

Dev: Windows 10 pro X64 with SQL Server 2017 Developer and Office 365.

Live server opens files fine. Dev used to import same file, with same stored proc, using OPENROWSET no problem. Since having O365 it fails...

OPENROWSET error:

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver] Your network access was interrupted. To continue, close the database, and then open it again.".

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x2818 Thread 0x1990 DBC 0xcffc81d8 Excel'.".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

OPENDATASOURCE error:

Msg 109, Level 20, State 0, Line 5

A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.)

Also crashes SQL Server and you have to start it again.

!! EDIT !! :

I connected to Dev server as "sa" and it works fine??? I've always used windows auth before with no issues. It's not file system permissions as I tried granting "Everyone" full access to files and windows auth still didn't work.

This reply was modified 4 years, 3 months ago by WebWizardry.