SQL Job - almost good

  • Okay - I need to accomplish the following:

    1. Read SQL Table - if query returns results then send email with query results

    2. After sending email - update the table by changing a value (so we don't re-send this record)

    I have a job with two steps.

    Step one sends the email -

    EXEC [msdb]..sp_send_dbmail

    @profile_name='Default',

    @recipients='XXXXX@hotmail.com',

    @subject='DB Mail',

    @body='Bryan this is the sample email for Worth MOP to SOP Changes',

    @execute_query_database = 'WORTH',

    @query = 'exec X_MOPSOPLINK',

    @attach_query_result_as_file=1,

    @query_attachment_filename = 'SOP to MOP Changes.txt'

    This works great except that it sends an email regardless if the query has results - is there an easy way to modify so that it only sends email if there is a query result?

    @query fires this stored procedure:

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE PROCEDURE

    [dbo].[X_MOPSOPLINK]

    AS

    BEGIN

    SELECT CUSTID, CUSTNAME, MANUFACTUREORDER_I, SOPNUMBE, ITEMNMBR, ITEMDESC,

    Convert(date, NEW_ENDDATE) as NEWDATE,

    Convert(date, OLD_ENDDATE) as OLDDATE

    -------, OLD_SOPREQSHIPDATE, NEW_SOPREQSHIPDATE

    FROM X_SOPMOP_Link_2

    where NOTICE_SENT=0

    END

    GO

    I'm wondering if I should try to modify the stored procedure to include the send email procedure? If so, what is best method? Any suggestions would be appreciated........

  • Bron Tamulis (10/25/2016)


    Okay - I need to accomplish the following:

    1. Read SQL Table - if query returns results then send email with query results

    2. After sending email - update the table by changing a value (so we don't re-send this record)

    I have a job with two steps.

    Step one sends the email -

    This works great except that it sends an email regardless if the query has results - is there an easy way to modify so that it only sends email if there is a query result?

    I'm wondering if I should try to modify the stored procedure to include the send email procedure? If so, what is best method? Any suggestions would be appreciated........

    One approach is to do as you mention and have the email call in the stored procedure. It looks like from your query, you need to work with all rows where no notice has been sent. You can just get the count for the table where NOTICE_SENT=0, if that count is greater than 0, then do the EXEC [msdb]..sp_send_dbmail ......

    You could also send an email to just yourself or whoever if you need to know that nothing was sent due to no data. If count > 0 then send mail with query else send mail to DBA. But that could be obnoxious depending on the business, data, how often and when this is executed. Just a thought.

    Sue

  • like Sue_H, i would have the procedure send the email. if you are reusing the procedure elsewhere, add an optiona parameter with a default, like @sendEmailNotification int=0

    it looks like a simple IF EXISTS would work; something like this:

    IF EXISTS(SELECT 1 FROM X_SOPMOP_Link_2 where NOTICE_SENT=0)

    AND @sendEmailNotification >0

    BEGIN

    --the exact same send email code goes here.

    END

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thank you both - Sue and Lowell..........

    Quick follow up question - my step 2 is to update the table by setting the NOTICE_SENT column = '1' - is a simple update statement best or should I create a separate stored procedure that I fire on step 2 - updating the column?

    Any thoughts or best practices?

  • If I were doing this, I would fire the email and update the table in the same procedure. I don't see a reason to separate them, unless one activity can be done without the other, but it doesn't sound like that's the case here.

  • Ed Wagner (10/26/2016)


    If I were doing this, I would fire the email and update the table in the same procedure. I don't see a reason to separate them, unless one activity can be done without the other, but it doesn't sound like that's the case here.

    There's a very good reason. Copy your DB or even just the proc from Prod to Dev to refresh the Dev environment and watch someone inadvertently send an email to the customers with totally whacked data or some choice remarks that developers are sometimes known to put into their tests.

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

  • But thats easily fixed with smart code

    If exists... and @@SERVERNAME='Production' And db_name='ProdDB'

    So i would argue it all belongs in a single procedure.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply