Daryl1976 Daryl1976 - 10 months ago 108
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.ExecuteNonQuery() | Out-Null

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 Source

You must set command type as stored procedure

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