Hi,

Is there a way to extract data from a table in to excel inside a stored procedure and not using SSIS?

Thanks,

Preetha

Hello,

What about this? Something like this is what you have in mind?

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;',
'SELECT Name, Date FROM [Sheet1$]')
SELECT [Name], GETDATE() FROM msdb.dbo.sysjobs
GO

Reference:
http://www.mssqltips.com/sqlservertip/1202/export-data-from-sql-server-to-excel/

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

• Edited by Alberto MorilloMVP, Editor 6 hours 39 minutes ago

Hi,

I get this error when I tried to run this:

Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

Run below script.

Fix/Workaround/Resolution:

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

Then the above script will work.

I am still getting the same error. IS Microsoft.Jet.OLEDB.4.0 an inbuilt linked server?

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
Why dont you use Import/Export option in SSMS.

I want to automate the process so writing this in SP would be ideal

Thats y want to use it n see

Hello,

What about this? Something like this is what you have in mind?

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;',
'SELECT Name, Date FROM [Sheet1$]')
SELECT [Name], GETDATE() FROM msdb.dbo.sysjobs
GO

Reference:
http://www.mssqltips.com/sqlservertip/1202/export-data-from-sql-server-to-excel/

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

• Edited by Alberto MorilloMVP, Editor Friday, January 24, 2014 4:50 AM

In addition , you can use OPENDATASOURCE or create linked server to EXCEL.

SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
   'Data Source=c:\book1.xls;Extended Properties=Excel 8.0')...Sheet1$

for insert its

insert into OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
   'Data Source=c:\book1.xls;Extended Properties=Excel 8.0')...Sheet1$
(date,calltime) select date,calltime from db.dbo.calls where customerID=10