Daryl1976 Daryl1976 - 12 days ago 6
SQL Question

Powershell Stored Procedure Parameters not being sent

I have a stored procedure in a PowerShell (version 4) script that logs the results of a job

function Log-Build($lastExitCode, $result, $taskId) {
$date = Get-Date
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)

$cmd = New-Object System.Data.SqlClient.SqlCommand
$myResult = $result -join "<br/>`r`n" |Out-String
$cmd.Connection = $connection;

$cmd.CommandText = "LSBuild_LogAndMailResult";

$cmd.Parameters.AddWithValue("@Task_ID",[string]$taskId);
$cmd.Parameters.AddWithValue("@Result",[string]$myResult);
$cmd.Parameters.AddWithValue("@Deployment_Status",[int]$lastExitCode);

$connection.Open()
$cmd.ExecuteNonQuery() | Out-Null
$connection.Close()
}


However, when I run it, I always get the following error response:


Exception calling "ExecuteNonQuery" with "0" argument(s): "Procedure or function 'LSBuild_LogAndMailResult' expects parameter '@Deployment_Status',
which was not supplied.


I checked in the terminal window in Powershell ISE to make sure the parameter was there ( $cmd.Parameters["@Deployment_Status"].value;) and it is definitely being added. Not sure why it's not getting passed to the database

Answer

You must set command type as stored procedure

$cmd.CommandType = [System.Data.CommandType]::StoredProcedure