Only send DBMail when query results are present

  • 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).

  • 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

  • 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

  • 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

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

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

  • 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

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

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

  • 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

  • I had been struggling with this ...Exactly what i was looking for...Thanks a ton Adu Dina .. 🙂

  • 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


    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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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?

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

  • 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