How can I configure in MS release manager to call a stored procedure as part of automation build deployment

Question

As part build automation I need to execute a stored procedure (on daily basis) from Microsoft Release Manager , what is the best possible way to do it ?

Answer 1

You can use the PowerShell step to execute a stored procedure.

1) Write a PowerShell script to execute stored procedures

2) Add the release step "PowerShell"

3) Set the path to your script

Here is a script to execute a stored procedure:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Your connection string"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$sqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
$SqlCmd.CommandText = "sp_helpdb"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]