September 16, 2015 at 9:03 pm
I have a PS script that has three parameters: -ConfigurationGroup "$ConfigurationGroup" -PolicyCategoryFilter "$PolicyCategoryFilter" -EvalMode "$EvalMode".
Some of these values can have spaces in them hence the quotes around the params.
When I run this in PS ISE it completes successfully.
However when I then try and run it via a SQL Agent job either using Type: Powershell or Operating System (CmdExec) it fails with:
Cannot convert the "DBA - DB Config" value of type "System.String" to type "System.Management.Automation.ParameterAttribute".
It seems it can handle the spaces in ISE but not SQL Agent job.
Any ideas? Do I have to use a different syntax?
September 17, 2015 at 8:08 am
If your variable is passing in a string, meaning when I set the value of the variable I wrap it in single-quotes, then there is no need to put the variable in double-quotes while calling your script. When you wrap the variable in double-quotes you are forcing the type of the variable to be a string, that is what the error means. Remove the quotes and the script should run as is.
When you state you run in via ISE with no errors, are you running it the exact same way you call it from SQL Agent, or just running the script the SQL Agent job is calling?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 17, 2015 at 7:14 pm
These are the main bits of code I now have having removed the double quotes around the parameter assignments:
param(
[parameter] [string]$ConfigurationGroup,
[parameter] [String]$PolicyCategoryFilter,
[parameter] [string]$EvalMode
)
...
...
...
# Iterate through the EPM dataset
foreach ($row in $ds.tables["PolicyParameters"].rows)
{
$ConfigurationGroup = $row.ConfigurationGroup
$PolicyCategoryFilter = $row.PolicyCategoryFilter
$EvalMode = $row.EvalMode
SL "D:\EPM\PSScripts\"
.\PS.ps1 -ConfigurationGroup $ConfigurationGroup -PolicyCategoryFilter $PolicyCategoryFilter -EvalMode $EvalMode
This works when running through ISE but if I paste this same code into a SQL Agent job it fails with the error mentioned previously.
September 17, 2015 at 7:43 pm
Remove the "[parameter]" unless you are going to set any advanced parameter attributes. This is likely why the ParameterAttribute data type is being forced and errors.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 17, 2015 at 8:03 pm
Thanks very much Shawn that works!
Only thing now is that the SQL job never seems to complete even though the Powershell script has finished inserting records? Do I need to have some kind of exit at the end after the foreach loop?
September 17, 2015 at 8:09 pm
Well Tue code you pasted in is missing the closing bracket of the loop. If that is not in the script.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 17, 2015 at 8:12 pm
Sorry yes that is in my script - just missed copying it.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply