Geek Answers Handbook

How to export data to Excel on SQL Server using SQL jobs

I need to export data from a specific table in my database to Excel files (.xls / .xlsx), which will be located in a shared folder on my network. Now the situation is this:

  • I need to use SQL SERVER Agent Jobs. 2. I need to create a new excel file every 2 minutes that will contain updated data.

    I am using SQL Server 2008, which does not include BI development studio. I do not know how to solve this situation. Firstly, I'm not sure how to export data using workstations, because all the possible ways that I tried had some problems with OLEDB connection. "Sp_makewebtask" is also not available in SQL 2008. And I'm also confused about how to dynamically generate file names.

    Any link or solution would be helpful.

+4
source share
2 answers

I have found a better way. I created a package of SSIS (SQL Server Integration Services) to automate the entire Export to Excel task. Then I deployed this package using SQL Agent Agent Jobs. This is a tidier and cleaner solution, as I found.

+2
source

Follow these steps:

1) Make a stored procedure that creates a temporary table and inserts records into it.

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

3) Create an SQL job that sequentially performs steps 1 and 2.

+4

More articles:


All Articles