Getting no response from SQL stored procedure in PowerShell code

PowerShell Help
1

Hi folks. I’ve successfully written up (with Google search help) the sql code to execute a stored procedure in Powershell. It is successfully executing the stored procedure but what is failing is my ability to view the responses (results) after the stored procedure is executed. I added the line “return $dataset.Tables” but nothing is returned. When i check on the SQL Server Management Studio side, i see that the Powershell code has successfully executed the stored procedure. What am i doing wrong that is causing the response not to be dispalyed? Please let me know. Thanks for your help again.

 

$csv = Import-Csv ‘C:\stuff\list.csv’

$items = $csv | Select-Object -ExpandProperty Items

function Run-SQLcode {

param ($item)

$SQLconn = New-Object System.Data.SqlClient.SqlConnection(“Data Source=MyServer;Database=CoolStuff;Integrated Security=True”)
$SQLconn.Open()
$SQLcmd = $SQLconn.CreateCommand()
$SQLcmd.CommandText = “dbo.storedProcedure ‘$item’”
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter($SQLcmd)
$dataset = New-Object System.Data.DataSet
[void]$adapter.Fill($dataset)
return $dataset.Tables
$dataset.tables[0]
$SQLconn.Close()

}

foreach($item in $items){
Run-SQLcode $item
}

2

I query whether using Invoke-SqlCmd might be simpler and easier. However, either way, you def need to stipulate “exec”

Invoke-Sqlcmd -ServerInstance $SqlServerName -Database $DbName -Query "exec dbo.storedProcedure '$item'"

Going the way you were, you need to pass the parameter value differently:

$Item = "yourevariablevalue"

$SQLcmd.CommandText = "Exec dbo.storedProcedure @item"

$SQLcmd.Parameters.AddWithValue("@Item", $Item) | Out-Null
3

Thank you Iain. I ran the code below with your recommendation. The stored procedure is successfully executed on the SQL side (verified via SSMS) but on the Powershell side, I still do not get any response results displayed. Below is what i ran with your code changes. Not sure what else i’m missing?

 

[pre] $csv = Import-Csv ‘C:\stuff\list.csv’

$items = $csv | Select-Object -ExpandProperty Items

function Run-SQLcode {

param ($item)

$SQLconn = New-Object System.Data.SqlClient.SqlConnection(“Data Source=MyServer;Database=CoolStuff;Integrated Security=True”)
$SQLconn.Open()
$SQLcmd = $SQLconn.CreateCommand()
$SQLcmd.CommandText = “EXEC dbo.storedProcedure @item”

$SQLcmd.Parameters.AddwithValue(“@item”, $item) | Out-Null
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter($SQLcmd)
$dataset = New-Object System.Data.DataSet
[void]$adapter.Fill($dataset)
return $dataset.Tables
$dataset.tables[0]
$SQLconn.Close()

}

foreach($item in $items){
Run-SQLcode $item
} [/pre]