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

Zack
Zack used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

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

Commented:
Have you tried expandable strings (" ") instead of literal strings (' ') for the $sqlcmd variable?
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

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.

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:
Hi EE,

Found my solution:

$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]
Write-Host -Object "phextension:$phextension"
$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"
$SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
$SqlCmd.Parameters.AddWithValue("@Extension", $phextension)  | Out-Null
$SqlCmd.Parameters.AddWithValue("@StartDate", $phextstartdate) | Out-Null
$SqlCmd.Parameters.AddWithValue("@EndDate", $phextfinshdate) | Out-Null




$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

Write-Host -Object $SqlCmd
Write-Host -Object $SqlAdapter.SelectCommand
$DataSet = New-Object -TypeName System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0] | export-csv "c:\temp\$($phextension)_data.csv"

Write-Host -Object '-----'

}

Open in new window


Thank you
ZackGeneral IT Goto Guy

Author

Commented:
I found the solution to my problem

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial