Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.


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.

share|improve this question
add comment

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
+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
add comment

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.
share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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