October 30, 2017 at 1:35 pm
Hi,
I query that will be part of a store procedure. the store procedure will be loading a table, after a load I want to check two fields first before I sent email.
INSERT INTO table (id, processName, isProcessed, isSuccess)
Values(1,FirstProcess, 1,1),(2,SecondProcess,1,1,0),(2,ThirdProcess,1,0,0)
I want to check first if isProcessed is 1 and isSuccess 0, then send email with the name of the process, second check if isProcessed is 1 but isSuccess is 0 then send second email with the name of the processor still, emails will be worded differently.
Please help
October 30, 2017 at 2:01 pm
hoseam - Monday, October 30, 2017 1:35 PMHi,I query that will be part of a store procedure. the store procedure will be loading a table, after a load I want to check two fields first before I sent email.
INSERT INTO table (id, processName, isProcessed, isSuccess)
Values(1,FirstProcess, 1,1),(2,SecondProcess,1,1,0),(2,ThirdProcess,1,0,0)I want to check first if isProcessed is 1 and isSuccess 0, then send email with the name of the process, second check if isProcessed is 1 but isSuccess is 0 then send second email with the name of the processor still, emails will be worded differently.
Please help
sp_send_dbmail will do what you need.
Or do you need help with the conditional logic?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 30, 2017 at 11:08 pm
Phil Parkin - Monday, October 30, 2017 2:01 PMhoseam - Monday, October 30, 2017 1:35 PMHi,I query that will be part of a store procedure. the store procedure will be loading a table, after a load I want to check two fields first before I sent email.
INSERT INTO table (id, processName, isProcessed, isSuccess)
Values(1,FirstProcess, 1,1),(2,SecondProcess,1,1,0),(2,ThirdProcess,1,0,0)I want to check first if isProcessed is 1 and isSuccess 0, then send email with the name of the process, second check if isProcessed is 1 but isSuccess is 0 then send second email with the name of the processor still, emails will be worded differently.
Please help
sp_send_dbmail will do what you need.
Or do you need help with the conditional logic?
Yes please, a conditional logic.
October 30, 2017 at 11:39 pm
Phil Parkin - Monday, October 30, 2017 2:01 PMhoseam - Monday, October 30, 2017 1:35 PMHi,I query that will be part of a store procedure. the store procedure will be loading a table, after a load I want to check two fields first before I sent email.
INSERT INTO table (id, processName, isProcessed, isSuccess)
Values(1,FirstProcess, 1,1),(2,SecondProcess,1,1,0),(2,ThirdProcess,1,0,0)I want to check first if isProcessed is 1 and isSuccess 0, then send email with the name of the process, second check if isProcessed is 1 but isSuccess is 0 then send second email with the name of the processor still, emails will be worded differently.
Please help
sp_send_dbmail will do what you need.
Or do you need help with the conditional logic?
I tried this but it throws errors when I get more than one isProcessed
IF (SELECT IsProcessed FROM [database].[dbo].[DataLoadCheck] WHERE CAST(CheckDate AS DATE) = CAST(GETDATE() AS DATE)) > 0
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients= @EmailAddress,
@copy_recipients= @EmailAddressCC,
@subject=@MailSubject,
@body=@BodyMessage,
@body_format='HTML',
@from_address='Sender Name <NoReply@myemail.com>',
@reply_to='NoReply@myemail.com'
END
October 31, 2017 at 4:43 am
hoseam - Monday, October 30, 2017 11:39 PMPhil Parkin - Monday, October 30, 2017 2:01 PMhoseam - Monday, October 30, 2017 1:35 PMHi,I query that will be part of a store procedure. the store procedure will be loading a table, after a load I want to check two fields first before I sent email.
INSERT INTO table (id, processName, isProcessed, isSuccess)
Values(1,FirstProcess, 1,1),(2,SecondProcess,1,1,0),(2,ThirdProcess,1,0,0)I want to check first if isProcessed is 1 and isSuccess 0, then send email with the name of the process, second check if isProcessed is 1 but isSuccess is 0 then send second email with the name of the processor still, emails will be worded differently.
Please help
sp_send_dbmail will do what you need.
Or do you need help with the conditional logic?I tried this but it throws errors when I get more than one isProcessed
IF (SELECT IsProcessed FROM [database].[dbo].[DataLoadCheck] WHERE CAST(CheckDate AS DATE) = CAST(GETDATE() AS DATE)) > 0
BEGINEXEC msdb.dbo.sp_send_dbmail
@recipients= @EmailAddress,
@copy_recipients= @EmailAddressCC,
@subject=@MailSubject,
@body=@BodyMessage,
@body_format='HTML',
@from_address='Sender Name <NoReply@myemail.com>',
@reply_to='NoReply@myemail.com'END
I'm not quite sure I follow your logic, but I think if you change the IF statement as follows:IF EXISTS (SELECT * FROM [database].[dbo].[DataLoadCheck] WHERE CAST(CheckDate AS DATE) = CAST(GETDATE() AS DATE) AND IsProcessed > 0)
Thomas Rushton
blog: https://thelonedba.wordpress.com
October 31, 2017 at 11:28 am
ThomasRushton - Tuesday, October 31, 2017 4:43 AMhoseam - Monday, October 30, 2017 11:39 PMPhil Parkin - Monday, October 30, 2017 2:01 PMhoseam - Monday, October 30, 2017 1:35 PMHi,I query that will be part of a store procedure. the store procedure will be loading a table, after a load I want to check two fields first before I sent email.
INSERT INTO table (id, processName, isProcessed, isSuccess)
Values(1,FirstProcess, 1,1),(2,SecondProcess,1,1,0),(2,ThirdProcess,1,0,0)I want to check first if isProcessed is 1 and isSuccess 0, then send email with the name of the process, second check if isProcessed is 1 but isSuccess is 0 then send second email with the name of the processor still, emails will be worded differently.
Please help
sp_send_dbmail will do what you need.
Or do you need help with the conditional logic?I tried this but it throws errors when I get more than one isProcessed
IF (SELECT IsProcessed FROM [database].[dbo].[DataLoadCheck] WHERE CAST(CheckDate AS DATE) = CAST(GETDATE() AS DATE)) > 0
BEGINEXEC msdb.dbo.sp_send_dbmail
@recipients= @EmailAddress,
@copy_recipients= @EmailAddressCC,
@subject=@MailSubject,
@body=@BodyMessage,
@body_format='HTML',
@from_address='Sender Name <NoReply@myemail.com>',
@reply_to='NoReply@myemail.com'END
I'm not quite sure I follow your logic, but I think if you change the IF statement as follows:
IF EXISTS (SELECT * FROM [database].[dbo].[DataLoadCheck] WHERE CAST(CheckDate AS DATE) = CAST(GETDATE() AS DATE) AND IsProcessed > 0)
Understand that this change means one e-mail, regardless of the number of conditions that occur, and it doesn't identify the "offending rows".
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 31, 2017 at 12:08 pm
sgmunson - Tuesday, October 31, 2017 11:28 AMThomasRushton - Tuesday, October 31, 2017 4:43 AMhoseam - Monday, October 30, 2017 11:39 PMPhil Parkin - Monday, October 30, 2017 2:01 PMhoseam - Monday, October 30, 2017 1:35 PMHi,I query that will be part of a store procedure. the store procedure will be loading a table, after a load I want to check two fields first before I sent email.
INSERT INTO table (id, processName, isProcessed, isSuccess)
Values(1,FirstProcess, 1,1),(2,SecondProcess,1,1,0),(2,ThirdProcess,1,0,0)I want to check first if isProcessed is 1 and isSuccess 0, then send email with the name of the process, second check if isProcessed is 1 but isSuccess is 0 then send second email with the name of the processor still, emails will be worded differently.
Please help
sp_send_dbmail will do what you need.
Or do you need help with the conditional logic?I tried this but it throws errors when I get more than one isProcessed
IF (SELECT IsProcessed FROM [database].[dbo].[DataLoadCheck] WHERE CAST(CheckDate AS DATE) = CAST(GETDATE() AS DATE)) > 0
BEGINEXEC msdb.dbo.sp_send_dbmail
@recipients= @EmailAddress,
@copy_recipients= @EmailAddressCC,
@subject=@MailSubject,
@body=@BodyMessage,
@body_format='HTML',
@from_address='Sender Name <NoReply@myemail.com>',
@reply_to='NoReply@myemail.com'END
I'm not quite sure I follow your logic, but I think if you change the IF statement as follows:
IF EXISTS (SELECT * FROM [database].[dbo].[DataLoadCheck] WHERE CAST(CheckDate AS DATE) = CAST(GETDATE() AS DATE) AND IsProcessed > 0)Understand that this change means one e-mail, regardless of the number of conditions that occur, and it doesn't identify the "offending rows".
sp_send_dbmail can either include a query result in the body or attach the result set as a file, which might work if the goal is to get a work list for someone to deal with.
November 1, 2017 at 7:21 am
Knowing which was a problem is a job for the @BodyText, this is a variant on something I use (since it looks like you are already using html type)
DECLARE @Res VARCHAR(MAX)
SELECT @Res = COALESCE(@Res , '') +
'<tr>'+
'<td text-align: center;> '+[id]+' </td>'+
'<td text-align: center;> '+[Processname]+' </td>'+
'<td text-align: center;> '+[isProcessed]+' </td>'+
'<td text-align: center;> '+[isSuccess]+' </td>'+
+ '</tr>'
FROM <YourTableName>
Select @Bodytext = '<!DOCTYPE html>
<html>
<style>
table, th, td {
border: 1px solid black;
border-collapse: collapse;
text-align: right;
}
th, td {
padding: 5px;
}
</style>
<body>
'+
'<table border: 1px solid black;><tr> <th> Id </th><th> Processname </th><th> isProcessed </th><th> isSuccess </th>'
+@Res
+'</table>'
+'</body>
</html> '
Where your <YourTableName> is whatever you set it to be in the initial query you gave...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply