Solved

Automatic Notification

Posted on 2004-08-09
8
271 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 42

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

730 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