1

I wish to schedule the query result export to the excel sheet on SQL server. Is it possible to do that?

The query should create a new CSV or excel file and dump all the data from the query in to the sheet.

I tried 

INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\contact.xls;',
'SELECT * FROM [Sheet1$]')
select * from temp1

But it requires the file to be present already.Can some one help me out ?

user2569524
  • 1,651
  • 7
  • 32
  • 57

2 Answers2

0

If you dont 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'

Possible Duplicate of T-SQL: Export to new Excel file

Community
  • 1
  • 1
Dgan
  • 10,077
  • 1
  • 29
  • 51
  • .i Get the error proc sp_makewebtask not found with the this moethod and with the previous approach I get " Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"." – user2569524 Mar 18 '14 at 19:05
0

Use Export/Import Wizard. Source: SQL server Destination: Excel

You may need to indicate the name of Excel sheet and its location in folder structure.

I am showing only one step of the Wizard here:

enter image description here

user2063329
  • 443
  • 2
  • 5
  • 15