Email from SQL Server Management Console

390 pts.
Tags:
SQL Server Management
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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!!’

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    66,185 pointsBadges:
    report
  • RoadDust
    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
    390 pointsBadges:
    report
  • RoadDust
    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
    390 pointsBadges:
    report
  • RoadDust
    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
    390 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following