Leer Excel desde SQL Server

descargar Access Database Engine X64.exe

https://www.microsoft.com/en-us/download/details.aspx?id=13255


instalarlo dentro del server con el siguiente parámetro   /passive

C:\Users\desacop>C:\DW\AccessDatabaseEngine_X64.exe /passive

sp_configure 'show advanced options', 1;

RECONFIGURE;

GO

sp_configure 'Ad Hoc Distributed Queries', 1;

RECONFIGURE;

GO

RECONFIGURE;

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 

ejecutar:

DECLARE @cmd VARCHAR(255)

SET @cmd = 'bcp "My Select Query Here" queryout "My Excel File Path Here" -U linkuser -P linkuser01 -c'

Exec xp_cmdshell @cmd

para ver posibles errores

The setting can be applied as follows
Open SQL Server Configuration Manager -> Services -> SQLServer service.
Right click and choose properties.
Go to advanced tab and append -g512; to startup parameters property and it will resolve

SELECT * INTO Data_dq

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

    'Excel 12.0; Database=C:\BASURA\reporte.xls', [R$]);

GO

SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source=C:\BASURA\reporte.xls;Extended Properties=Excel 8.0')...[R]

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=E:\OOlguinRemate@gmail.com\PROYECTOS\11.- DESARROLLOS\Excel a SQL\reporte.xls', [R$])

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=E:\OOlguinRemate@gmail.com\PROYECTOS\11.- DESARROLLOS\Excel a SQL\reporte.xls', 'SELECT * FROM [R$]')