How to call a stored procedure with a char parameter from Powershell

Question

I have a Powershell script which I want to execute a SQL Server stored procedure. The stored procedure has a parameter named @backupType which of a CHAR(1) type. I'm calling it like this:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=(local);Database=master;Integrated Security=True"
$SqlConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "sp_myStoredProcedure"
$SqlCmd.Connection = $SqlConnection

$SqlCmd.Parameters.Add("@backupType", [System.Data.SqlDbType]"Char", 1)
$SqlCmd.Parameters["@backupType"].Value = "F"

$result = $SqlCmd.ExecuteNonQuery()
$SqlConnection.Close()

The error I get is:

Exception calling "ExecuteNonQuery" with "0" argument(s): "Procedure or function 'sp_myStoredProcedure' expects parameter '@backupType', which was not supplied." At line:14 char:1

$result = $SqlCmd.ExecuteNonQuery()

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

CategoryInfo : NotSpecified: (:) [], MethodInvocationException

FullyQualifiedErrorId : SqlException

I can only assume I'm specifying the parameter wrong but a Google and search of SO has not surfaced anything helpful. Any ideas?

@TheIncorrigible1 I don't think so. I expect it's a collection of strongly typed parameters but I'm not 100% sure. — Tom Troughton
– Tom Troughton, Commented Dec 15, 2017 at 17:19
@DavidG I removed two other parameters which are both string parameters and occur earlier in the SP. Since the error specified the char param I assumed the issue was with my syntax but maybe the error is misleading? I've triple-checked for typos etc. — Tom Troughton
– Tom Troughton, Commented Dec 15, 2017 at 17:20

Esperento57 · Accepted Answer · 2017-12-16 09:10:05Z

1

try with

$SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
$SqlCmd.Parameters.Add("@backupType", [System.Data.SqlDbType]::Char, 1).Value="F"

answered Dec 16, 2017 at 9:10

Esperento57

17.6k6 gold badges42 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to call a stored procedure with a char parameter from Powershell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related