May 11, 2016 at 11:54 pm
Typed in services.msc
Select SQLAgent->Properties->Recovery->
Under First Failure select - >Run a Program
Under program ->Path to the powershell executable
Under command ->
-Command 'Send-MailMessage -To abcd@abcd.org -Subject \"SQLAgent on ABCDSERVER Stopped\" -Body \"Please check\" -SmtpServer smtp.abcd.org -From EFGHIJ@defg.org'
I am trying to setup autoalerting in case the sqlagent stops.The above process is not working.
Can anybody please help find the issue.
Thanks
May 12, 2016 at 12:26 am
In what way is it not working? Silently? Error message? etc.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
May 12, 2016 at 8:18 am
I do not get any email alert
May 12, 2016 at 11:21 pm
Send-MailMessage –From abcdef@abcd.org –To edft@gsdg.org –Subject “Test Email” –Body “Powershell Email Testing” –SmtpServer smtp.abcd.org
When I run the above command from the powershell window it sends out an email,but I when I use the same command to get an email upon SQLAgent stoppage I do not get the email.
services.msc
under recovery option
under first failure:run program
under program :powershell path
under command line parameters :Send-MailMessage –From abcdef@abcd.org –To edft@gsdg.org –Subject "Test Email" –Body "Powershell Email Testing" –SmtpServer smtp.abcd.org
Can anybody please help fix this.
Thanks
May 13, 2016 at 1:21 am
Have you checked that the command runs properly from SQL Agent?
Have you used another method to test that the alert script is run?
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
May 13, 2016 at 3:07 am
It does not run from SQLAgent.
Message
Executed as user: The error information returned by PowerShell is: 'The term 'Send-MailMessage' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. '. Process Exit Code -1. The step failed.
May 13, 2016 at 3:53 am
sqlnewbie17 (5/13/2016)
It does not run from SQLAgent.Message
Executed as user: The error information returned by PowerShell is: 'The term 'Send-MailMessage' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. '. Process Exit Code -1. The step failed.
Have you tried System.Net.Mail? SQL Server's POSH support lags a bit behind the OS support, and if your version of SQL Server's using POSH v1, Send-MailMessage is not supported until v2 - so while you may be running v2 on your box, SQL Server may well be using a previous version internally as I understand it. Worth a try anyway.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
May 16, 2016 at 11:34 pm
powershell.exe -noprofile -command Send-MailMessage –From asfdsa@gdfgs.org –To abcd@abcd.org –Subject "Test Email" –Body "Powershell Email Testing" –SmtpServer smtp.abcd.org
The above code runs as part of SQL job but does not generate any email alert.
It fails when I run from powershell.
Error:
Send-MailMessage : A positional parameter cannot be found that accepts argument 'Email'.
At line:1 char:17
+ Send-MailMessage <<<< -From adsas@.org -To sadfsf@fsdfa.org -Subject Test Email -Body Powershell Ema
il Testing -SmtpServer smtp.sdffds.org
+ CategoryInfo : InvalidArgument: (:) [Send-MailMessage], ParameterBindingException
+ FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.PowerShell.Commands.SendMailMessage
Send-MailMessage –From sddfgd@dsgsdg.org –To sdasd@fasdfa.org –Subject "Test Email" –Body "Powershell Email Testing" –SmtpServer smtp.sdfsf.org
The above code generates an email from powershell but fails to run as part of SQL job.
When I run this as part of SQLAgent service it fails.
services.msc
under recovery option
under first failure:run program
under program :powershell path
under command line parameters :
Send-MailMessage –From abcdef@abcd.org –To edft@gsdg.org –Subject "Test Email" –Body "Powershell Email Testing" –SmtpServer smtp.abcd.org
I really need to set up a SQL Agent failure email alert using powershell.
Looks like I am missing to include some email commandlets.
Advise is appreciated.
Thanks
May 17, 2016 at 3:29 am
#variables
$emailFrom = "asdfAlerts@fghj.org"
$emailTo = "abcdef@fghj.org"
$subject = "Powershell Function calling an SMTP server"
$body = "Send an email through SMTP in Powershell"
$smtpServer = "smtp.fghj.org"
#create a function
Function sendEmail([string]$emailFrom, [string]$emailTo, [string]$subject,[string]$body,[string]$smtpServer)
{
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($emailFrom,$emailTo,$subject,$body)
}
sendEmail $emailFrom $emailTo $subject $body $smtpServer
The code is saved as email.ps1 under C:\PS\email.ps1
The above code generates an email alert when executed in both powershell or as part of sqljob.
But it does not give an email alert when the code is part of the Recovery tab in SQLServer agent service
under command line parameters option.
First failure:run a program
program:powershell path
Command line parameters:-Command C:\PSS\email.ps1.
May 17, 2016 at 3:51 pm
What is the execution policy on the machine you are trying to run this on set to?
Additionally, if you are going to call the powershell program to run a script, you want to specify the -file parameter, not -command. The -command parameter would be if you were trying to pass the powershell command(s) directly when calling powershell and not through a script. It looks like earlier on this thread you tried doing that, but if you do try and do that the entire command needs to be wrapped in quotes, otherwise you get errors because of spaces.
Joie Andrew
"Since 1982"
May 18, 2016 at 11:19 pm
I used file instead of command.
Command line parameters:-file C:\PS\email.ps1
It did not send out an email alert upon stopping the SQLAgent.
May 19, 2016 at 12:05 am
Try wrapping the powershell script in a batch file and calling that instead. In the batch file redirect output to a file and review the file after the script was supposed to have run when the agent stops. It is possible that the script is failing when running as a script for some reason you did not expect yet works when you run it manually. Grabbing the PowerShell output could help here because you would get to see if any errors are thrown back.
Joie Andrew
"Since 1982"
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply