Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Automatic Notification

Posted on 2004-08-09
8
Medium Priority
?
274 Views
Last Modified: 2010-05-19
Actually I have to start making a simple application which will check for the passport renewal date of the employees and send alerts on the screen and notification via email after specific periods of time, like 30 days, 15 days, 3 days, for a specific group users.

Isn't it possible to check the dates automatically... I m not that much good in SQL Server but I thought if I use SQL Server then there might be some way to do this...may be through some SP or triggers
0
Comment
Question by:khurram007
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 17

Expert Comment

by:BillAn1
ID: 11751270
Is this password etc for your application, and stored in the database, or are you talking about passwords for SQLServer itself?
If the fomer, then no problem, you can schedule a job to check your data, and send notifications no problem.
You can write a stored procedure that checks all users which are about to expire, then send a email using xp_sendmail.
If the latter, then no. SQLServer paswords do not expire, & if you are using windows authentication, then SQLServer does not (at least easily!) have access to information about these passwords etc
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 11751380
0
 
LVL 2

Expert Comment

by:DaveHavard
ID: 11751572
You could use a stored procedure containing:

declare @email varchar(255)
declare @overdue int
declare @message varchar(255)
declare c cursor fast_forward for
select       email,
      datediff(d, passportexpires, getdate())
from      table1
where      datediff(d, passportexpires, getdate()) in (30, 15, 3, 0)

open      c
fetch next from c into @email, @overdue

while (@@fetch_status=0)
begin
select @message='Your passport expired ' + cast(@overdue as varchar) + ' days ago!'
exec xp_sendmail @email, @message
fetch next from c into @email, @overdue
end
close c
deallocate c

However you will need to set up a scheduled task in SQL to run it each day. Go to Management\SQL Server Agent\Jobs in Enterprise Manager, create a new job and follow from there - shout if you need help.

Dave
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:khurram007
ID: 11751607
I am not asking about the SQL Password... Its the username and password and passport details(by passport I mean the International booklet of documents used for travel) in the database. Can you tell me in detail how to check it and send it. The best would be if provided with a working example...

Thanks..
0
 
LVL 2

Author Comment

by:khurram007
ID: 11751650
I thought that can be done without use of Task Scheduler.. isn't it???
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11751669
Appologies! I read your question, and thought I saw the word 'password', when in fact you had the word 'passport' - I'm sorry for the confusion.

Davehavard's example will provide you with what you want, possibly with one small change.

When you have the sp created, you schedule it using Enterprise Manager - SQLServer Agent -> Jobs
create a new job, with one step which is exec MyStoredProc
and set the schedule to what ever you want - daily most likely?


0
 
LVL 2

Author Comment

by:khurram007
ID: 11751854
hmmm....

I don't know anything about stored procedures...

Can you guide me from scratch???
0
 
LVL 2

Accepted Solution

by:
DaveHavard earned 2000 total points
ID: 11752140
Open query analyser, connect to your database server then run the code below remembering to replace the database, table and field names where listed:

use YOUR_DATABASE_NAME
go
create procedure spNotifyExpiredPassportUsers
as
declare @email varchar(255)
declare @overdue int
declare @message varchar(255)
declare c cursor fast_forward for
select      YOUR_EMAIL_FIELD_NAME,
             datediff(d, YOUR_PASSPORT_EXPIRES_FIELD_NAME, getdate())
from     YOUR_TABLE_NAME
where     datediff(d, YOUR_PASSPORT_EXPIRES_FIELD_NAME, getdate()) in (30, 15, 3, 0)

open     c
fetch next from c into @email, @overdue

while (@@fetch_status=0)
begin
--CHANGE THE MESSAGE TEXT TO WHAT EVER YOU WANT:
select @message='Your passport expired ' + cast(@overdue as varchar) + ' days ago!'
exec xp_sendmail @email, @message
fetch next from c into @email, @overdue
end
close c
deallocate c
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question