0

I have a stored procedure that I am going to run every weekend, it produces a result set that I need to export into an Excel file.

For the above problem I want to automate this process, so I am going to create a SQL Job and I am going to run this stored procedure every weekend so that that generated Excel file is sent to my reporter.

For this I need steps to export the result set data to an Excel file.

And also is it possible to send that Excel file to the specific mail while running the job itself?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
RaviKumar
  • 13
  • 3

1 Answers1

0

So, you might try your luck on https://dba.stackexchange.com/, but in my experience a SQL Agent job running a stored procedure could be coaxed to return CSV or XML - and those could end up in Excel, but there are missing links. I think the missing links would involve programming and potentially 3rd party tools to avoid using Excel's COM API.

I'd strongly recommend your pursuing SQL Server Reporting Services. It is included free with your edition of SQL and includes the ability to

  1. run reports on a schedule (subscriptions),
  2. format the results as an Excel file
  3. distribute the results via email

You'd take your query and use it as the data source for a "report" and use the report wizard to create a very simple table with the results. Avoid page headers (or footers) that span columns - this will keep the excel output cleaner.

References

  1. Stack Overflow: reporting-services-export-to-excel-with-multiple-worksheets
  2. Technet: Reporting Services
Community
  • 1
  • 1
Stan
  • 985
  • 1
  • 7
  • 12