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.
ZackGeneral IT Goto GuyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jason CrawfordTransport NinjaCommented:
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

0
ZackGeneral IT Goto GuyAuthor Commented:
Hi Jason,

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

Thank you.
Getcallcosts-Stored-Proc-Parameters.PNG
0
Jason CrawfordTransport NinjaCommented:
Have you tried expandable strings (" ") instead of literal strings (' ') for the $sqlcmd variable?
0
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

ZackGeneral IT Goto GuyAuthor 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.
0
Jason CrawfordTransport NinjaCommented:
have you inserted a breakpoint before that code block and attempted to debug it?
0
ZackGeneral IT Goto GuyAuthor Commented:
Hi Jason,

Yes couldn't find anything wrong hence the post.

Thank you.
0
ZackGeneral IT Goto GuyAuthor Commented:
Hi Jason,

Debug mode pic attached.

Thank you
Debug-Mode-Pic.PNG
0
ZackGeneral IT Goto GuyAuthor 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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZackGeneral IT Goto GuyAuthor Commented:
I found the solution to my problem
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.