I have been working on this for some time now and am totally stuck.
I have two identical servers: Server 2003 R2 Enterprise x64 machines running SQL Server 2005 x64 Corporate Edition. One is Production one is Test.
When I run this
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=E:\bulk_import\cab0d978-56df-4858-9e75-574b3b540deb.xlsx', 'SELECT * FROM [Sheet1$]');
on test, data!!
On Production I see this:
Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
Obviously, I have figured out all the steps necessary for downloading the correct driver, installing it, configuring AdHoc Queries, AllowInProcess, DynamicProperties and so on. I went through that trouble in the Test environment and followed the same steps
in Production with no luck.
In all my searching around the only difference between the servers is that Production SQL Server Service is run by Administrator while Test is run by an account called sqlserv.
I have to believe this is permission based. Something somewhere, a folder, or an executable or something is not accessible by this administrator account.
Thanks in advance for any help you may be able to give!
Edited bylreichenThursday, September 05, 2013 12:51 PM