March 16, 2017 at 1:05 pm
I am trying automate a monthly report that would export data from a table into an Excel spreadsheet. I don't want it to overwrite the existing spreadsheet so I would need for each spreadsheet to be named the current date of when the file was generated. Not sure if that makes sense what's the easiest solution for this T-SQL in an Agent job if so can I get an example of a query or an SSIS package? If neither I'm open to any other solutions.
March 16, 2017 at 1:52 pm
exec msdb.dbo.sp_send_dbmail
@profile_name = 'DBAEmailProfile',
@recipients = 'DBA@mail.com',
@subject = 'MonthlyReportData',
@body_format='HTML',
@query_result_header=1,
@execute_query_database='productsDB',
@query =
'SELECT productid, price FROM dbo.product'
@query_result_separator=' ',
@attach_query_result_as_file = 0, --set to 1 for true, this script will keep results in email body
@query_attachment_filename = 'Data.csv' -- will only attach when prior parameter set TRUE
or get creative with OPENROWSET if you want it on disk:
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\Test.xls;','SELECT productid, price FROM dbo.product')
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
March 17, 2017 at 10:33 am
Thanks for the reply Henrico,
I'm new to SQL so forgive me for the questions but I need the CSV file to be stored in a specific location on a share drive. Your query I'm assuming is attaching the file to an email sent to either myself or the user? Also is Openrowset used only when link servers are involved?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply