Export the query to excel sheet automatically

Question

Hello Team,

I am new to sql ....We use sql query in sql 2005 in SQL server and then we are exporting the output to excel.Could you please guide how to make the process automated for creating excel sheet on daily bases on the same query . Since i need to do the task manually on modifying the data in the query..:(

Please provide me more detail information or step by step ...since i am not much good in sql...

---

Exchange Queries

Answer 1

Hi,

You may take a look on SQL Server Reporting Services where you can automate excel exports with subscriptions. By default you can subscribe to a report by email or file system delivery.

Please let me know if you have more question.

Thanks,

Janos

---

There are 10 type of people. Those who understand binary and those who do not.
My Blog

Answer 2

Is there any other way to gets these reports automated?

Reporting services Configuration manager gives the error " No report servers were found in the specified machine"

I checked the Services, i donot find any SQL server reporting services listed/installed.

---

Exchange Queries

Answer 3

You can use Database Mail. you can attach the query result, however it requires some extra formatting before can work with it in excel. You can schedule a t-sql task with SQL agent. Code should look like this:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'your mailprofile',
    @recipients = 'youremail',
    @query = 'SELECT TOP 10 * FROM someTable' ,
    @subject = 'test db mail',
    @attach_query_result_as_file = 1 ;

Janos

---

There are 10 type of people. Those who understand binary and those who do not.
My Blog

Answer 4

Hello,

You have to create an Integration Services Project "SSIS" . Create a data source pointing to your SQL Server using your SELECT statement and create a Excel Data source to use as Destination. When you have the SSIS package ready you can schedule it to run it daily using a SQL Job

http://www.youtube.com/watch?v=CJI6pPueyzM

---

Javier Villegas | @javier_vill | http://sql-javier-villegas.blogspot.com/

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 5

You could use powershell script to pwerfom this task

http://sethusrinivasan.wordpress.com/2012/04/06/export-the-query-to-excel-sheet-automatically/

Thanks

Sethu Srinivasan [MSFT]

SQL Server

http://blogs.msdn.com/sqlagent

Answer 6

Thanks for your timely help. I was able to export the Query output to the Excel sheet. But i do it manually every time.

Is there a way to automate it, i mean schedule it for every day?

---

Exchange Queries

Answer 7

You can create a scheduled SQL Agent  job to automate this task every day.

http://blogs.msdn.com/b/sqlagent/archive/2010/10/14/creating-a-regularly-scheduled-job.aspx

Thanks

Sethu  Srinivasan [MSFT]

SQL Server

http://blogs.msdn.com/sqlagent