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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply