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$]')
