April 7, 2009 at 7:07 am
I have the following email being sent with query results using sp_send_dbmail. However I only want to send this email if the field ThresholdExceeded = True.
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'recipient@domain.com',
@subject=N'Check Phone Call Date Threshold',
@query = 'SELECT CASE WHEN DATEDIFF(D, MAX(TRANSDATE), GETDATE()) > 4 THEN ''True'' ELSE ''False'' END AS ''ThresholdExceeded'',''It has been '' + CONVERT(VARCHAR(10), DATEDIFF(D, MAX(TRANSDATE), GETDATE()) ) + '' days since the last phone transaction was recorded.'' AS Message from DATABASE.dbo.TABLEwhere transtype = ''6''' ;
If results are: ThresholdExceeded = False, Message = It has been 3 days since the last phone transaction was recorded.
Then no email is sent.
If results are: ThresholdExceeded = True, Message = It has been 5 days since the last phone transaction was recorded.
Then an email is sent.
The email should contain the Message field in the body of the email.
April 7, 2009 at 7:44 am
This was helpful.
set transaction isolation level read uncommitted
set nocount on
SELECT tablename, transfered, DATEDIFF(day, transfered, GETDATE()) AS diff
INTO ##tempResults
FROM [product].dbo.table_updated
WHERE DATEDIFF(day, transfered, GETDATE()) >2
ORDER BY transfered
IF @@rowcount > 0
BEGIN
EXEC msdb..sp_send_dbmail
@recipients = 'user@domain.com',
@subject = 'SQL Product Database',
@query = 'select * from ##tempResults',
@body = 'This is an auto-generated email from SQL and contains details of Table updates.'
drop table ##tempResults
END
April 7, 2009 at 8:53 am
Receiving following error
Msg 245, Level 16, State 1, Line 7
Conversion failed when converting the varchar value 'FALSE' to data type int.
April 7, 2009 at 9:22 am
Works with following:
set transaction isolation level read uncommitted
set nocount on
SELECT CASE WHEN DATEDIFF(D, MAX(TRANSDATE), GETDATE()) > 4 THEN '1' ELSE '0' END AS ThresholdExceeded,'It has been '+ CONVERT(VARCHAR(10), DATEDIFF(D, MAX(TRANSDATE), GETDATE()) ) + ' days since the last phone transaction was recorded.' AS Message
INTO ##tempResults
FROM [DATABASE].dbo.TABLE
WHERE transtype = '6'
BEGIN
SELECT * FROM ##tempResults WHERE ThresholdExceeded = '0'
EXEC msdb..sp_send_dbmail
@recipients = 'RECIPIENT@DOMAIN.com',
@subject = 'Check Phone Call Date Threshold',
@query_result_header = '0',
@query = 'select Message from ##tempResults'
drop table ##tempResults
END
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy