Database mail

Question

Post reply

Database mail

SQL Galaxy

One Orange Chip

Points: 29472
More actions
July 21, 2009 at 3:09 am

#212640

Hi friends,
I have configured successfully database backup alert through database mail, it was working fine.
I need one urgent help from you, Could you send me details about how to setup email alert when sql server and agent services stop.
thanks advance

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lempster SSCoach Points: 15651 More actions · Answer 1

You could achieve want you want using a Powershell script executed via a Scheduled Task. Have a look at this post:

http://www.sqlteam.com/article/monitoring-sql-server-agent-with-powershell

Regards

Lempster

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

Lempster Thanks for your reply ...

I achived for automate email alert when sql services stopped. but could not able send email mulitple emailid, i tried below script but failed.

plz can anyone help, regds how to call multiple (cc) email in this powershell script ?

$servers=get-content "D:\services\servers.txt"

foreach($server in $servers)

{

# go to each server and return the name and state of services

# that are like "SQLAgent" and where their state is stopped

# return the output as a string

$body=get-wmiobject win32_service -computername $server |

select name,state |

where {($_.name -like "MSSQLSERVER" -or $_.name -like "SQLSERVERAGENT*") `

-and $_.state -match "Stopped"} |

Out-String

if ($body.Length -gt 0)

{

#Create a .net mail client

$smtp = new-object Net.Mail.SmtpClient("10.4.54.22")

$subject="Microsoft SQL Service & SQL Agent Service is down on " + $server

$msg.to.add("user@dom.com,user1@dom.com")

$smtp.Send("abc@powertest.COM", "ananda.murugesan@ril.com", $subject,$body,$msg.to.add)

"message sent"

}

Error:

You cannot call a method on a null-valued expression.

At D:\services\servicescheck1.ps1:21 char:12

+ $msg.to.add <<<< ("user@dom.com,user1@dom.com")

message sent

Thanks.

Lempster SSCoach Points: 15651 More actions · Answer 3

If you have a list of recipients in a CSV file, this should work:

$recpts = import-csv

if ($body.Length -gt 0)

{

#Create a .net mail client

$smtp = new-object Net.Mail.SmtpClient("10.4.54.22")

$subject="Microsoft SQL Service & SQL Agent Service is down on " + $server

$from="abc@powertest.com"

foreach ($recpt in $recpts){

$to = $recpt.email

$msg = New-Object system.net.mail.mailmessage

$msg.From = $from

$msg.To.add($to)

$msg.Subject = $subject

$msg.Body = $body

$smtp.Send($msg)

}

Regards

Lempster

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 4

Hi , Thanks for reply...

I could try above script, but my server there is no .csv (MSoffice not installed) insted of csv file, i used txt file for emaillist. but this is not hope below script.

$servers=get-content "D:\services\servers.txt"

foreach($server in $servers)

{

# go to each server and return the name and state of services

# that are like "SQLAgent" and where their state is stopped

# return the output as a string

$body=get-wmiobject win32_service -computername $server |

select name,state |

where {($_.name -like "MSSQLSERVER" -or $_.name -like "SQLSERVERAGENT*") `

-and $_.state -match "Stopped"} |

Out-String

$recpts = import-txt (d:\services\EmailList.txt)

if ($body.Length -gt 0)

{

#Create a .net mail client

$smtp = new-object Net.Mail.SmtpClient("10.4.54.22")

$subject="Microsoft SQL Service & SQL Agent Service is down on " + $server

$from="JerpCoker@ril.com"

foreach ($recpt in $recpts) {

$to = $recpt.email

$msg = New-Object system.net.mail.mailmessage

$msg.From = $from

$msg.To.add($to)

$msg.Subject = $subject

$msg.Body = $body

$smtp.Send($msg)

}

Error:

PS D:\services> .\servicescheck1.ps1

The term 'import-txt' is not recognized as a cmdlet, function, operable program, or script file. Veriy again.

At D:\services\servicescheck1.ps1:16 char:21

+ $recpts = import-txt <<<< (d:\services\EmailList.txt)

Thanks.

Lempster SSCoach Points: 15651 More actions · Answer 5

The error is telling you that import-txt is not a Powershell cmdlet....which it isn't. To read contents from a .txt file use this command:

$recpts = get-content "d:\services\EmailList.txt"

Make sure that each recipient is on a separate line in the .txt file.

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 6

Thanks for your valuable reply

Finally, this script was not working.

I got the errors below

PS D:\services> .\servicescheck1.ps1

Exception calling "Add" with "1" argument(s): "The parameter 'addresses' cannot be an empty string.

Parameter name: addresses"

At D:\services\servicescheck1.ps1:27 char:12

+ $msg.To.add <<<< ($to)

Exception calling "Send" with "1" argument(s): "A recipient must be specified."

At D:\services\servicescheck1.ps1:30 char:11

+ $smtp.Send <<<

---------

But i can get results when execute the following command

PS D:\services> get-content "D:\services\emaillist.txt"

ananda.murugesan@ril.com

jayesh.Karia@ril.com

server.Admin.Jamnagar@ril.com

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 7

Yes..

I got the result,

Insted of $msg.To.add($to)

i changed the script like

$msg.To.add(email1@test.com)

$msg.To.add(email2@test.com)

$msg.To.add(email3@test.com)

Thanks

Lempster SSCoach Points: 15651 More actions · Answer 8

It looks like the 'To' parameter need to be set explicitly, but you should be able to get a 'cc' list from a text file, e.g.

foreach ($recpt in $recpts) {

$to = "user@domain.co.uk"

$cc = $recpt

$msg = New-Object system.net.mail.mailmessage

$msg.From = $from

$msg.to.add($to)

$msg.cc.add($cc)

$msg.Subject = $subject

$msg.Body = $body

$smtp.Send($msg)

Regards

Lempster

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 9

Hi, Lempster thank you very much for your help....

This script CC copy was working fine.

Regards

Ananda.