• Status: Solved
  • Priority: High
  • Security: Private
  • Views: 31
  • Last Modified:

Oschestrator Powershell script problem

Dear expert

Below script code works fine i powershell, Im trying to add this in the orchestrator(A SCCM add-in) I got this error:

Cannot bind parameter 'Date' to the target. Exception setting "Date": "Object reference not set to an instance of an object."
Seems it complain this: $dateFilter = get-date $dateFilter -format yyyy-MM-dd, I kinda knew that SSCM Orchestrator use the old PS version, now I dont know what to do to fix this problem. Well the function Im after is: when you run the script till well first get running date with format yyyy-MM-dd and then use that line into a sql query.

Anyone know the solution for it? Thanks.

$SQLServer = "System"
$SQLDBName = "Prod"
$months = 2
$dateFilter = get-date $dateFilter -format yyyy-MM-dd

function f_sqlquery ($dateFilter) {

      $SqlQuery = "
      DECLARE
            @ReminderMonth date = '$dateFilter';
      DECLARE
            @P1 int = (      SELECT      COUNT(*)
                              FROM        Invoice.Reminder R
                              WHERE       R.DatetimeSentToReceiver >= @ReminderMonth
                              AND R.DatetimeSentToReceiver < DATEADD(MONTH, 1, @ReminderMonth)
                              AND R.ReminderNumber = 1 ),
            @P2 int = (      SELECT      COUNT(*)
                              FROM        Invoice.Reminder R
                              WHERE       R.DatetimeSentToReceiver >= @ReminderMonth
                              AND R.DatetimeSentToReceiver < DATEADD(MONTH, 1, @ReminderMonth)
                              AND R.ReminderNumber = 2 ),
            @P3 int = (      SELECT      COUNT(*)
                              FROM        Invoice.Reminder R
                              WHERE       R.DatetimeSentToReceiver >= @ReminderMonth
                              AND R.DatetimeSentToReceiver < DATEADD(MONTH, 1, @ReminderMonth)
                              AND R.ReminderNumber = 3 );
      
      select CONVERT(varchar(7),@ReminderMonth,120) AS 'Month' , @P1 AS 'Reminder 1', @P2 AS 'Reminder 2', @p3 AS 'Reminder 3', SUM(@P1+@P2+@P3) AS 'Reminder total'

      "
      
      
      return $DataSet.Tables[0].Rows
}


$sqlArr = @()

if ($months -gt 1) {
      $months--
      0..$months | % {
            $dateQuery = ( get-date (get-date $dateFilter).AddMonths( $_ ) )
            if ( (get-date $dateQuery) -le (get-date) ) {
                  $sqlArr += f_sqlquery $dateQuery $SQLDBName
            }
      }
} else {
      $sqlArr += f_sqlquery $dateFilter $SQLDBName
}
0
WeTi
Asked:
WeTi
1 Solution
 
oBdACommented:
That's got nothing to do with the PowerShell version; they way you're setting the variable is incorrect. You need to remove the "$dateFilter" (which is an empty variable at this point) from the line it complains about:
$dateFilter = Get-Date -Format yyyy-MM-dd

Open in new window

0
 
WeTiAuthor Commented:
right
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now