Execute stored procedure (PowerShell)

This example shows how to execute a stored procedure from PowerShell.

# Assumption, the DBName connection string is in the web.config file
# Replace “DBName” with the actual database name

# Replace “120” with the actual Command Timeout required (in seconds)
# Replace “SPName” with the actual stored procedure name

# Replace “Parm1, Parm2” with the actual parameter name
# -----------------------------------------------------------------------
$ConnString = [DotNetNuke.Common.Utilities.Config]::GetConnectionString("DBName")
$SQLConnection = New-Object System.Data.SqlClient.SqlConnection
$SQLConnection.ConnectionString = $ConnString

$SQLCmdString = " DBName.dbo.SPName"
$SQLCmdTimeout = 120

$SQLCmd = New-Object System.Data.SqlClient.SqlCommand
$SQLCmd.CommandType = [System.Data.CommandType]::StoredProcedure
$SQLCmd.CommandText = $SQLCmdString
$SQLCmd.CommandTimeout = $SQLCmdTimeout
$SQLCmd.Connection = $SQLConnection


$SQLCmd.Parameters.Add("@Parm1",[system.data.SqlDbType]::Int) | out-Null
$SQLCmd.Parameters['@ Parm1'].Direction = [system.data.ParameterDirection]::Input
$SQLCmd.Parameters['@ Parm1'].value = 1


$SQLCmd.Parameters.Add("@Parm2",[system.data.SqlDbType]::Int) | out-Null
$SQLCmd.Parameters['@ Parm2'].Direction = [system.data.ParameterDirection]::Input
$SQLCmd.Parameters['@ Parm2'].value = 2


$SQLConnection.Open()
$SQLCmd.ExecuteNonQuery() | out-null
$SQLConnection.Close()