We help IT Professionals succeed at work.

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

Zack
Zack asked
on
81 Views
Last Modified: 2017-04-11
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.
Comment
Watch Question

Jason CrawfordTransport Ninja
CERTIFIED EXPERT

Commented:
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

ZackGeneral IT Goto Guy

Author

Commented:
Hi Jason,

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

Thank you.
Getcallcosts-Stored-Proc-Parameters.PNG
Jason CrawfordTransport Ninja
CERTIFIED EXPERT

Commented:
Have you tried expandable strings (" ") instead of literal strings (' ') for the $sqlcmd variable?
ZackGeneral IT Goto Guy

Author

Commented:
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 CrawfordTransport Ninja
CERTIFIED EXPERT

Commented:
have you inserted a breakpoint before that code block and attempted to debug it?
ZackGeneral IT Goto Guy

Author

Commented:
Hi Jason,

Yes couldn't find anything wrong hence the post.

Thank you.
ZackGeneral IT Goto Guy

Author

Commented:
Hi Jason,

Debug mode pic attached.

Thank you
Debug-Mode-Pic.PNG
General IT Goto Guy
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
ZackGeneral IT Goto Guy

Author

Commented:
I found the solution to my problem