CODEWITHSUNDEEP
sqlsql-server
hhh
hhh

Reputation: 21

Send a SQL server email every 1 hour

I have the following that sends an email to recipients that I get from a table in my database. What Im trying to do is send this email every hour. How could I do that?

This is what I have right now?

DECLARE @recipient VARCHAR(4000)

USE data

SELECT @recipient = STUFF((SELECT ';' + email
                        FROM dbo.email
                        FOR XML PATH('')
                       ), 1, 1, '')

EXEC msdb.dbo.sp_send_dbmail 
@profile_name='SQL Server Alerts System',
@recipients = @recipient,
@subject='Test message',
@body='This is the body of the test message.
Congrates Database Mail Received By you Successfully.'

Upvotes: 2

Views: 1339

Answers (2)

Wen Ji
Wen Ji

Reputation: 55

I know this post is long ago but just to add, you can actually do the following:

  1. Put the statement where you select email recipient and execute the mail sending into a stored procedure.
  2. Create a job
  3. At Schedules tab, set when you want the job to run. For me i usually run it 12.00am daily.
  4. Next in the 'Steps' tab click on 'New'.
  5. Put in a name for the step. Type should be Transact-SQL. For Run as it should be the database owner(or SA, dbo...)
  6. Include an execute statement of the stored procedure you created just now for mail sending in the command box area.

You can test it for yourself. Just wait patiently until the time for the job to run and see if you/recipient recipient receives any email

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300559

Create a scheduled SQL Server Agent Job

Upvotes: 3

Related Questions