Ideal approach for automated export of SQL Server stored procedure results into Excel spreadsheet?

Other SQL Server Topics
1

Hi,

I realize this is potentially outside the scope of this forum, but I'm interested in what might be considered the ideal approach for running a stored procedure, dumping the results into an Excel spreadsheet, and making this process automated/scheduled.

A few ideas I plan to look into:

  • VB.NET application running as a scheduled task
  • SSIS package

Are there any other SQL tools (or non-SQL tools for that matter) that are generally recommended in these situations?

I'd be particularly interested in any SQL tool that allows for some manipulation of the format of the spreadsheet, for example (1) checking column names already in an Excel spreadsheet to know where to dump the column data into, (2) size, color and style formatting of text and cells, (3) where the data gets dumped (say, starting the first column in C4 as opposed to A1), and (4) being able to run multiple stored procedures, one for each tab of the spreadsheet.

Thank you for any assistance on this!

2

what you have listed will work, you can also add x programming language that can talk to sql server. Powershell is another cool and powerful tool

3

Thanks for the reply, yosiasz!

I'll have to take a closer look at PowerShell.

4

SSIS is the tool MS created for this kind of task.

5

This will not fit that requirement, but I mention it just in case of any use.

We have SProcs that are executed directly from Excel - i.e. the user "refreshes" the data "on-demand" - they open the XLS, and if they want fresh data they press a button to refresh the Sheet. It uses the Query tool that is part of / associated with Excel (sorry, for the life of me I can't remember the name at the moment ...). If the Sproc needs any parameters we use a separate sheet with two columns of Question Prompt / Data to "feed" the query tool with suitable parameters.