DECLARE C1 CURSOR READ_ONLY
FOR
select * from ITHelpdesk.dbo.tickets a
inner join ITPortal.dbo.tbl_staffData b
on a.tbl_StaffDataRequesterID = b.Staff_ID
where a.TicketStatusID <> '6'
OPEN C1
FETCH NEXT FROM C1 INTO
@ID,@tbl_StaffDataRequesterID,@OpenDate, @DueDate,@SupportStaffID,
@Subject,@Desc0,@TicketCategoryID,@TicketTypeID,
@TicketPriorityID,@TicketStatusID,@Staff_email
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM C1 INTO
@ID,@tbl_StaffDataRequesterID,@OpenDate, @DueDate,@SupportStaffID,@Subject,@Desc0,@TicketCategoryID,@TicketTypeID,
@TicketPriorityID,@TicketStatusID,@Staff_email
END
CLOSE C1
DEALLOCATE C1
hey..let me ask you one more question..is it possible code this in my code behind??because one of the guy at my office say i can use Scripting..eg:VB Script.. :)
of course you can write in .net so the syntax is the same but
as i mentioned before. you can need to add the send mail function. you can just browse thru / google to see how the function is wrriten, here is an example
MailMessage message =
newMailMessage();message.From =
newMailAddress("aaa@yahoo.com");message.To.Add(newMailAddress("bbb@yahoo.com));message.CC.Add(newMailAddress("ccc@yahoo.com));message.Subject =
"This is my subject";message.Body =
"This is the content";SmtpClient
client = newSmtpClient();client.Send(message);
BabyAngel
Member
80 Points
226 Posts
Re: Sending of email!
Apr 23, 2009 09:13 AM|LINK
ok can..i go try this out..and get back to u aite..thanx :)
BabyAngel
Member
80 Points
226 Posts
Re: Sending of email!
Apr 24, 2009 02:27 AM|LINK
hey people..
i've got a problem..
i tried using the command thingy u give by modifying a little..but there's some error msges..
this my command:
---------------------------------------------------------------------------------------
DECLARE
@out_desc VARCHAR(1000),
@out_mesg VARCHAR(10),
@body varchar(20)
DECLARE
@ID int,
@tbl_StaffDataRequesterID int,
@OpenDate VARCHAR,
@DueDate VARCHAR,
@SupportStaffID int,
@Subject varchar(50),
@Desc0 varchar(50),
@TicketCategoryID int,
@TicketTypeID int,
@TicketPriorityID int,
@TicketStatusID int,
@Staff_email varchar(100)
DECLARE C1 CURSOR READ_ONLY
FOR
select * from ITHelpdesk.dbo.tickets a
inner join ITPortal.dbo.tbl_staffData b
on a.tbl_StaffDataRequesterID = b.Staff_ID
where a.TicketStatusID <> '6'
OPEN C1
FETCH NEXT FROM C1 INTO
@ID,@tbl_StaffDataRequesterID,@OpenDate, @DueDate,@SupportStaffID,
@Subject,@Desc0,@TicketCategoryID,@TicketTypeID,
@TicketPriorityID,@TicketStatusID,@Staff_email
WHILE @@FETCH_STATUS = 0
BEGIN
SET @body = '<table><tr><td>TicketID:</td><td>' + @ID + '</td></tr>' +
'<tr><td>requesterID:</td><td>' + @tbl_StaffDataRequesterID + '</td></tr>' +'<tr><td>OpenDate:</td><td>' + @OpenDate +'</td></tr>' + '<tr><td>DueDate:</td><td>' + @DueDate + '</td></tr>' +'<tr><td>SupportStaffID:</td><td>' + @SupportStaffID + '</td></tr>' + '<tr><td>Subject:</td><td>' + @Subject + '</td></tr>' + '<tr><td>Description:</td><td>' + @Desc0 + '</td></tr>' + '<tr><td>Ticket Category:</td><td>' + @ticketcategoryID + '</td></tr>' + '<tr><td>Ticket Type :</td><td>' + @ticketTypeID + '</td></tr>' + '<tr><td>Ticket Priority:</td><td>' + @ticketPriorityID + '</td></tr>' + '<tr><td>Ticket Status:</td><td>' + @ticketStatusID + '</td></tr>' </table>'
EXEC sp_send_mail
@Staff_email,
'xxxxxxx',
@Staff_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
@ID,@tbl_StaffDataRequesterID,@OpenDate, @DueDate,@SupportStaffID,@Subject,@Desc0,@TicketCategoryID,@TicketTypeID,
@TicketPriorityID,@TicketStatusID,@Staff_email
END
CLOSE C1
DEALLOCATE C1
----------------------------------------------------------------------------------------------
but when i click ok..i get this error message,
error - syntax error in the command :
error 170 : Line 35 : Incorrect syntax near '<'
unclosed quotation mark before character string ' ,
@Output_mesg = @out_mesg output,
@Output_desc = @out_desc output
print @out_mesg
print @out_desc
i cant spot the error..please help.. :(mo meng
Contributor
6700 Points
1351 Posts
Re: Sending of email!
Apr 24, 2009 02:36 AM|LINK
SET @body = '<table><tr><td>TicketID:</td><td>' + @ID + '</td></tr>' +
'<tr><td>requesterID:</td><td>' + @tbl_StaffDataRequesterID + '</td></tr>' +'<tr><td>OpenDate:</td><td>' + @OpenDate +'</td></tr>' + '<tr><td>DueDate:</td><td>' + @DueDate + '</td></tr>' +'<tr><td>SupportStaffID:</td><td>' + @SupportStaffID + '</td></tr>' + '<tr><td>Subject:</td><td>' + @Subject + '</td></tr>' + '<tr><td>Description:</td><td>' + @Desc0 + '</td></tr>' + '<tr><td>Ticket Category:</td><td>' + @ticketcategoryID + '</td></tr>' + '<tr><td>Ticket Type :</td><td>' + @ticketTypeID + '</td></tr>' + '<tr><td>Ticket Priority:</td><td>' + @ticketPriorityID + '</td></tr>' + '<tr><td>Ticket Status:</td><td>' + @ticketStatusID + '</td></tr>' </table>'
remove the ' before the </table>' i.e
SET @body = '<table><tr><td>TicketID:</td><td>' + @ID + '</td></tr>' +'<tr><td>requesterID:</td><td>' + @tbl_StaffDataRequesterID + '</td></tr>' +'<tr><td>OpenDate:</td><td>' + @OpenDate +'</td></tr>' + '<tr><td>DueDate:</td><td>' + @DueDate + '</td></tr>' +'<tr><td>SupportStaffID:</td><td>' + @SupportStaffID + '</td></tr>' + '<tr><td>Subject:</td><td>' + @Subject + '</td></tr>' + '<tr><td>Description:</td><td>' + @Desc0 + '</td></tr>' + '<tr><td>Ticket Category:</td><td>' + @ticketcategoryID + '</td></tr>' + '<tr><td>Ticket Type :</td><td>' + @ticketTypeID + '</td></tr>' + '<tr><td>Ticket Priority:</td><td>' + @ticketPriorityID + '</td></tr>' + '<tr><td>Ticket Status:</td><td>' + @ticketStatusID + '</td></tr></table>'
BabyAngel
Member
80 Points
226 Posts
Re: Sending of email!
Apr 24, 2009 05:26 AM|LINK
hey ya..so sorry for the late late reply...
anyway i tried and no error msges now..
but when i start job ryte??i get a pop up box saying : SQLServerAgent is not currently running so it cannot be notified of this action
why is that so??
mo meng
Contributor
6700 Points
1351 Posts
Re: Sending of email!
Apr 24, 2009 05:46 AM|LINK
from http://support.microsoft.com/kb/911841
BabyAngel
Member
80 Points
226 Posts
Re: Sending of email!
Apr 24, 2009 06:05 AM|LINK
hey ya..i tried to locate the SqlServerAgent..they dont even have la..how sia?
BabyAngel
Member
80 Points
226 Posts
Re: Sending of email!
Apr 24, 2009 06:12 AM|LINK
hey..let me ask you one more question..is it possible code this in my code behind??because one of the guy at my office say i can use Scripting..eg:VB Script.. :)
malcolms
All-Star
18687 Points
3124 Posts
MVP
Re: Sending of email!
Apr 24, 2009 06:18 AM|LINK
it is possible:
http://www.systemnetmail.com
BabyAngel
Member
80 Points
226 Posts
Re: Sending of email!
Apr 24, 2009 06:28 AM|LINK
hey malcolms...thanx for replying..and thanx for the URL..i shall go read this up and try.. :)
mo meng
Contributor
6700 Points
1351 Posts
Re: Sending of email!
Apr 24, 2009 06:55 AM|LINK
of course you can write in .net so the syntax is the same but
as i mentioned before. you can need to add the send mail function. you can just browse thru / google to see how the function is wrriten, here is an example
MailMessage message = new MailMessage();message.From = new MailAddress("aaa@yahoo.com"); message.To.Add(new MailAddress("bbb@yahoo.com)); message.CC.Add(new MailAddress("ccc@yahoo.com));message.Subject = "This is my subject";message.Body = "This is the content"; SmtpClient client = new SmtpClient();client.Send(message);