How to Export data to Excel in SQL Server using SQL Jobs

sql excel sql-server-2008 export-to-excel sql-agent-job
Atanu Roy picture Atanu Roy · Sep 27, 2013 · Viewed 23.8k times · Source

I need to export the data from a particular table in my database to Excel files (.xls/.xlsx) that will be located into a shared folder into my network. Now the situation is like this -

  1. I need to use SQL SERVER Agent Jobs. 2.I need to generate a new excel file in every 2 minutes that will contain the refreshed data.

    I am using sql server 2008 that doesn't include BI development studio. I'm clueless how to solve this situation. First, I'm not sure how to export the data using jobs because every possible ways I tried had some issues with the OLEDB connection. The 'sp_makewebtask' is also not available in SQL 2008. And I'm also confused how to dynamically generate the names of the files.

    Any reference or solution will be helpful.

Answer

Dev picture Dev · Sep 27, 2013

Follow the steps given below :

1) Make a stored procedure that creates a temporary table and insert records to it.

2) Make a stored procedure that read records from that temporary table and writes to file. You can use this link : clickhere

3) Create an SQL-job that execute step 1 and step 2 sequentially.

Related questions

Saving results with headers in Sql Server Management Studio

I am using SQL Server Management Studio. I wish to save the results of a query to an excel file. I choose "save as" and then save to CSV file which I can open in excel. All good except I …

Read More
New Line Issue when copying data from SQL Server 2012 to Excel

I recently upgraded to SQL2012 and am using Management Studio. One of my columns in the database has a CHAR(13) + CHAR(10) stored in it. When I was using SQL Server 2008, this would copy and paste completely fine into Excel. Now, …

Read More
Table in Excel from SQL Server stored procedure with parameter field in workbook

I need to create a dynamic report using Excel 2010 and SQL Server. Going off of previous ones created by others I figured out how to create the data connection and use the stored procedure with a specific parameter in the …

Read More