Abu Dina (10/15/2012)
sqlrd22 (10/15/2012)
I have a job in SQL Server Agent that sends the results of a query to my email twice daily, however most of the time this query will not have any results. I, therefore, wish for the results of the query to only be sent to me when there are actual results from the query.At the moment the query results just come with the headings and a message saying (0 rows affected).
Are you using T-SQL to generate the report and send mail?
From what you say it sounds like your setup is like the below:
1) SQL Agent Job runs twice a day.
2) There is a step in the job which generates the report and send mail.
If that's how it is then why not use an IF statement to check for size of record set of your report before you use the sp_send_dbmail?
Sorry, yes, my job runs twice daily and I use the following script in the job
exec msdb.dbo.sp_send_dbmail
@profile_name = 'Default',
@recipients = 'me@example.com',
@subject = 'warning',
@query = 'select * from [e009]',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'warning.csv'
go
The code for the view I reference in the query is -
select * from [gate].[dbo].[data] where clob like 'AAA|e0221002|_|'+CONVERT(varchar (8), GETDATE(),112)+'%ECP%';
go