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
54 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 25

Expert Comment

by:Shaun Kline
Comment Utility
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 68

Expert Comment

by:Qlemo
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

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

Expert Comment

by:Shaun Kline
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 68

Accepted Solution

by:
Qlemo earned 500 total points
Comment Utility
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
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 68

Expert Comment

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

Expert Comment

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

Expert Comment

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

Expert Comment

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

743 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

14 Experts available now in Live!

Get 1:1 Help Now