Check if email for each specific recipient was sent or not sql server 2008
-
I have a table with students' details and email address and I send email to them I want to see the logs for each student if it was sent successfully or not. for example:
STUD_1 student1@email.com failed
STUD_2 student2@email.com sentCan we do it like this? The idea is to get the sent_status from
sysmail_mailitems
(or any other accurate/relevant source) for each student.
-
I have a table with students' details and email address and I send email to them I want to see the logs for each student if it was sent successfully or not. for example:
STUD_1 student1@email.com failed
STUD_2 student2@email.com sentCan we do it like this? The idea is to get the sent_status from
sysmail_mailitems
(or any other accurate/relevant source) for each student.
-
If I understand the question correctly, you can use a query like
SELECT mailitem_id,
recipients,
subject,
sent_status
FROM sysmail_allitemsFor table description, see sysmail_allitems[^]
can we link sysmail_allitems to my student table? to see if the email was sent or not to a particular student?
-
can we link sysmail_allitems to my student table? to see if the email was sent or not to a particular student?
If the students table contains the email address, then you can use that to check if the recipient is equal to student.email. If multiple recipients can be found ind recipients field then you can join using a string search with CHARINDEX function. So either something like:
SELECT ...
FROM sysmail_allitems sai,
student s
WHERE LOWER(sai.recipients) = LOWER(s.email)or
SELECT ...
FROM sysmail_allitems sai,
student s
WHERE CHARINDEX(LOWER(s.email), LOWER(sai.recipients)) > 0 -
If the students table contains the email address, then you can use that to check if the recipient is equal to student.email. If multiple recipients can be found ind recipients field then you can join using a string search with CHARINDEX function. So either something like:
SELECT ...
FROM sysmail_allitems sai,
student s
WHERE LOWER(sai.recipients) = LOWER(s.email)or
SELECT ...
FROM sysmail_allitems sai,
student s
WHERE CHARINDEX(LOWER(s.email), LOWER(sai.recipients)) > 0It's usually cleaner to use the ANSI
JOINsyntax, so that you keep the join conditions separate from any filter conditions.SELECT
...
FROM
sysmail_allitems sai
INNER JOIN student s
ON sai.recipients = s.emailAlso, most databases don't use a case-sensitive collation, so there shouldn't be any need to use the
LOWERfunction here.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
It's usually cleaner to use the ANSI
JOINsyntax, so that you keep the join conditions separate from any filter conditions.SELECT
...
FROM
sysmail_allitems sai
INNER JOIN student s
ON sai.recipients = s.emailAlso, most databases don't use a case-sensitive collation, so there shouldn't be any need to use the
LOWERfunction here.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Thanks for the suggestion :)
Richard Deeming wrote:
It's usually cleaner to use the ANSI
JOINsyntaxI usually use ANSI join but in this case I felt it would be more understandable to use the 'old' syntax because the variations for the join itself are quite different.
Richard Deeming wrote:
most databases don't use a case-sensitive collation, so there shouldn't be any need to use the
LOWERfunction here.Not sure about this so I wanted to be on the safe side. I should have written these reasons to the original post in the first place. :)
