Export data to Excel from SQL Server using OPENROWSET issue

Question

I am using the below script and got the following error:

Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

EXEC SP_CONFIGURE 'show advanced options', 1; 
GO 
RECONFIGURE; 
EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries', 1; 
GO 
RECONFIGURE; 

INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 
'Excel 12.0;Database=c:\CSV\testing.xlsx;', 
'SELECT * FROM [Sheet1$]') 
select top 10 * from Emp2

How can I fix this? I need to export data to Excel file in UNC path. Please guide me how to achieve it.

Again I try the below script too but this time I am getting a different error:

Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

INSERT INTO OPENROWSET 
   ('Microsoft.Jet.OLEDB.4.0', 
   'Excel 8.0;Database=c:\CSV\testing.xlsx;','SELECT ID,Name FROM [Sheet1$]')
   select top 10 * from Emp2

Please guide me how to export data to an Excel file which will be generated in UNC path.

I see that the file path you supplied is 'C:\...'; is the file located on a storage device that is local to the machine hosting the SQL Server? — mendosi
– mendosi, Commented Nov 13, 2016 at 12:12
Does the server have either of those two OLEDB providers installed? SSMS -> server objects -> linked servers -> providers — mendosi
– mendosi, Commented Nov 13, 2016 at 12:17

ASH · Accepted Answer · 2016-12-26 22:48:00Z

Here are a couple things to try.

Export data to existing EXCEL file from SQL Server table

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;', 
    'SELECT * FROM [SheetName$]') select * from SQLServerTable

If you don't want to create an EXCEL file in advance and want to export data to it, use

EXEC sp_makewebtask 
    @outputfile = 'd:\testing.xls', 
    @query = 'Select * from Database_name..SQLServerTable', 
    @colheaders =1, 
    @FixedFont=0,@lastupdated=0,@resultstitle='Testing details'

To export data to new EXCEL file with heading(column names), create the following procedure

create procedure proc_generate_excel_with_columns
(
    @db_name    varchar(100),
    @table_name varchar(100),   
    @file_name  varchar(100)
)
as

--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
select 
    @columns=coalesce(@columns+',','')+column_name+' as '+column_name 
from 
    information_schema.columns
where 
    table_name=@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
exec(@sql)

--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''
exec(@sql)

--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec(@sql)

--Delete dummy file 
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)


After creating the procedure, execute it by supplying database name, table name and file path

EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'

All details are here...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

what is sp_makewebtask ? where is the code for sp_makewebtask ?

Collectives™ on Stack Overflow

Export data to Excel from SQL Server using OPENROWSET issue

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related