Thursday, June 20, 2013

PowerShell: Executing SQL stored procedure


Write-Host "Loading SQL PowerShell snapin..." -ForegroundColor Yellow
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
Write-Host "Done." -NoNewline -ForegroundColor Green

#Set the connection string
$SqlConnection.ConnectionString = "Connection string to SQL server goes here"

#Declare a SqlCommand object
$SqlCommand = New-Object System.Data.SqlClient.SqlCommand

try
{
    #Set SqlCommand properties
    $SqlCommand.CommandText = "Stored Procedure name goes here"
    $SqlCommand.Connection = $SqlConnection

    #Open SqlConnection
    $SqlConnection.Open()

    Write-Host "SqlConnection opened successfully." -ForegroundColor Green

    #Execute stored procedures
    $SqlCommand.ExecuteNonQuery()

    Write-Host "Stored procedure executed successfully." -ForegroundColor Green
}
catch 
{
    Write-Host "Error executing the stored procedure" -ForegroundColor Red
}
finally
{
    #Close connection always
    $SqlConnection.Close()
}

2 comments: