You will want to create a SQL Agent job and have that job run the sp_send_dbmail procedure to send the email. One of the parameters of that procedure is @Query which will allow you to give it a SQL Query such as “SELECT * FROM MyView” and it will email out the output from the view.
I tried the above and am now getting the error “Failed to notify DustinL via Email”. I have setup DustinL as my operator, I’ve double checked my email address that I entered. I’ve even gone into the DatabaseMail and made sure the profile was correct. Also, I am able to send myself a test email.
Rather than set yourself up as an operator, you could just put your address in the ‘recipient’ parameter of the stored proc…
@profile_name = ‘MyMailProfile’,
@recipients = ‘DLaw@Daniluk.com’,
@blind_copy_recipients = firstname.lastname@example.org’,
@query = ‘SELECT * FROM MyView’,
@subject = ‘DBMail Is My Friend!!’