I have the task of migrating our databases from a Windows 2012 R2 server with SQL Server 11.0.7507 (2012) to a Windows 2019 server with SQL Server 16.0.1110.1 (2022).
I am having issues with opening excel spreadsheets. The following works on the old server but not on the new server.
select * from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=C:\Reports\ETAReport.xlsx;', 'SELECT * FROM [DataSheet$]');
I get the error
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error". Msg 7303, Level 16, State 1, Line 8 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
I have tried everything here https://stackoverflow.com/questions/26267224/the-ole-db-provider-microsoft-ace-oledb-12-0-for-linked-server-null and other similar threads. This is what I have done on the new server:
- Installed the 64 bit of Microsoft Access Database Engine 2010 Redistributable (version 14.0.7015.1000 matches the current server).
- EXEC sp_configure 'show advanced options', 1
- EXEC sp_configure 'ad hoc distributed queries', 1
- EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
- EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
- Given 'Everyone' rights to the folder, checked the file name.
- Restarted the server multiple times.
Many say Error 7303 is file open but it definitely is not open (still happens immediately after restarted server) and office is not installed on the server.
The one thing I have noticed is that on the old server the SQL Instance user (NT Service\MSSQLSERVER) has a user folder in C:\Users\ and when I run the above query on the old server it writes to C:\Users\MSSQLSERVER\AppData\Local\Temp. The new server has NO user folder for the SQL user.
Is SQL 2022 different or is that the missing bit? If so how do I create that folder?
Thanks!
