How to track when a data download from SQL Server DB in Email

Question

I am using SQL server email client.

I am sending mails using

EXEC msdb.dbo.sp_send_dbmail                                                                           
    @profile_name = 'Mailer_Profile',    
    @recipients = @Email,      
    @copy_recipients = @ccRecipts,    
    @body = @MESSAGE  ,                                                                           
    @body_format = 'HTML',        
    @subject = @SUB

In the @MESSAGE we are putting some information , when the mail will be opened , it has to be downloaded from our Database end. We need to track this. i.e. when it was downloaded or which mail ID it has downloaded.

Answer 1

You want know if the mail has been opened, or has the link been clicked and some data downloaded?

If the former is true, then there's no way to check this from sql server. Over here you can check sent_status field, which has three values for: 1=sent, 2=failed and 3=unsent. But no value for delivered.

SELECT * FROM msdb..sysmail_mailitems

If you want to check if the user has clicked the link, then your code EXEC msdb.dbo.sp_send_dbmail doesn't help much, because we don't know what is supposed to happen when the user clicks the link, and you'll have -10 for this question very soon :).