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.
Answer by KenJ ·
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.