question

meirelestgod avatar image
meirelestgod asked

My objetive is create an alert email that send automatically every week with the new complaint

I'm new in sql, and i have to create an alert email send automatically every week with the new complaint did the past week.

Can someone help me?

Thanks a lot.

sqlcreateemailemail settingalert
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
KenJ avatar image
KenJ answered

1. configure database mail if not already configured

2. set up a weekly SQL Agent job that executes sp_send_dbmail - https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-2017

for sp_db_mail, in addition to the regular parameters to send the message, you'll need to:

set the @query parameter to the query that selects new complaints

set the @execute_query_database parameter to the database that @query executes in

to attach the query results, set @attach_query_result_as_file = 1

and @query_attachment_filename to what you want the attachment to be named

You didn't mention SQL Server edition. If you are running SQL Server Express edition, you'll need to take a different scheduling and email approach since it won't have either database mail or SQL Agent. Perhaps schedule the task in windows task scheduler and have it execute a powershell script to execute the query and send the email.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.