| Author |
Topic |
|
BaldEagle
Starting Member
23 Posts |
 Posted - 2002-04-20 : 19:52:59
|
| I want a job schedule that will check for all the appointments who are 1 day after today's date. And i will run this job daily. Any advice on how to do this? Must i create a procedure 1st? Please teach me from step 1 as i am quite a newbie in this field of SQL Server. Thanks in advance everyone. :D |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-04-20 : 22:07:19
|
| Well, I'm not going to give you a step by step, but I will tell you a few things that will help you more in the long run.First, the DateDiff function. It will tell you the difference between two dates using any unit you specify. So SELECT DateDiff(d, GetDate(), AppointmentDate)Will tell you how many days in the future an appointment is. If you use that in a WHERE clauseWHERE DateDiff(d, GetDate(), AppointmentDate) = 1will give you all the appointments that are one day away. So that will give you an idea how you need to write your query.As for scheduling it, the easiest way is using Enterprise Manager. Look in books online for scheduling tasks, or just start to play in Enterprise Manager, you will get the idea pretty quick.Hope that helpsDamian |
 |
|
|
BaldEagle
Starting Member
23 Posts |
Posted - 2002-04-21 : 00:26:34
|
| Ya i noe the DateDiff part. ok in order for me to get a job schedule started i must 1st do a procedure 1st right? But the problem is i am not gd in writing procedures. Can enlighten me?I have this SQLSelect a.*, c.Email From FIS_Appointment a, FIS_User b, FIS_StaffContact c Where DateDiff("d",getDate(),a.ADate) = 1 AND a.UserID = b.UserID AND b.StaffContactID = c.StaffContactIDSo now how can i loop thru the results so that i will send an email to every results's email.Please advice. Thanks Thanks |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-04-21 : 00:51:03
|
| Do you realise what you have asked for vs what you need ????You asked : "do I need a procedure to run a job ? "You want : a system to send emails on a schedule.Big Difference!Creating a prodecure is as simple as dropping a query into a Create Procedure MyProcNameASYour query hereAs for the email part. Rather than re-hash it all, do a search of this site on emails, I think the "Mail Queue" system might help you a lot. Your procedure could insert into a queue table, then use the queue system to send the emails.Read the articles, then post any specific questions.Remember, it is really hard to help someone who doesn't ask the right questionDamianedited to fix dsiplayhei guy, don't play on my postingEdited by - robvolk on 04/21/2002 07:35:35Edited by - merkin on 04/21/2002 07:47:15 |
|
|
|
BaldEagle
Starting Member
23 Posts |
Posted - 2002-04-21 : 01:13:55
|
| ok sorry abt that. What is the syntax to loop through a result in a procedure. Coz for asp is Do while Not objRs.EOF |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-04-21 : 01:41:04
|
| What you are asking about is a Cursor. The syntax is in Books OnlineBut I would recommend against it.Have you read the mail articles yet ? How do you want to send the emails ?Damian |
|
|
|
BaldEagle
Starting Member
23 Posts |
Posted - 2002-04-21 : 01:47:32
|
| ya i m reading it now. Trying to understand. Can i ask u if i have questions. Thanks for ur patience and help. :D |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-04-21 : 08:11:34
|
| WHERE AppointmentDate >= DATEADD(d, DATEDIFF(d, '2000-01-01' GetDate())+1, '2000-01-01') AND AppointmentDate < DATEADD(d, DATEDIFF(d, '2000-01-01' GetDate())+2, '2000-01-01')It may be ugly, but at least it's sargable! |
|
|
|
|
Job Schedule