SQLServer Tips: Distributed Query Issues With ACE Provider in 64 bit

Here is another quick blog on solution for an issue you may face while working with ACE provider in 64 bit windows server

Scenario

A colleague of mine came to me today morning with an issue. He was trying out few distributed query options using OPENROWSET for exporting some data from Excel 2007 for an adhoc requirement utilizing the ACE provider . Suddenly he got a transport error and couldn't connect to instance anymore.
I started a remote desktop session to the server and to my surprise I saw SQL Server service and SQL Agent agent being down. I restarted them and asked him to try again and monitored myself this time. The OPENROWSET query fetched the results and immediately after that gave the same transport error. At the same time the services for SQL Server and SQL Agent went down. So it was obvious that ACE provider distributed query was causing the services to be down.
I tried the same query in my server and it worked fine. But we were consistently able to reproduce the issue in the problem server. My server was having Windows Server 2008 R2 as the OS whereas the one with the problem had Windows Server 2012 as the OS.

Solution

I was not sure what should be done for solving this however I decided to apply the below specified steps to see if it fixes the issue.

http://visakhm.blogspot.com/2013/12/how-to-solve-microsoftaceoledb120-error.html

as they also related to ACE provider issues

As I expected doing the above steps fixed the issue.
The steps that were of significant relevance in the above case were the below

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

Once this is applied the distributed queries started working fine without affecting SQLServer services.
Something to lookout for the next time when you experience similar problems!