Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

Automatic Notification

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
khurram007
Asked:
khurram007
  • 3
  • 2
  • 2
  • +1
1 Solution
 
BillAn1Commented:
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
 
DaveHavardCommented:
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
khurram007Author Commented:
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
 
khurram007Author Commented:
I thought that can be done without use of Task Scheduler.. isn't it???
0
 
BillAn1Commented:
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
 
khurram007Author Commented:
hmmm....

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

Can you guide me from scratch???
0
 
DaveHavardCommented:
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now