Email from SQL Server Management Console
I have created a view in the SQL Server Management Console and was wondering if there was anyway I could email that view on a monthly basis? I know that I can send notifications but there doesn't seem to be a way to have it attach my view.
Your help is appreciated,
Dustin
DLaw@Daniluk.com
Software/Hardware used:
Software/Hardware used:
ASKED:
September 16, 2008 8:59 PM
UPDATED:
September 19, 2008 4:05 PM
Answer Wiki:
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.
Thanks,
Dustin
Dustin,
Rather than set yourself up as an operator, you could just put your address in the 'recipient' parameter of the stored proc...
EXEC msdb..sp_send_dbmail
@profile_name = 'MyMailProfile',
@recipients = 'DLaw@Daniluk.com',
@blind_copy_recipients = who.ever@what-ever.com',
@query = 'SELECT * FROM MyView',
@subject = 'DBMail Is My Friend!!'
To see all answers submitted to the Answer Wiki: View Answer History.
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.
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.
Thanks,
Dustin
Thanks for your help, Now when I try to run the T-SQL I get this message:
Error formatting query, probably invalid parameters
And I’ve even tried putting Select * from dbo.tblClients into the @query and get the same error.
If I take out the @query it sends the email just fine.
Thanks,
Dustin
Nevermind, once I added my database name to the SQL statement it worked. Instead of dbo.QryJobTimeNoEntryLast30 it had to be Jobtracking.dbo.QryJobTimeNoEntryLast30.
Thanks a lot for your help.
Dustin