Scheduling Email Using Sql Server

DECLARE
@out_desc VARCHAR(1000),
@out_mesg VARCHAR(10)

DECLARE @name VARCHAR(20),
@birthdate datetime,
@email NVARCHAR(50)

DECLARE @body NVARCHAR(1000)

DECLARE C1 CURSOR READ_ONLY
FOR
SELECT [name], [birthdate], [email]
FROM Customers

OPEN C1
FETCH NEXT FROM C1 INTO
@name, @birthdate, @email
WHILE @@FETCH_STATUS = 0
BEGIN
IF DATEPART(DAY,@birthdate) = DATEPART(DAY,GETDATE())
AND DATEPART(MONTH,@birthdate) = DATEPART(MONTH,GETDATE())
BEGIN
SET @body = 'Happy Birthday ' + @name +
'
Many happy returns of the day'
+ '

Customer Relationship Department'
EXEC sp_send_mail
sender@abc.com',
'xxxxxxx',
@email,
'Birthday Wishes',
@body,
'htmlbody',
@output_mesg = @out_mesg output,
@output_desc = @out_desc output

PRINT @out_mesg
PRINT @out_desc
END
FETCH NEXT FROM C1 INTO
@name, @birthdate, @email
END
CLOSE C1
DEALLOCATE C1



Now to make the above script automatically run daily we will need to schedule it to run daily using the SQL Server Job Scheduler.

Below I’ll explain how to schedule the script to run daily using Job Scheduler




Step 1

In the SQL Server Enterprise Manager expand the Management Tab and select SQL Server Agent Node.

Refer figure below.





Step 2

On the Right Panel Select Job, Right Click it and select New Job from context menu to open New Job Window.

Refer figure below.





Step 3

In the New Job Window, in General Tab enter the following details

1. Name - Name of the Job
2. Description – Description of the Job (Optional)
3. Enabled – Determines whether job is enabled or disabled

Refer figure below





Step 4

In the New Job Window, in Steps Tab click New Step to open a New Step Window

In the New Step Window enter the following details

1. Step Name - Name of the Step
2. Type – Select Transact SQL Script
3. Database – Select the database on which you want to run the script.
4. Command – Paste the SQL Script which you wish the Job Scheduler to run.


Refer figure below





Step 5

In the New Job Window, in Schedules Tab click New Schedule to open a New Schedule Window

In the New Schedule Window enter the following details

1. Name - Name of the Step
2. Enabled – Determines whether Schedule is enabled or disabled
3. Schedule Type – Select Recurring schedule type since we need to run it daily


Refer figure below





Step 6

Next click on Change button in the Schedule Window to set the schedule

In the Edit Schedule Window enter the following details

1. Occurs – Daily since we need to run it daily
2. Daily Frequency – Since we need to run once a day, select the time you wish to run
3. Start date – Select date from when you want the schedule to run.


Refer figure below





Step 7

That’s it and your job is created. You will see a new entry in the SQL Server agent -----> Jobs

To start the job right click the job and in the context menu click Start Job



This code is Taken From :
http://www.aspsnippets.com/Articles/Automated-Email-Notifications-using-SQL-Server-Job-Schedular.aspx