Solved

How can I send up to 5 people and email alert that a contract is about to expire?

Posted on 2016-07-14
20
61 Views
Last Modified: 2016-07-18
Our company has many, many contracts, with responsible persons in multiple departments of our company.

I'm tasked with developing a database of these contracts and their expiration dates and a system where alerts are sent to up to 5 people when a contract is within 90 days, 60 days, 30 days and 15 days of expiring. (The db and input form are done.)

So for a given contract, when its expiration date is exactly 90 days from expiration, one email is sent to the email addresses listed in the database.  Then at 60 days prior to expiration another email is sent to them.  Etc...


I've tried many solutions using sp_send_dbmail (T-SQL) and just can't find one that works for this scenario.

The first issue, I can't get multiple email addresses into the @recipients parameter from multiple table columns (email01, email02, email03, etc.)
Another issue I foresee is when more than one different contract expires on the same day, sending the appropriate messages to the appropriate recipient(s).

I'm wondering if there's a better approach, entirely; not using sp_send_dbmail.  The data input form for the table for this thing is in ASP.Net 4.0 and C# codebehind.  I could put the system in the codebehind of the input form, but that would require someone opening the form in order to trigger the check for expiring contracts.  I need the check to happen every single day.  The form may not be used for weeks at a time.

The single table has the following columns:
Company VARCHAR (100)
ExpirationDate (date, null)
email01 VARCHAR(50)
email02 VARCHAR(50)
email03 VARCHAR(50)
email04 VARCHAR(50)
email05 VARCHAR(50)

This is a huge thing for my company.  I really want to find a good solution.  It seems pretty simple, but I'm stumbling terribly on it.  Thanks in advance.
0
Comment
Question by:megnin
  • 10
  • 6
  • 2
  • +1
20 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 41711243
One idea would be to write a .Net Console application that handles sending out emails. You would install the application on a server, and then create a Scheduled Task to run the application as a set time.
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 41711244
The check should be performed by something suited to run on schedule: SQL Agent, or Scheduled Tasks running a batch file.
Because I like to do things in PowerShell, I would use that. It is easy to execute an SQL, loop thru the results and do whatever is required, including sending mails. Each step is relative simple, and easy to adapt because it is a scriping langauge  (versus a compiled program).
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 41711268
Another alternative is a report that runs daily and lists contracts expiring that are relative to the user.  Are you by chance using SSRS or another report server?
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 1

Author Comment

by:megnin
ID: 41711321
Shaun, that's one of the ideas I'm considering.  I've only written a three or four console applications, so I'd be Googling the syntax of every line of code and the whole email subsystem.    ;-)  I'm more familiar with web applications, but this thing has to run every single day.

Brian, yeah, we use SSRS and I love making reports with it.  I wish I could do this in SSRS.  However, our staff can't be bothered to look at a report.  They want something else to take the action to remind them when a contract is about to expire in 90 days, then when they ignore that, again at 60 days until they only have two weeks left and give them a fourth reminder.  Great idea though.  I wish SSRS would send emails.  ... I just Googled "email with SSRS" and it seems that it can.  Do you know if it can send the alerts per my original question?  I.e. with the alert going to the address(es) in the record that meets the alert criteria (i.e. 90 days before expiration, etc.)?

Qlemo, I don't know PowerShell.  Could you give me an example script that would do what I'm looking for?  I could put it on a server and schedule it to run every day.  Thanks.

For the sp_send_dbmail solution, I'd be over one big hurdle if I could get the [up to] five email addresses into the @recipients parameter and working.  I'll have to read more about SSRS's mail system.  

Any code examples would be really helpful.  Thanks again!
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 41711360
For dbmail, since you are looking at a set of data returned for 90, 60 or xx days, you would need to use a cursor to send an email per expiring contract. For the recipients for a single contract, you can assign each column from your query (e.g. Select @Recipient1 = email01, @Recipient2 = email02 ... From <table>)  to separate variables and then combine them into a single variable (e.g. @EmailRecipients). You should then be able to assign the variable with all of the recipients to the @recipients parameter of the sp_send_dbmail. With that said, cursors in SQL are memory intensive and you may want to consider that as a last resort.

Brian may have more recent details on SSRS. When I last used it, I don't believe you could get a list of recipients from a query. I believe you needed to explicitly state the recipients in the SSRS task that generates and sends the email.

For a console application, your code is probably mostly written, as your web application most likely does most of what you need: Query the database, loop through the result set, send emails.

The email portion is using System.Net.Mail. To start, you create a MailMessage object, set the properties similar to sending an email from Outlook (From, To, CC, BCC, Subject, Body), set the IsBodyHTML property (if the body of the email is in HTML format). Once the mail message is ready, you need to create a SmtpClient object, set properties on it for your mail server, and then use the Send method to send you email.

For an example, reference Microsoft's page on the MailMessage object: https://msdn.microsoft.com/en-us/library/system.net.mail.mailmessage(v=vs.110).aspx
0
 
LVL 1

Author Comment

by:megnin
ID: 41711376
Shaun, thank you so much for explaining the pros and cons of the options.  I have little experience in most of this, so it all really helps.  Using a separate variable for each of the email addresses and then combining them is a great idea I hadn't thought of.

If I continue to have trouble with sp_send_dbmail, then the console application solution is starting to look more attractive.  I'd actually like to work on another console app.  I haven't done anything but web apps in quite a while.

Oh, fyi, I won't be in the office tomorrow, so don't think I've abandoned this if I don't respond again until Monday.  I sincerely appreciate your help.  Brian and Qlemo, I appreciate your suggestions as well.  I would welcome anything else you may add.  Do have a good weekend, all.  Hopefully I'll have some success to report on Monday ... or more questions.  ;-)
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 41711377
You can absolutely do this with SSRS.  Create the report so that it is specific to the requesting user (this link may help).  Another advantage to this is that it makes the users responsible for some of the management through subscriptions that show up in their email daily/weekly.
0
 
LVL 1

Author Comment

by:megnin
ID: 41711409
Brian, oh, that's good to hear!  Thanks for that link.  It's a bit late in the day to get started researching the SSRS solution, but I'm excited about that.  I do love working with SSRS.
0
 
LVL 69

Accepted Solution

by:
Qlemo earned 500 total points
ID: 41711551
This is the PowerShell script. Store it as e.g. c:\temp\ee\ExpiryNotification.ps1, and create a scheduled task with action of powershell, parameters -noninteractive -ExecutionPolicy Bypass -File c:\temp\ee\ExpiryNotification.ps1.
Of course you need to set the connection string and the mail parameters for SMTP server and sender correctly.

The query to get the mail addresses right is "rather safe than sorry", as I'm not certain whether your unoccupied fields contain empty strings or NULL. This works for both.
<# connection string and SQL command to execute #>
[String] $constr = 'Server=MyServer\MyInstance; Database=MyDB; Integrated Security=ssp'
[String] $sqlCmd = @'
  select Company, datediff(day, getdate(), ExpirationDate) as diff, email01, email02, email03, email04, email05
    from Tbl
   where datediff(day, getdate(), ExpirationDate) in (30,60,90)
'@


$set = New-Object System.Data.Dataset
(New-Object System.Data.SqlClient.SqlDataAdapter ($sqlCmd, $constr)).Fill($set) | out-null

$set.Tables[0] |
  % {
    $to = $_.eMail01, $_.eMail02, $_.eMail03, $_.eMail04, $_.eMail05 | ? { $_ -ne '' -and !($_ -is [DBNull]) }
    <# Mail parameters #>
    Send-MailMessage -SmtpServer mail.company.com -From Reporting@company.com -To $to `
                     -Subject "Contract for $($_.Company) will expire in $($_.Diff) days" -whatif
  }

Open in new window

0
 
LVL 1

Author Comment

by:megnin
ID: 41717053
Qlemo, that's awesome.  Blank email addresses are actually empty strings.  Not for any particular reason.

That looks to me, not knowing PowerShell, like it should do exactly what I need.  That is:
1. Poll the database for any company WHERE the contract ExpirationDate is 30, 60, or 90 days away.
2. Send an email to the [up to] five addresses stating which company contract is expiring in how many days.

:-D

What's the "-whatif" at the very end do?
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 41717096
Sorry, please remove the -whatif, it does not work here. Most cmdlets allow for this switch to simulate what they would do - that is, they just echo a message with some info, without performing the action as requested.

If you can "read" the PowerShell script, I'm proud about that. Because it was my goal to have something simple, which you can apply small changes yourself to probably.
Should you ever have the need, the Send-MailMessage is able to add an body and attachments, of course (with the parameters named the same).
0
 
LVL 1

Author Comment

by:megnin
ID: 41717312
Hi Qlemo, I'm going to try the PowerShell route.  Can you help me with the connection string?  I'm just not sure how to specify the Server "Instance."

Here is the form of a typical connection string I use in ASP.Net web forms:
<add name="ContractsConnectionString" connectionString="Data Source=OurSQLServer;Initial Catalog=Contracts;Integrated Security=True" providerName="System.Data.SqlClient" />


[String] $constr = 'Server=MSQLServ; Database=Contracts; Integrated Security=ssp'


How best can I test this before setting up a scheduled task?
...more questions... Does it need to run on the Server?  Looks like it may run from any [my] computer on the network.
Schedule Task and Parameters... What scheduling mechanism?  Not really something I've done before.  (Windows Task Scheduler on my PC?)
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 41717507
You should be able to use the connection string from .NET without changes in PowerShell, so:
[String] $constr = 'Data Source=OurSQLServer;Initial Catalog=Contracts;Integrated Security=True'

Open in new window

Single or double quotes do not make a difference here - whatever you like more. But a string enclosed in single quotes is literally, while one in double quotes allows for escape sequences like tab, return aso., and variable expansion. Single quotes are more safe.

For testing, I would insert a test record via any arbitrary SQL tool, then just run the PowerShell script, e.g. by using the full command line in a DOS prompt:
powershell -NoExit -ExecutionPolicy Bypass -File c:\temp\ee\ExpiryNotification.ps1

Open in new window

I've left out the -noninteractive and replaced with -NoExit intentionally - for testing outside of a scheduled task this is much better, as you get prompts if something might be missing, and PowerShell stays open for reviewing the output, looking at the vars aso.

The script should be able to run on any PC being able to connect to SQL Server machine (i.e. able to resolve the name, no firewall etc.).

Scheduled Task is exactly that. Just use the Wizard to create a new task.
0
 
LVL 1

Author Comment

by:megnin
ID: 41717522
I used the connection string as I posted it above and it works.  I've tested it on my PC and it work great.  My boss gave me a "High Five", so a virtual High Five to you as well.  ;-)  

Now it's just a matter of putting it in a Scheduled Task and formatting the Body.  I put some HTML in the body and used the  -BodyAsHtml switch.  Works great!  :-D

I've been wanting to learn PowerShell for a long time and this gives me an excuse to play with it more.  

Thank you!
0
 
LVL 1

Author Comment

by:megnin
ID: 41717533
Brian, I sure would like to learn to get SSRS to send email.  I can think of so many places where I could use that.  I'm constantly working on database reports and people are starting to want more automated reports and more hands off alerts like Qlemo's PowerShell solution.

Are there any other resources you'd recommend?  Like how to get SSRS configured to use SMTP mail?  Thanks!
0
 
LVL 1

Author Comment

by:megnin
ID: 41717563
Qlemo, what's the simplest way to send a "hard coded" email to my boss daily just so he can have a warm fuzzy that the scheduled task it working every day?  I tried to figure out where to stick that, but I don't know where to put it where it would fire every day without breaking the current functionality.

Maybe use the existing message and if no contracts are expiring, still send the message, but to only his email address.  I don't care if the text is simply missing a company and days till expiration if there are none.

Worse case I'll just set up a separate script and task to run every day, so he knows the server and task(s) are functioning.
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 41717577
Just add another Send-MailMessage to the very end of the script, line 20+. That will run unconditionally everytime the task is run, and only if no error lead to terminating the script. Since all work is done at that moment, you cannot break anything (unless there is a severe syntax error).
0
 
LVL 1

Author Comment

by:megnin
ID: 41717586
Perfect!  I can't thank you enough.  I'm already thinking of other applications I have where I have emails sent only when triggered by the form submission, but really want them sent on a more regular basis.

Is it okay if I wait a bit for Brian to respond before I close this question out?  Not more than a day.  I probably should go ahead and close this out and make a new one for any SSRS questions.
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 41717702
No issue with waiting. But if there is too much detail required for getting SSRS "on the road", ask a new, specific question.
0
 
LVL 1

Author Closing Comment

by:megnin
ID: 41717746
Thank you again Qlemo.  It works great and I'll be able to customize variations of it for other projects as well.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

786 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