October 25, 2016 at 5:07 pm
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........
October 25, 2016 at 6:00 pm
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
October 25, 2016 at 6:18 pm
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
October 26, 2016 at 4:43 am
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?
October 26, 2016 at 11:49 am
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.
October 26, 2016 at 4:04 pm
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
October 26, 2016 at 5:43 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply