SQL Job step calling powershell script and pass parameters

Question

Hi,
I have sql job step that calls power shell script by passing parameters like this:
$runDate = (get-date -f yyyy-MM-dd);
$toEmailAddress = "address1@Stuff .com,address2@Stuff .com"
Powershell.exe D:\sysapps\xxx\SQLJobs\sqljobname\reports.ps1 -pRunDate $runDate -pToEmailAddress $toEmailAddress

======================

reports.ps1 has code like this:
param(
[string]$pRunDate,
[string]$pToEmailAddress
)

================================ Email details ============================================

Sender and Recipient Info

$MailFrom = "it@Stuff .com"
$MailTo = $pToEmailAddress
$body = "Trade Reports as of <b><i>" + $pRunDate + "</i></b>"

Server Info

$SmtpServer = "xxxx"
$SmtpPort = "25"

Message stuff

$MessageSubject = "Reports For " + $pRunDate
$Message = New-Object System.Net.Mail.MailMessage $MailFrom,$MailTo
$Message.IsBodyHTML = $true
$Message.Subject = $MessageSubject
$Message.Body = $body

Construct the SMTP client object, credentials, and send

$Smtp = New-Object Net.Mail.SmtpClient($SmtpServer,$SmtpPort)
$Smtp.Send($Message)

This sends email to only first address. I added
Write-Host $pToEmailAddress to see what is the value of this variable. When it is passed from sql job step to powershell script, comma is getting removed between 2 addresses. How it can happen I do not know.
Please suggest how to pass multiple addresses from sql job step and how to use it in script.

SQL Job step is set with 'Powershell' as type.

Answer 1

Remove the explicit call to powershell.exe. You are already running in the context of PS so by specifying the EXE you're "shelling out" again which messes up the call.

$runDate = (get-date -f yyyy-MM-dd);
$toEmailAddress = "******@gmail.com,******@gmail.com"

D:\sysapps\xxx\SQLJobs\sqljobname\reports.ps1 -pRunDate $runDate -pToEmailAddress $toEmailAddress