Solved

Automatic Notification

Posted on 2004-08-09
8
265 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
Comment Utility
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
Comment Utility
0
 
LVL 2

Expert Comment

by:DaveHavard
Comment Utility
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
 
LVL 2

Author Comment

by:khurram007
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 2

Author Comment

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

Expert Comment

by:BillAn1
Comment Utility
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
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL 2014 get SPIDs of users 6 25
C# SQL BULK INSERT CLASS 5 33
Complex SQL 10 32
Group by and order by clause 28 35
In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
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 extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now