SQL Server Export to Excel with Openrowset

SQL Server export to Excel with OPENROWSET

You'd have to use dynamic SQL. OPENROWSET etc only allows literals as parameters.

DECLARE @myfile varchar(800)

SET @myfile = 'C:\template.xls'

EXEC ('
insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', 
''Excel 8.0;Database=' + @myfile + ';'', 
''SELECT * FROM [SheetName$]'') 
select * from myTable
')

Remember: the path is relative to where SQL Server is running

Export SQL result to Excel by using OPENROWSET, but provider has not been registered

to summarize:

In order to use OPENROWSET for export/import excel files from/to x64 SQL Server you should install Access Database Engine 2010 Redistributable on your server, not on your workstation. Until you successfully install this x64 bit driver on your server pc you'll get the error

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered

while trying to use this provider in your OPENROWSET queries

Using OPENROWSET to export dataset into specific range in Excel sheet

There is no way how to do this via opendatasource/openrowset...

Opendatasource is half-unproduced functionality. It's probably not done to work with data but only for ad-hoc connections.

---

Related Topics