I want to execute some stored procedure and I want to export that result to Excel. How can Do that ? I'm new to SQL. any help or tutorials about this will appreciated.
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\DataFolder\Documents\TestExcel.xls;Extended Properties=EXCEL 5.0')...[Sheet1$] ; SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\TEST.xls;HDR=YES', 'select * from [Sheet1$]');
Both SSIS and SSRS import/export methods can't be done too easily, though they have the advantage of being able to automate their execution, and the copy from SSMS output is only a manual solution. The OPENROWSET/OPENDATASOURCE functions can be called more easily in a procedure or ad-hoc query.
Couple of ways that I know of
- Run procedure in ssms, then copy results into excel (make sure to turn on the option to copy column headers in tools,options,query results)
- Create a SQL server integration services (SSIS) package to run the procedure and write the results to a CDC or excel file.
- If you have SQL server reporting services (SSRS) you can create a report to run the procedure and display the results, then from the report website you can export the report to excel/csv. This can also be scheduled through SSRS subscriptions.