Only send DBMail when query results are present

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Only send DBMail when query results are...

Only send DBMail when query results are present

Rate Topic

Display Mode

Topic Options

Author

Message

sqlrd22

Posted Monday, October 15, 2012 2:42 AM

Valued Member

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:07 AM
Points: 59, Visits: 663

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

Abu Dina

Posted Monday, October 15, 2012 2:57 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:30 AM
Points: 484, Visits: 2,122

sqlrd22 (10/15/2012)

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?

-----------------------------------
http://www.SQL4n00bs.com

Post #1372603

sqlrd22

Posted Monday, October 15, 2012 3:08 AM

Valued Member

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:07 AM
Points: 59, Visits: 663

Abu Dina (10/15/2012)

sqlrd22 (10/15/2012)

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

Abu Dina

Posted Monday, October 15, 2012 3:23 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:30 AM
Points: 484, Visits: 2,122

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

-----------------------------------
http://www.SQL4n00bs.com

Post #1372619

sqlrd22

Posted Monday, October 15, 2012 3:27 AM

Valued Member

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:07 AM
Points: 59, Visits: 663

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

sqlrd22

Posted Monday, October 15, 2012 3:44 AM

Valued Member

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:07 AM
Points: 59, Visits: 663

I tested it with other data and it works great, thanks.

Post #1372630

Abu Dina

Posted Monday, October 15, 2012 3:46 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:30 AM
Points: 484, Visits: 2,122

Excellent stuff! Nice one. BigGrin

-----------------------------------
http://www.SQL4n00bs.com

Post #1372632

Jeff Moden

Posted Monday, October 15, 2012 4:20 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,893, Visits: 26,771

So now if the job just stops running altogether, how will you know? Wink

--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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1372985

sqlrd22

Posted Tuesday, October 16, 2012 2:05 AM

Valued Member

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:07 AM
Points: 59, Visits: 663

Jeff Moden (10/15/2012)

So now if the job just stops running altogether, how will you know? Wink

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

Abu Dina

Posted Tuesday, October 16, 2012 2:25 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:30 AM
Points: 484, Visits: 2,122

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.

-----------------------------------
http://www.SQL4n00bs.com

Post #1373073

« Prev Topic | Next Topic »

Permissions