April 3, 2018 at 6:04 am
I'm testing a PowerShell Script to send me an email anytime SQL Server Agent stops/fails to start. I'm using the command from this site:
http://sqlish.com/alert-when-sql-server-agent-service-stops-or-fails/
I stopped the SQL Server Agent in services, and I didn't get an email. I should have gotten an email alert as soon as the agent stopped.I know the Database Mail is working because I tested that and got an email notification. I checked in SSMS and I can see the SQL Server Agent has a brown down arrow as opposed to a red "X". Do I also need to stop SQL Server too to trigger the alert?
Thanks.
April 3, 2018 at 6:27 am
Actually, I did try stopping SQL Server, and that didn't trigger the alert. Here is my PowerShell Command I used in Service > SQL Server Agent > Recovery > Command Line:
-Command ‘Send-MailMessage -To <email address removed> -Subject \”<server name removed>:SQL Agent Stopped\” -Body \”Please look into the issue; Scheduled Jobs will not run if the SQL Server Agent Service remains stopped.\” -SmtpServer <smtp server removed> -From <email address removed> ’
April 3, 2018 at 7:56 am
WC_Admin - Tuesday, April 3, 2018 6:27 AMActually, I did try stopping SQL Server, and that didn't trigger the alert. Here is my PowerShell Command I used in Service > SQL Server Agent > Recovery > Command Line:-Command ‘Send-MailMessage -To <email address removed> -Subject \â€<server name removed>:SQL Agent Stopped\†-Body \â€Please look into the issue; Scheduled Jobs will not run if the SQL Server Agent Service remains stopped.\†-SmtpServer <smtp server removed> -From <email address removed> ’
Send-MailMessage doesn't use Database Mail. If you haven't set the smtp server or there is no value for $PSE-mailServer, it won't know the server. Or it can also be a credential issue.
Try testing from Powershell alone with just sending a message. You may need to set the smtp server or credentials - test it from a Powershell console or ISE.
Send-MailMessage
Sue
April 4, 2018 at 8:26 am
Sue_H - Tuesday, April 3, 2018 7:56 AMWC_Admin - Tuesday, April 3, 2018 6:27 AMActually, I did try stopping SQL Server, and that didn't trigger the alert. Here is my PowerShell Command I used in Service > SQL Server Agent > Recovery > Command Line:-Command ‘Send-MailMessage -To <email address removed> -Subject \â€<server name removed>:SQL Agent Stopped\†-Body \â€Please look into the issue; Scheduled Jobs will not run if the SQL Server Agent Service remains stopped.\†-SmtpServer <smtp server removed> -From <email address removed> ’
Send-MailMessage doesn't use Database Mail. If you haven't set the smtp server or there is no value for $PSE-mailServer, it won't know the server. Or it can also be a credential issue.
Try testing from Powershell alone with just sending a message. You may need to set the smtp server or credentials - test it from a Powershell console or ISE.
Send-MailMessageSue
Ok I had to let PowerSheel know what is my email server:
PS C:\Users\ $PSEmailServer = 'mail.mycompany.com'
I ran a test Send-MailMessage and it works when I ran this command in PowerShell:
PS C:\Users\ Send-MailMessage -To "myworkemail@email.com" -From "myotheremail@email.com"
-Subject "Testing email from PowerShell command" -body "Testing sending email from server via PowerShell command" -SmtpServer "myemailserver.name.com"
.
So, I went back into my test server > Services > SQL Server Agent > Recovery > Run a Program and pasted this command in the command line parameters:
-Command 'Send-MailMessage -To myworkemail@email.com -Subject \â€myservername:SQL Agent Stopped\†-Body \â€Please look into the issue; Scheduled Jobs will not run if the SQL Server Agent Service remains stopped.\†-SmtpServer myemailserver.name.com -From myotheremail@email.com’
I went into Services and stopped SQL Server Agent, and no email was sent. Is my command wrong? Do I need to need to stop the service in a different way?
April 4, 2018 at 11:10 am
WC_Admin - Wednesday, April 4, 2018 8:26 AMSue_H - Tuesday, April 3, 2018 7:56 AMWC_Admin - Tuesday, April 3, 2018 6:27 AMActually, I did try stopping SQL Server, and that didn't trigger the alert. Here is my PowerShell Command I used in Service > SQL Server Agent > Recovery > Command Line:-Command ‘Send-MailMessage -To <email address removed> -Subject \â€<server name removed>:SQL Agent Stopped\†-Body \â€Please look into the issue; Scheduled Jobs will not run if the SQL Server Agent Service remains stopped.\†-SmtpServer <smtp server removed> -From <email address removed> ’
Send-MailMessage doesn't use Database Mail. If you haven't set the smtp server or there is no value for $PSE-mailServer, it won't know the server. Or it can also be a credential issue.
Try testing from Powershell alone with just sending a message. You may need to set the smtp server or credentials - test it from a Powershell console or ISE.
Send-MailMessageSue
Ok I had to let PowerSheel know what is my email server:
PS C:\Users\ $PSEmailServer = 'mail.mycompany.com'I ran a test Send-MailMessage and it works when I ran this command in PowerShell:
PS C:\Users\ Send-MailMessage -To "myworkemail@email.com" -From "myotheremail2email.com"
-Subject "Testing email from PowerShell command" -body "Testing sending email from server via PowerShell command" -SmtpServer "myemailserver.name.com"
.
So, I went back into my test server > Services > SQL Server Agent > Recovery > Run a Program and pasted this command in the command line parameters:-Command 'Send-MailMessage -To myworkemail@email.com -Subject \â€myservername:SQL Agent Stopped\†-Body \â€Please look into the issue; Scheduled Jobs will not run if the SQL Server Agent Service remains stopped.\†-SmtpServer myemailserver.name.com -From myotheremail@email.com’
I went into Services and stopped SQL Server Agent, and no email was sent. Is my command wrong? Do I need to need to stop the service in a different way?
Pretty sure the issue is credentials. When run from the recovery options. it's likely running as the system account. You can test it by just running something in Powershell like:
whoami | out-file 'C:\temp\WhoIsRunning.txt'
How to manage that depends on what works for you and whatever company limitations,recommendations you may have. If you search on New-Object System.Management.Automation.PSCredential
you can find quite a few different ways, suggestions, examples.
Sue
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply