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.

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?

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.

Zack

ASKER

Hi Jason,

Debug mode pic attached.

Thank you
Debug-Mode-Pic.PNG

ASKER CERTIFIED SOLUTION

Zack