1


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.

2

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
  • I think that ACE.OLEDB is for MS Access, not sure if it's the same as for MS Excel. The old provider was JET, but not sure about last versions of Office. Anyway, following your error you could probably find help on the following pages: link1, link2, link3. – Marian Jan 6 '12 at 10:42
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.