Powershell 2.0 e-mail when SQL Agent service stopps

Hi Experts,

I read this article which was really promising, but I am a N00B to Powershell, and I can't seem to troubleshoot my errors.

Essentially, I want a powershell script that runs (send me and e-mail notification)when any of my SQL services stop.

This website sounds like it is so easy, but after several hours of trying, I am not having any luck.

http://sqlish.com/alert-when-sql-server-agent-service-stops-or-fails/#comment-59
Any help would be appreciated

I get the following error when testing:
Missing expression after unary operator '-'.
At line:1 char:2
+ - <<<< Comandlet `Send-MailMessage

+ CategoryInfo          : ParserError: (-:String) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : MissingExpressionAfterOperator
Aaron GoodwinAsked:
Who is Participating?
 
SubsunCommented:
In PS window you just run C:\Automation\Service_down_notify.ps1 for testing..
0
 
apathy42Commented:
Check the syntax...it looks like you may have a space after one of your - signs.  You can also check syntax by taking everything within the single quotes and pasting it into a Powershell window, and see if it works.
0
 
Aaron GoodwinAuthor Commented:
Hi Apathy42,

Thanks for the reply.  As long as I drop the -command and the  single quotes ' '  surrounding the text, the message will send\work from the powershell command prompt.  So how do I get it to run from the windows service recovery is the problem.  if you can get it working with one of your services....anyservice let me know!  much like in the example from the website I was referring to.  I will also try removing spaces after the - signs.

Cheers
0
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
SubsunCommented:
Did you configured the service as shown in screenshot of article?
ServiceAlso please keep in mind that It will send mail in if the service is crashed (Failed)..
0
 
Aaron GoodwinAuthor Commented:
Hi SubSun, Yes I setup the recovery as illustrated in the article.  I was then trying to test the function by doing an end task from windows task manager to see if the alert would send out.  No alert came through so I've been trying to see if there were other ways I could test the command.  In the article it referred to stopping the service and starting again to test, but that didn't work for me.  Do you know of a way to accomplish what I am trying to do?  Just looking for an alert if the service is
0
 
Aaron GoodwinAuthor Commented:
Subsun,

Are you able to get an e-mail sent if you fail the agent service, using the methods described in the article?
0
 
SubsunCommented:
Okie.. I have removed some unwanted slash's. Copy below mentioned code to Command line parameters after replacing the following..

Replace suleman@sqlish.com with your TO address
<Mention ServerName> with your server name
<your mail Server> with your SMTP server name
suleman@sqlish.com with your From address
-Command ‘Send-MailMessage -To suleman@sqlish.com -Subject ”<Mention ServerName>:SQL Agent Stopped” -Body ”Please look into the issue; Scheduled Jobs will not run if the SQL Server Agent Service remains stopped.” -SmtpServer <your mail Server> -From suleman@sqlish.com'

Open in new window

0
 
SubsunCommented:
Yes It should work.. You have PowerShell on the server which you are trying setup this alert, right?
0
 
Aaron GoodwinAuthor Commented:
Hi Subsun,

Yes SQL server and Powershell  (Ver 2.0) are on the server.
Just a quick question.....where you said to replace <Mention ServerName>.  would I leave it as <awesomeservername> in the greater than and less than symbols....or remove those entirely? same with <SMTPSERVER>

cheers
0
 
Aaron GoodwinAuthor Commented:
also how do you test this, I've done an end task on the SQLAGENT.exe hoping that would trigger an alert, but nothing at this point.
0
 
SubsunCommented:
Remove <> .. If your mail server Smtp.Mail.com
then
.....-SmtpServer Smtp.Mail.com -From subsun@test.com........
0
 
SubsunCommented:
When you killed SQLAGENT.exe, Did it stop the service?
0
 
Aaron GoodwinAuthor Commented:
yes it stopped the service
0
 
SubsunCommented:
Run the following code from PowerShell and see if you get a mail (Of course you need to replace the attributes as I mentioned in my previous comment)

Send-MailMessage -To suleman@sqlish.com -Subject ”<Mention ServerName>:SQL Agent Stopped” -Body ”Please look into the issue; Scheduled Jobs will not run if the SQL Server Agent Service remains stopped.” -SmtpServer <your mail Server> -From suleman@sqlish.com

Open in new window

0
 
Aaron GoodwinAuthor Commented:
Yes that worked just fine from the PS admin console
0
 
SubsunCommented:
If you are getting mail using above command then it should work fine....Try entering this in Command line parameters..
-command "& '<Copy above mentioned command here>'"

Open in new window


Another option is to save the above command as a .ps1 file. Copy paste above command in to a txt file and save it as "Script.ps1"

Enter the script name (with path of the file) in Command line parameters as follows:..
-command "& 'C:\test\Script.ps1'"
0
 
Aaron GoodwinAuthor Commented:
tried your idea to save it as a file...same error result

Missing expression after unary operator '-'.
At line:1 char:2
+ - <<<< command
    + CategoryInfo          : ParserError: (-:String) [], Par
    + FullyQualifiedErrorId : MissingExpressionAfterOperator

Even if I just type -command all by itself that error message comes up.  It's like -command is not a recognized cmdlet or something

any ideas?  And thanks for the help too
0
 
SubsunCommented:
Are you adding  -command "& 'C:\test\Script.ps1'" to Command line parameters? If yes how you are getting error? can you post a screenshot?
0
 
Aaron GoodwinAuthor Commented:
Hi Subsun,

Yes I added the -command "& 'C:\Automation\Service_down_notify.ps1'" to the command line Parameters and killed the SQLAGENT.exe process with task manager to test.  No e-mail was sent....nothing happened.

The only way I am able to get errors that I have posted is by running commands in the PS window.  I've been trying to figure out if it is a syntax issue on my end, or if this just doesn't work on my system.  Have you been able to get notification using this method for any service yourself?
0
 
Aaron GoodwinAuthor Commented:
works fine running
& "C:\automation\Service_down_notify.ps1"
from the PS window
0
 
Aaron GoodwinAuthor Commented:
WHOOO HOOOO!!!

Got it!
got rid of the -command and left this in the Command Line Paramaters.
& "C:\Automation\Service_down_notify.ps1"
e-mail fired and the service restarted after killing SQLAGENT.exe in the task manager.  You rock!  Thanks so much for the help.
0
 
Aaron GoodwinAuthor Commented:
thanks for the help, works like a charm now
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.