Sending of email!

hey people..i need some help in solving this problem..i have no idea how to start this because i've not done this before..

any help is appreciated.. :)

i'm doing this IT Helpdesk system for my project..

what i'm suppose to do now is : i have to auto generate an email everyday at 9am and 5pm to a supportstaff with the ticket details which status is not equal to close :)

how do i start about doing this??

please help..thanx!(:

Re: Sending of email!

Hi There,

Have a look at this article. would give you guideline from where to start.

http://msdn.microsoft.com/en-us/magazine/cc163821.aspx.

Let meknow if you furthur help!

Re: Sending of email!

write an window program e.g. and schedule it to run at the desired time

Re: Sending of email!

window program??

what's that?how do i start doing that??

please help.. :(

Re: Sending of email!

For an article on sending mail check out this site:

http://www.systemnetmail.com

A windows form is an application like Word or Excel.

Re: Sending of email!

BabyAngel
<div id=ctl00_ctl00_bcr_bcr_PostForm__QuoteText>
window program??

what's that?how do i start doing that??

please help.. :(

</div>

if SQL server database is involved in your case then its very easy without any program just write a query to send emails and schedule it using job schedular Read my article

http://www.aspsnippets.com/post/2009/03/08/Automated-Email-Notifications-using-SQL-Server-Job-Schedular.aspx

Re: Sending of email!

hey mudassarkhan..thanx for replying..your post seems quite useful..i just want to check if its possible to do this without using stored procedures??because i'm not using stored procedures for my project.. :)

Re: Sending of email!

BabyAngel
hey mudassarkhan..thanx for replying..your post seems quite useful..i just want to check if its possible to do this without using stored procedures??because i'm not using stored procedures for my project.. :)

Yes definately you can schedule your query too

Re: Sending of email!

i'm so sorry,,

but i need your help in this..

how do i schedule my query to this??

Re: Sending of email!

In the step 4 in my article just paste your query instead of stored proc

Re: Sending of email!

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

Re: Sending of email!

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.

Re: Sending of email!

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??

Re: Sending of email!

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

Re: Sending of email!

Declare a cursor for this query as i have done there

Re: Sending of email!

omg..this is hard..i seriously cant do this sia..

haiz.. :(

Re: Sending of email!

What are the columns in your query that you want to use in email?

Re: Sending of email!

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?

Re: Sending of email!

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?

Re: Sending of email!

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