July 24, 2014 at 8:46 pm
Hi all.
I found this code online in order to email oujt a View I have created in SQL 2008 as a CSV file:
EXEC msdb.dbo.sp_send_dbmail
@recipients='MyEmail@MyDomain.com',
@subject= 'Daily Reports',
@body='Please find attached',
@query ='SELECT * FROM MYView',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'MyNName.CSV',
@query_result_separator = ''
GO
So far i'm happy with the results, although I would like to do away with row 2 that is populated with the dashes, spent most of today trying to find a fix for that and the UNION option is not workign out so far, but first things first. Before I tackle that i'd like to know if ther is a way I can have more control of the filename that is emailed.
I would like the todays Date in YYYYMMDD format as the strat of the filename but trying to declare a variable for the Date to use in that filename line is proving difficult. I tried using the code below:
@query_attachment_filename = SELECT FORMAT(DATE(), 'YYYYMMDD') & 'MyNName.CSV'
but its not having any of it.
Does anyone have any suggestions to fix this issue? Once I've nailed that down I plan on creating a JOB that will run that code and email out the report to my email address.
Thanks,
Mitch.
July 24, 2014 at 10:06 pm
Mitch2007 (7/24/2014)
Hi all.I found this code online in order to email oujt a View I have created in SQL 2008 as a CSV file:
EXEC msdb.dbo.sp_send_dbmail
@recipients='MyEmail@MyDomain.com',
@subject= 'Daily Reports',
@body='Please find attached',
@query ='SELECT * FROM MYView',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'MyNName.CSV',
@query_result_separator = ''
GO
So far i'm happy with the results, although I would like to do away with row 2 that is populated with the dashes, spent most of today trying to find a fix for that and the UNION option is not workign out so far, but first things first. Before I tackle that i'd like to know if ther is a way I can have more control of the filename that is emailed.
I would like the todays Date in YYYYMMDD format as the strat of the filename but trying to declare a variable for the Date to use in that filename line is proving difficult. I tried using the code below:
@query_attachment_filename = SELECT FORMAT(DATE(), 'YYYYMMDD') & 'MyNName.CSV'
but its not having any of it.
Does anyone have any suggestions to fix this issue? Once I've nailed that down I plan on creating a JOB that will run that code and email out the report to my email address.
Thanks,
Mitch.
Try
DECLARE @FileName NVARCHAR (200);
SELECT @FileName = CONVERT (VARCHAR, GETDATE(), 112) + 'MyNName.CSV'
EXEC msdb.dbo.sp_send_dbmail
@recipients='MyEmail@MyDomain.com',
@subject= 'Daily Reports',
@body='Please find attached',
@query ='SELECT * FROM MYView',
@attach_query_result_as_file = 1,
@query_attachment_filename = @FileName ,
@query_result_separator = ''
July 25, 2014 at 8:52 am
Thank you Sir.
I just gave it a run through and it works perfectly!
Cheers again!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply