Avatar of Zack
Zack
Flag for Australia asked on

Powershell - Stored procedure parameter when parsed isn't being recognized.

Hi EE,

I have the following script:

$constr = "User Id=$oracleuserId;Password=$oraclepassword;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$oraclehost2)(PORT=$oracleport))(CONNECT_DATA=(SERVICE_NAME=$oracleserviceName)))"




$fin = "'4239948'"
$conn= New-Object Oracle.DataAccess.Client.OracleConnection($constr)
$conn.Open()
$sql="select ph.extension Extension, to_char(elh.beg_effective_dt_tm,'yyyy/mm/dd hh24:mi:ss') StartDate
,to_char(elh.end_effective_dt_tm,'yyyy/mm/dd hh24:mi:ss') EndDate
from encntr_loc_hist elh, phone ph, encntr_alias ea
WHERE elh.encntr_id = ea.encntr_id
and ea.alias = $fin
and ea.alias_pool_cd = 11662
and elh.loc_facility_cd = 11664
and elh.active_ind=1
and elh.loc_bed_cd = ph.parent_entity_id
and ph.extension(+) is not null
and ph.parent_entity_name(+) = 'LOCATION'
and ph.phone_type_cd(+) = 160
and ph.end_effective_dt_tm(+) >= elh.end_effective_dt_tm
and ph.beg_effective_dt_tm(+) <= elh.beg_effective_dt_tm"


Write-Host $sql


$command = New-Object Oracle.DataAccess.Client.OracleCommand($sql,$conn)
$reader=$command.ExecuteReader()

$Results = @()

While ($reader.Read())
{

Write-Host 'Reading from reader1' -fore Yellow
Write-Host $temp
$temp = ''
$temp += $( $reader.GetString(0)) + ', '
$temp += $( $reader.GetString(1)) + ', '
$temp += $($reader.GetString(2))
$Results += $temp
}

Write-Host "Results: $Results"

$phextension = ($Results | ForEach-Object {
$_.Split(',')[0]
}) -join ','


$phextstartdate = ($Results | ForEach-Object {
$_.Split(',')[1]
}) -join ','


$phextfinshdate = ($Results | ForEach-Object {
$_.Split(',')[2]
}) -join ','

Write-Host "phextension: $phextension"

Write-Host "phextstartdate: $phextstartdate"

Write-Host "phextfinshdate: $phextfinshdate"

$conn.Close()

$Results | foreach {

  $phextension = $_.Split(',')[0]

  $phextstartdate = $_.Split(',')[1]

  $phextfinshdate = $_.Split(',')[2]

  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
  $SqlConnection.ConnectionString = "Server = $sqlhost2; Database = $SQLdb; Integrated Security = True; User ID = $sqluserId; Password = $sqlpassword;"
  $sqlconnection.Open()
  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
  $SqlCmd.CommandText = 'GetCallCosts'
  $param1=$SqlCmd.Parameters.Add("@Extension" , [System.Data.SqlDbType]::VarChar)
  $param1.Value = $phextension
  $param2=$SqlCmd.Parameters.Add("@StartDate" , [System.Data.SqlDbType]::VarChar)
  $param2.Value = $phextstartdate
  $param3=$SqlCmd.Parameters.Add("@EndDate" , [System.Data.SqlDbType]::VarChar)
  $param3.Value = $phextfinshdate


  $SqlCmd.Connection = $SqlConnection
  $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
  $SqlAdapter.SelectCommand = $SqlCmd
  $DataSet = New-Object System.Data.DataSet
  $SqlAdapter.Fill($DataSet)
  $sqlconnection.Close()

  $DataSet.Tables[0]
  Write-Host '-----'




}

Open in new window


Values I am parsing are the below:

phextension: 6766,6735,6724,6747,6731,6731
phextstartdate:  2017/02/06 18:37:00, 2017/02/27 14:39:00, 2017/02/27 17:23:00, 2017/03/08 12:26:00, 2017/03/15 13:46:00
, 2017/03/21 11:37:00
phextfinshdate:  2017/02/16 17:34:00, 2017/02/27 17:23:00, 2017/02/28 00:30:00, 2017/03/15 13:46:00, 2017/03/21 11:37:00
, 2017/03/21 11:37:00


The issue is that i'm getting the error:

Exception calling "Fill" with "1" argument(s): "Procedure or function 'GetCallCosts' expects parameter '@Extension',
which was not supplied."
At C:\psscripts\Query4.ps1:108 char:3
+   $SqlAdapter.Fill($DataSet)
+   ~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException


I don't understand why this error is occurring any assistance is welcome.

Thank you.
Powershell

Avatar of undefined
Last Comment
Zack

8/22/2022 - Mon
Jason Crawford

Try this:

$constr = "User Id=$oracleuserId;Password=$oraclepassword;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$oraclehost2)(PORT=$oracleport))(CONNECT_DATA=(SERVICE_NAME=$oracleserviceName)))"




$fin = "'4239948'"
$conn = New-Object -TypeName Oracle.DataAccess.Client.OracleConnection -ArgumentList ($constr)
$conn.Open()
$sql = "select ph.extension Extension, to_char(elh.beg_effective_dt_tm,'yyyy/mm/dd hh24:mi:ss') StartDate
  ,to_char(elh.end_effective_dt_tm,'yyyy/mm/dd hh24:mi:ss') EndDate
  from encntr_loc_hist elh, phone ph, encntr_alias ea
  WHERE elh.encntr_id = ea.encntr_id
  and ea.alias = $fin
  and ea.alias_pool_cd = 11662
  and elh.loc_facility_cd = 11664
  and elh.active_ind=1
  and elh.loc_bed_cd = ph.parent_entity_id
  and ph.extension(+) is not null
  and ph.parent_entity_name(+) = 'LOCATION'
  and ph.phone_type_cd(+) = 160
  and ph.end_effective_dt_tm(+) >= elh.end_effective_dt_tm
and ph.beg_effective_dt_tm(+) <= elh.beg_effective_dt_tm"


Write-Host -Object $sql


$command = New-Object -TypeName Oracle.DataAccess.Client.OracleCommand -ArgumentList ($sql, $conn)
$reader = $command.ExecuteReader()

$Results = @()

While ($reader.Read())
{
  Write-Host -Object 'Reading from reader1' -ForegroundColor Yellow
  Write-Host -Object $temp
  $temp = ''
  $temp += $( $reader.GetString(0)) + ', '
  $temp += $( $reader.GetString(1)) + ', '
  $temp += $($reader.GetString(2))
  $Results += $temp
}

Write-Host -Object "Results: $Results"

$phextension = ($Results | ForEach-Object -Process {$_.Split(',')[0]}) -join ','


$phextstartdate = ($Results | ForEach-Object -Process {$_.Split(',')[1]}) -join ','


$phextfinshdate = ($Results | ForEach-Object -Process {$_.Split(',')[2]}) -join ','

Write-Host -Object "phextension: $phextension"

Write-Host -Object "phextstartdate: $phextstartdate"

Write-Host -Object "phextfinshdate: $phextfinshdate"

$conn.Close()

$Results | ForEach-Object -Process {
  $phextension = $_.Split(',')[0]

  $phextstartdate = $_.Split(',')[1]

  $phextfinshdate = $_.Split(',')[2]

  $SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection
  $SqlConnection.ConnectionString = "Server = $sqlhost2; Database = $SQLdb; Integrated Security = True; User ID = $sqluserId; Password = $sqlpassword;"
  $SqlConnection.Open()
  $SqlCmd = New-Object -TypeName System.Data.SqlClient.SqlCommand
  $SqlCmd.CommandText = 'GetCallCosts'
  $param1 = $SqlCmd.Parameters.Add('@Extension' , [Data.SqlDbType]::VarChar)
  $param1.Value = $phextension
  $param2 = $SqlCmd.Parameters.Add('@StartDate' , [Data.SqlDbType]::VarChar)
  $param2.Value = $phextstartdate
  $param3 = $SqlCmd.Parameters.Add('@EndDate' , [Data.SqlDbType]::VarChar)
  $param3.Value = $phextfinshdate


  $SqlCmd.Connection = $SqlConnection
  $SqlAdapter = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter
  $SqlAdapter.SelectCommand = $SqlCmd
  $DataSet = New-Object -TypeName System.Data.DataSet
  $SqlAdapter.Fill($DataSet)
  $SqlConnection.Close()

  $DataSet.Tables[0]
  Write-Host -Object '-----'
}

Open in new window

Zack

ASKER
Hi Jason,

Same error attached are the parameter names from the stored procedure.

Thank you.
Getcallcosts-Stored-Proc-Parameters.PNG
Jason Crawford

Have you tried expandable strings (" ") instead of literal strings (' ') for the $sqlcmd variable?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Zack

ASKER
Hi Jason,

Yes tried that:

  $SqlCmd.CommandText = "GetCallCosts"
  $param1 = $SqlCmd.Parameters.Add("@Extension" , [Data.SqlDbType]::VarChar)
  $param1.Value = $phextension
  $param2 = $SqlCmd.Parameters.Add("@StartDate" , [Data.SqlDbType]::VarChar)
  $param2.Value = $phextstartdate
  $param3 = $SqlCmd.Parameters.Add("@EndDate" , [Data.SqlDbType]::VarChar)
  $param3.Value = $phextfinshdate

Open in new window


Thank you.
Jason Crawford

have you inserted a breakpoint before that code block and attempted to debug it?
Zack

ASKER
Hi Jason,

Yes couldn't find anything wrong hence the post.

Thank you.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Zack

ASKER
Hi Jason,

Debug mode pic attached.

Thank you
Debug-Mode-Pic.PNG
ASKER CERTIFIED SOLUTION
Zack

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Zack

ASKER
I found the solution to my problem