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?
Thanks
Paul GAsked:
Who is Participating?
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 http://www.microsoft.com/en-us/download/details.aspx?id=13255?
0
Paul GAuthor Commented:
It was installed but just in case I have reinstalled it.
Sadly the same error.
0
Éric MoreauSenior .Net ConsultantCommented:
does that new server has access to S:\Excel\Salesperson_Targets.xlsx ?
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Éric MoreauSenior .Net ConsultantCommented:
have you installed the 64-bits version?
0
Paul GAuthor Commented:
Yes to both questions. Tried also with the file on local volume
0
É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 https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7db9f62d-8aa7-4778-8c0a-ee6dc7bf8531/linked-server-to-ms-access-using-jet-returns-unspecified-error?forum=sqldataaccess
0
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.
0
Éric MoreauSenior .Net ConsultantCommented:
probably my last try!

USE [master] 
GO

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

Open in new window


found on https://social.msdn.microsoft.com/Forums/en-US/068761fc-ea49-460d-8098-1c346f3f3852/ole-db-provider-microsoftaceoledb120-for-linked-server-null-returned-message-unspecified?forum=transactsql
0

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:
Result
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.
0
Paul GAuthor Commented:
and now it runs!!!

Thank you!!
0
Éric MoreauSenior .Net ConsultantCommented:
maybe the reconfigure woke up your server!
0
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.
0
Paul GAuthor Commented:
Many thanks
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.