Batch process for sending mail

Hello everyone,

I want to write a batch process that will run everyday and send emails.

The scenario is as follows:

There is a websummit held every year and people can register for it. However the request is subjected to be approved/rejected by the admin belonging to that area. Whenver a person enrolls, the RequestStatus is 0. The admin has to change the RequestStatus to either 1(accepted) or -1(rejected).

I want to write a batch process that will send mails to all admins belonging to their particular areas, everyday at 8.00 A.M. The email will be sent whenever the request has not been changed(ie. RequestStatus=0) and their is a lapse of more than 7 days for their area or the request count for their area has exceeded 5.

Table structure:

Admin

AdminID

Email

AdminArea

AdminID

AreaCode

Area

AreaCode

AreaName

WebSummit

SummitId

RequestorName

DateOfRequest

RequestStatus

AreaCode

Regards,

Vidya.

Hi,

Kindly advise.

Regards,

Vidya

|||

You'll probably want to use the master.dbo.xp_sendmail procedure, using the @.query parameter.

Roughly, create a SQL Server Agent job to run at 8:00AM

and run code that creates a table of recipients, then execute xp_sendmail for each recipient with the query to provide the information in the email you want them to see.

|||

Hi,

Thanks for reading. Some code will be useful.

Regards,

Vidya

|||

Here's the basic template for the script to be executed.

You'll need to adjust the queries to be exactly what you need.

I just did a simple query to get the admin who had 5 or more requests present.

Code Snippet

createtable #Admin (AdminID int, Email varchar(50))

insertinto #Admin values(1,'admin1@.mycompany.com')

insertinto #Admin values(2,'admin2@.mycompany.com')

insertinto #Admin values(3,'admin3@.mycompany.com')

insertinto #Admin values(4,'admin4@.mycompany.com')

createtable #AdminArea (AdminID int, AreaCode int)

insertinto #AdminArea values(1, 100)

insertinto #AdminArea values(2, 200)

insertinto #AdminArea values(3, 300)

insertinto #AdminArea values(4, 400)

createtable #Area (AreaCode int, AreaName varchar(50))

insertinto #Area values(100,'Area 100')

insertinto #Area values(200,'Area 200')

insertinto #Area values(300,'Area 300')

insertinto #Area values(400,'Area 400')

createtable #WebSummit ( SummitId int, RequestorName varchar(50),

DateOfRequest datetime, RequestStatus int, AreaCode int)

insertinto #WebSummit values( 1,'George Jetson',getdate(), 1, 100 )

insertinto #WebSummit values( 1,'Jane Jetson',getdate(), 1, 200 )

insertinto #WebSummit values( 1,'Fred Flinstone',getdate(), 1, 100 )

insertinto #WebSummit values( 1,'Wilma Flintsone',getdate(), 1, 100 )

insertinto #WebSummit values( 1,'Barney Rubble',getdate(), 1, 100 )

insertinto #WebSummit values( 1,'Betty Rubble',getdate(), 1, 100 )

droptable #to

select a.AdminID, a.Email

into #to

from #Admin a

where a.AdminID in

(

select a.AdminID

from #Admin a

innerjoin #AdminArea aa

on a.AdminID = aa.AdminID

innerjoin #Area b

on aa.AreaCode = b.AreaCode

innerjoin #WebSummit w

on b.AreaCode = w.AreaCode

groupby a.AdminID

havingcount(*)>4

)

declare elist cursorfor

select adminid, email from #to

declare @.id int, @.email varchar(50), @.cmd nvarchar(4000)

open elist

if@.@.cursor_rows> 0

begin

fetch next from elist into @.id, @.email

while@.@.fetch_status=0

begin

set @.cmd = N'put your query here WHERE AdminID = '+cast(@.id asvarchar(10))

exec master.dbo.xp_sendmail @.recipient=@.email, @.query=@.cmd,

@.subject='Summit Follow-up'

fetch next from elist into @.id, @.email

end

end

close elist

deallocate elist

|||

Thanks Dale.

That was very helpful.

Regards,

Vidya.