-
Notifications
You must be signed in to change notification settings - Fork 0
/
Test-SQLConnection.ps1
44 lines (35 loc) · 1.19 KB
/
Test-SQLConnection.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
# Sending an e-mail when connection to SQL Server is lost
function Test-SQLConnection {
Try
{
# Cycling test connection to SQL Server
While(1 -eq 1)
{
# SQLcmd command connecting to Server Instance
# Insert full instance name in SERVERNAME\INSTANCENAME
Invoke-Sqlcmd -ServerInstance "SERVERNAME\INSTANCENAME" -Query "DECLARE @ConnectionTest int;"
# Wait 5 seconds
sleep 5
}
}
Catch
{
# Sending an email
# Fill with SMTP Parameters
$smtpServer = “SMTPSERVER”
$smtpFrom = “SENDERADDRESS@DOMAIN.COM”
$smtpTo = “RECIPIENTADDRESS@DOMAIN.COM”
$smtpSubject = “SUBJECT”
$username = “CONNECTIONUSERNAME”
$password = “CONNECTIONPASSWORD”
$body = “BODY”
# Connect to $smtpserver
$smtp = New-Object -TypeName “Net.Mail.SmtpClient” -ArgumentList $smtpServer
# Use network credentials
$smtp.Credentials = New-Object system.net.networkcredential($username, $Password);
# Compose the body with Get-Date and $body
$smtpBody = “[$(Get-Date -Format HH:mm:ss)] $body”
# Send the email
$smtp.Send($smtpFrom, $smtpTo, $smtpSubject, $smtpBody)
}
}