I am trying to import an Excel file into SQL Server for that I decided to use OPENROWSET.
I have done below steps:
Step 1:-
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
Step2:-
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
Step3:-
Installed AccessDatabaseEngine_X64.Exe file.
Step4:-
SELECT* FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 8.0;Database=D:\Share\c.xls;',
'SELECT * FROM [Sheet1$]')
Here I got ERROR like this
"The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered."
But above all the steps executed without any error.
When I run this below query
Exec sp_MSset_oledb_prop
I can't find "Microsoft.ACE.OLEDB.12.0" in the list. Server Configuration blow: OS: Windows Server 2016 - 64bit SQL: Sql Server 2017
The same way I have followed the above Steps working fine 2 different servers.
Kindly help me to resolve this issue.