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.
You have the 3 ways posted in the previous answer, or you can do it in plain sql, using OPENROWSET or OPENDATASOURCE.
Some examples:
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.
-
+1 i refered [geekswithblogs.net/nagendraprasad/archive/2009/03/26/… link but im getting error of
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.– M.S.Nayak Jan 6 '12 at 10:33 -
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.
-
i refered [geekswithblogs.net/nagendraprasad/archive/2009/03/26/… link but im getting error of
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.– M.S.Nayak Jan 6 '12 at 10:32
