Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Only send DBMail when query results are present Expand / Collapse
Author
Message
Posted Monday, October 15, 2012 2:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 28, 2013 5:13 AM
Points: 61, Visits: 721
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).
Post #1372597
Posted Monday, October 15, 2012 2:57 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 19, 2014 3:37 AM
Points: 708, Visits: 3,288
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

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
Post #1372603
Posted Monday, October 15, 2012 3:08 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 28, 2013 5:13 AM
Points: 61, Visits: 721
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

Post #1372610
Posted Monday, October 15, 2012 3:23 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 19, 2014 3:37 AM
Points: 708, Visits: 3,288
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

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
Post #1372619
Posted Monday, October 15, 2012 3:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 28, 2013 5:13 AM
Points: 61, Visits: 721
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.
Post #1372623
Posted Monday, October 15, 2012 3:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 28, 2013 5:13 AM
Points: 61, Visits: 721
I tested it with other data and it works great, thanks.
Post #1372630
Posted Monday, October 15, 2012 3:46 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 19, 2014 3:37 AM
Points: 708, Visits: 3,288
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

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
Post #1372632
Posted Monday, October 15, 2012 4:20 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 35,262, Visits: 31,745
So now if the job just stops running altogether, how will you know?

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1372985
Posted Tuesday, October 16, 2012 2:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 28, 2013 5:13 AM
Points: 61, Visits: 721
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...
Post #1373071
Posted Tuesday, October 16, 2012 2:25 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 19, 2014 3:37 AM
Points: 708, Visits: 3,288
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

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
Post #1373073
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse