how to user correctly function OPENROWSET to access excel spreadsheet as SQL table?
see bellow the commands that I used and the mistakes that I received and the same error happend in SQL server management Studio and Azure Data Studio.
USE [master]
--CONFIGURANDO À INSTÂNCIA SQL PARA ACEITAR OPÇÕES AVANÇADAS
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
--HABILITANDO O USO DE CONSULTAS DISTRIBUÍDAS
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
--ADICIONANDO OS DRIVERS NA INSTÂNCIA SQL
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'AllowInProcess', 1
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'DynamicParameters', 1
--CONSULTANDO UMA PLANILHA
SELECT
cgc_cpf,
razao_social,
classificacao
FROM OPENROWSET('Microsoft.ACE.OLEDB.16.0',
'Excel 12.0; Database=D:\QueryExcel\pagamentos_classificacao.xlsx; HDR=YES; IMEX=1',
'SELECT * FROM [classificacao$]')
group by cgc_cpf, razao_social, classificacao
MESSAGE ERROR:
Msg 7403, Level 16, State 1, Line 19
The OLE DB provider "Microsoft.ACE.OLEDB.16.0" has not been registered.