April 25, 2011 at 7:16 am
Is there a way to schedule a sql server agent job using store procedure that will output the resultset to an excel file and email the file?
Thanks.
April 25, 2011 at 8:10 am
Does your stored procedure produce a file?
You could schedule a SQLCMD job that pipes output to a file, or a BCP job that pulls out data in a query.
The job part is easy, emailing attachments or results is easy. The Excel file is the issue. Does your stored procedure produce one, or a CSV? Or have you looked at SSIS to produce an Excel file?
April 25, 2011 at 9:24 am
Steve,
Thank you for your help. I will output the results to csv file and try and let you know the results.
Thanks.
April 25, 2011 at 9:42 pm
I agree with what Steve says here but of course I'd do it with PowerShell[/url]. If you really do need it to be exported as Excel file I think that wouldn't be too hard to add to a script like in that post.
@SQLvariantI have a PowerShell script[/url] for you.
April 27, 2011 at 9:52 am
It's note an Excel binary excel file, but it does end up in a tab delimited format that cleanly imports into excel on a double-click if you name it .xls (some CONVERT on dates is required for maximum readability):
http://weblogs.sqlteam.com/mladenp/archive/2006/07/25/10771.aspx
Some minor tweaks required for 2005+ - bcp needs -T for trusted connections, and @dbname should be removed.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply