how to user correctly function OPENROWSET to access excel spreadsheet as SQL table?

CARLITO SENA 0 Reputation points
2025-01-21T15:25:31.4566667+00:00

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.

SQL Server on Azure Virtual Machines
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.