Solved

Automatic Notification

Posted on 2004-08-09
8
270 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
  • 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:EugeneZ
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSIS On fail action 5 38
Database Integrity 1 49
Query for timesheet application 3 17
Current Month Filter in Visual Studio 10 22
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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

856 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