Author Posts

January 1, 2012 at 12:00 am

by apriljuly at 2013-03-11 06:56:41

Hi,

I was trying to pass 2 parameters to my stored procedure using powershell.

My code is as below, when I run it in powershell there's no error. However, it doesn't seem the parameters has been passed, because I can't get the correct result. Is there anything wrong on below?

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=localhost;Database=AMSDataWarehouse Test;Integrated Security=SSPI"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection = $SqlConnection
$sqlcmd.CommandText = "YQBReport1"
$param1=$sqlcmd.Parameters.Add("@from" , [System.Data.SqlDbType]::DateTime)
$param1.Value = "2013-03-13"
$param2=$sqlcmd.Parameters.Add("@to" , [System.Data.SqlDbType]::DateTime)
$param2.Value = "2013-03-14"
$SqlConnection.Open()
$sqlcmd.ExecuteNonQuery()
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
Write-Host $SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$SQLResult =$DataSet.Tables[0]
$commands = $SQLResult | foreach-object -process { $_.output }> output.ps1
.\output.ps1

by cmille19 at 2013-03-12 08:59:53

If you're expecting a result set, you're not going to get is using ExecuteNonQuery. ExecuteNonQuery like the name implies is used for executing insert, update and deletes (nonquery).

You can either use a datareader or fill a dataset. I find it easier to work with datasets:
$dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

$dataAdapter.SelectCommand = new-object System.Data.SqlClient.SqlCommand ($query,$connString)

$dt = New-Object System.Data.DataTable

$null = $dataAdapter.fill($dt)
$dt

by MaxTrinidad at 2013-04-16 12:17:45

Passing parameters can be as simple as the following sample:

$parm1 = 'dbo.MonthlySales'
$parm2 = 'false'
$sqlqry = "exec sp_spaceused '$parm1', '$parm2' "
:
$dataAdapter.SelectCommand = new-object System.Data.SqlClient.SqlCommand ($sqlqry,$connString)
:

🙂