Error reading data from Excel file using Microsoft.ACE.OLEDB.12.0

I have a simple query to select data from an Excel spreadsheet that I know used to work correctly without error.
The only change we have done is to move the 2008 R2 SQL server into a VMWare VM and upgrade the OS from Windows 2018 R2 to Windows 2012 Datacenter.

The query is
SELECT Period,[ANameGBP] FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=S:\Excel\Salesperson_Targets.xlsx;', 'SELECT * FROM [Targets$]')

After some delay this error is returned and the SQL server crashes and restarts.

Msg 109, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.)
The SQL log shows only the restart info.

I resurrected my old server and tried on this and it works correctly

Any ideas what may be causing this?
Paul GAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
have you installed the OLEDB driver from
Paul GAuthor Commented:
It was installed but just in case I have reinstalled it.
Sadly the same error.
Éric MoreauSenior .Net ConsultantCommented:
does that new server has access to S:\Excel\Salesperson_Targets.xlsx ?
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Éric MoreauSenior .Net ConsultantCommented:
have you installed the 64-bits version?
Paul GAuthor Commented:
Yes to both questions. Tried also with the file on local volume
Éric MoreauSenior .Net ConsultantCommented:
Permission issues?

open SSMS
expand Server Objects, Providers
double click the provider (Microsoft.Jet.OLEDB.4.0)
UNTICK the "Allow inprocess" option

found on
Paul GAuthor Commented:
I don't have (Microsoft.Jet.OLEDB.4.0) as I didn't on old server.
Checked this setting against Microsoft.ACE.OLEDB.12.0 and it is unticked.
Éric MoreauSenior .Net ConsultantCommented:
probably my last try!

USE [master] 

EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'ad hoc distributed queries', 1
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 

Open in new window

found on

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Paul GAuthor Commented:
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.
Paul GAuthor Commented:
and now it runs!!!

Thank you!!
Éric MoreauSenior .Net ConsultantCommented:
maybe the reconfigure woke up your server!
Paul GAuthor Commented:
Looking at the Provider Options for Microsoft.ACE.OLEDB.12.0 the following are now ticked

Dynamic Parameter & Allow inprocess

Thanks again.
Paul GAuthor Commented:
Many thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.