October 15, 2012 at 2:42 am
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).
October 15, 2012 at 2:57 am
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?
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
October 15, 2012 at 3:08 am
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
October 15, 2012 at 3:23 am
Okay so something like the below might work for you:
IF (select count(*) from [e009]) > 0
begin
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'
end
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
October 15, 2012 at 3:27 am
Abu Dina (10/15/2012)
Okay so something like the below might work for you:
IF (select count(*) from [e009]) > 0
begin
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'
end
Thanks, this will be hard to test as I have no control over whether the data comes in or not as it is sent into our database by an external source and this type of data only comes in when there is something wrong (hence the name warning) but I'll try it out.
October 15, 2012 at 3:44 am
I tested it with other data and it works great, thanks.
October 15, 2012 at 3:46 am
Excellent stuff! Nice one. 😀
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
October 15, 2012 at 4:20 pm
So now if the job just stops running altogether, how will you know? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 16, 2012 at 2:05 am
Jeff Moden (10/15/2012)
So now if the job just stops running altogether, how will you know? 😉
I guess I won't! We do have a piece of software that also gives us this information so we would know from there also...
October 16, 2012 at 2:25 am
Hmmm....Jeff makes a good point.
You could add a notification to the job so you get an email on failure but what happens if the job hangs or doesn't start?
Another way is to revert back to your original setup then create an email rule to divert the empty email reports into a junk folder.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
November 28, 2013 at 4:15 am
I had been struggling with this ...Exactly what i was looking for...Thanks a ton Adu Dina .. 🙂
November 29, 2013 at 8:41 am
sqlrd22 (10/15/2012)
Abu Dina (10/15/2012)
Okay so something like the below might work for you:
IF (select count(*) from [e009]) > 0
begin
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'
end
Thanks, this will be hard to test as I have no control over whether the data comes in or not as it is sent into our database by an external source and this type of data only comes in when there is something wrong (hence the name warning) but I'll try it out.
There's a small tweek that I'd make to that. As written above, both the COUNT(*) and the @query each make a table scan of the e009 table (or view). While I do understand that there's usually nothing in the table/view and that it runs only twice a day and there's not much in the table/view when it actually contains something, there's no need for any extra reads/cpu time on the system if you can avoid them. It also helps folks that look for code for their particular different problem.
With that thought in mind, if you change the IF in Abu Dina's good code to just check for the presence of at least 1 row, you accomplish the same thing but with fewer reads/cpu time.
IF EXISTS(SELECT TOP 1 1 FROM [e009])
begin
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'
end
--Jeff Moden
Change is inevitable... Change for the better is not.
October 27, 2014 at 2:18 pm
That solution works fine.
But I have the following error message:
Message
Executed as user: MYDOMAIN\MYUSER.
NbError: 631
[SQLSTATE 01000] (Message 0) File attachment or query results size exceeds allowable value of 1000000 bytes.
[SQLSTATE 42000] (Error 22050). The step failed.
How would you look if the query result is exceeding the maximum?
October 28, 2014 at 2:20 pm
you'll need to up the attachment size in your database main configuration.
exec msdb.dbo.sysmail_configure_sp 'MaxFileSize', '10000000'
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 29, 2014 at 12:19 pm
That's exactly what I don't want to do.
Is there a way to leave it like that, and check if the query result is too big and take only the top X rows?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply