under general: enter Name & Description to what ever you like
under stpes: clilck on New, general: enter Step Name, choose the Database, enter the SQL command e.g what is provided in the mudass link, parse the quesry make sure correct, advanced: choose on success action, i'll chose Quit the job reporting success and set
retry attemps if you want to, OK
under schedules: enter a name, and select what ever appropriate, OK.
i not sure the datatype you have they so you may need to change it & you may need to join other table to get the email address to sent to the recipient
just gv a try here you may need tweak here and there
DECLARE C1 CURSOR READ_ONLY
FOR
Select * from ITHelpdesk.dbo.tickets
where TicketSTatusID <> 6
OPEN C1
FETCH NEXT FROM C1 INTO
@ticketsID,@requesterID,@SupportStaffID, @emailID
WHILE @@FETCH_STATUS = 0
BEGIN
mo meng
Contributor
6700 Points
1351 Posts
Re: Sending of email!
Apr 23, 2009 06:36 AM|LINK
make sure you login as an administrator,
expand your server name,
expand sql server agent,
right click on job and new job
p.s. i pm u
mo meng
Contributor
6700 Points
1351 Posts
Re: Sending of email!
Apr 23, 2009 07:20 AM|LINK
under general: enter Name & Description to what ever you like
under stpes: clilck on New, general: enter Step Name, choose the Database, enter the SQL command e.g what is provided in the mudass link, parse the quesry make sure correct, advanced: choose on success action, i'll chose Quit the job reporting success and set retry attemps if you want to, OK
under schedules: enter a name, and select what ever appropriate, OK.
BabyAngel
Member
80 Points
226 Posts
Re: Sending of email!
Apr 23, 2009 07:46 AM|LINK
am i suppose to put this in the command??
Select * from ITHelpdesk.dbo.tickets
where TicketSTatusID <> 6
because i want to send email with the records of ticket which Status is not closed(6)
is it??
mo meng
Contributor
6700 Points
1351 Posts
Re: Sending of email!
Apr 23, 2009 07:57 AM|LINK
you need to declare a cursor as what in the link porivded by mudas, which loop all the data for you select statement than send the email one by one
refer to the link for the cursor function
mudassarkhan
All-Star
78956 Points
13402 Posts
MVP
Re: Sending of email!
Apr 23, 2009 08:04 AM|LINK
Declare a cursor for this query as i have done there
Contact me
BabyAngel
Member
80 Points
226 Posts
Re: Sending of email!
Apr 23, 2009 08:06 AM|LINK
omg..this is hard..i seriously cant do this sia..
haiz.. :(
mudassarkhan
All-Star
78956 Points
13402 Posts
MVP
Re: Sending of email!
Apr 23, 2009 08:15 AM|LINK
What are the columns in your query that you want to use in email?
Contact me
mo meng
Contributor
6700 Points
1351 Posts
Re: Sending of email!
Apr 23, 2009 08:20 AM|LINK
yes, can you provide what do you want the email looks like, do you store the email's id in the same table as what you hv write just now?
BabyAngel
Member
80 Points
226 Posts
Re: Sending of email!
Apr 23, 2009 08:26 AM|LINK
i want to display all the columns in my tickets table where the status is not equals to closed..
the columns are ticketsID,requesterID,SupportStaffID etc etc....
and u need the emailID column too?
mo meng
Contributor
6700 Points
1351 Posts
Re: Sending of email!
Apr 23, 2009 08:48 AM|LINK
i not sure the datatype you have they so you may need to change it & you may need to join other table to get the email address to sent to the recipient
just gv a try here you may need tweak here and there
DECLARE
@out_desc VARCHAR(1000),
@out_mesg VARCHAR(10)
DECLARE @ticketsID int,
@requesterID int,
SupportStaffID int,
emailID varchar(100)
DECLARE C1 CURSOR READ_ONLY
FOR
Select * from ITHelpdesk.dbo.tickets
where TicketSTatusID <> 6
OPEN C1
FETCH NEXT FROM C1 INTO
@ticketsID,@requesterID,@SupportStaffID, @emailID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @body = '<table><tr><td>TicketID:</td><td>' + @ticketsID + '</td></tr>' +
'<tr><td>requesterID:</td><td>' + @requesterID + '</td></tr>' +
'<tr><td>SupportStaffID:</td><td>' + @SupportStaffID + '</td></tr></table>'
EXEC sp_send_mail
@emailID,
'xxxxxxx',
@email,
'Birthday Wishes',
@body,
'htmlbody',
@output_mesg = @out_mesg output,
@output_desc = @out_desc output
PRINT @out_mesg
PRINT @out_desc
FETCH NEXT FROM C1 INTO
@ticketsID,@requesterID,@SupportStaffID, @emailID
END
CLOSE C1
DEALLOCATE C1