June 23, 2016 at 6:25 am
I have this piece of powershell code which sends out an email when executed in a SQL job or run in powershell mode but does not work in this below situation.
Attached is a screenshot of the Recovery tab under SQLAgent services.
I have run this command in powershell before stopping SQLAgent but I did not get any email alert that the SQLAgent has been stopped.
set-executionpolicy remotesigned
Can anyone help fix this?
Thanks
#variables
$emailFrom = "defg@kjid.org"
$emailTo = "abcd@def.org"
$subject = "Powershell Function calling an SMTP server"
$body = "Send an email through SMTP in Powershell"
$smtpServer = "smtp.abcdefgh.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
June 23, 2016 at 6:41 am
Have you tested this from PowerShell directly?
Have you run the SQLAgent task manually to ensure that it will work in the context of SQLAgent?
Have you tried writing a different SQLAgent task to ensure that you have the task launch under these conditions?
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
June 24, 2016 at 12:28 am
Have you tried writing a different SQLAgent task to ensure that you have the task launch under these conditions? <--- What do you mean by this ?
Yes to both powershell testing and SQLJob testing.
I get alerted on both the occasions.
June 25, 2016 at 2:12 am
In your settings for running the script you are specifying the -command parameter. Try changing it to the -file parameter and see if your result is any different.
Additionally, while your execution policy may be remotesigned, you might want to check if that is the same execution policy for the account running the SQL Agent service.
Joie Andrew
"Since 1982"
August 2, 2016 at 4:38 pm
Did you manage to resolve this sqlnewbie17?
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
August 30, 2016 at 3:19 pm
Hi,
User Joie Andrew is right, you are looking for the -File parameter instead of the -Command parameter when you are pointing to a script file. If you would like to use the -Command parameter, then you have to type the command(s) right in there using the curly brackets, and separating each command with a semicolon. e.g.
powershell.exe -Command {Write-Host "TEST" | Out-File -FilePath "C:\file.txt"; Start-Process -FilePath notepad.exe;}
Which version of PoSh do you have? Is it 3.0?
Starting from PowerShell 3.0 there is a cmdlet under the name of Send-MailMessage, if this is your case, you could ease your code by switching it with this cmdlet.
I would strongly suggest to use the -NoProfile and -NonInteractive in order to avoid loading custom scripts (profiles) and the -NonInteractive switch is used to do not open a PoSh window, in this case it will work as a background process.
As you might know, these parameters are temporary, they only last during the session, so after the session is closed, the configuration is gone too. If after retesting your issue with my previous suggestions did not help, I would ask you to additionally add to the start parameters the -ExecutionPolicy and set it with the value "Bypass", so we can be sure you are not having an execution policy misconfiguration (script origin[/url]).
For more about information about PowerShell start parameters, please visit this LINK
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply