$SQLServerPort - Exsilio Blog

I am naming my first runbook and workflow SQL_Job_Agent. I also want to return an output of a string from my code, and I set up all the parameters that I will need with the code below.

workflow SQL_Job_Agent {

[OutputType([string])]

param (

[Parameter(Mandatory=$true)] [string] [Parameter(Mandatory=$false)] [int] [Parameter(Mandatory=$true)] [string] [Parameter(Mandatory=$true)] [string] [Parameter(Mandatory=$false)] [string] [Parameter(Mandatory=$false)] [string] [Parameter(Mandatory=$false)] [bool] [Parameter(Mandatory=$false)] [string] [Parameter(Mandatory=$false)] [string] )

$sqlServer, $SQLServerPort = 1433, $Database, $SprocName, $Parameters = "", $SqlCredentialName, $SendErrorEmail = $false, $FromEmailCredentialName, $ToEmailAssetName

The parameters are: $sqlServer ? the name of your SQL server that your stored procedure is located on. $SQLServerPort ? the port that is open for connections to your SQL server. The default is 1433. $Database ? the name of the database that your stored procedure is located on. $SprocName ? the name of the stored procedure that you wish to execute. $Parameters ? parameters that the stored procedure takes. The format should be @parameter1Name = parameter1Value, @parameter2Name = parameter2Value, ... $SqlCredentialName ? Name of the credential that you set up to connect to the SQL server and database. $SendErrorEmail - $true or $false value if you wish to send error emails. The default is $false. $FromEmailCredentialName ? Name of the email credential that you set up. $ToEmailAssetName ? Name of the variable asset containing the email addresses you wish to receive the error emails.

The next section of code will get all of the credentials and variables that we set up previously.

#Get Credential Assets #Get the username and password from the SQL Credential $SqlCredential = Get-AutomationPSCredential -Name $SqlCredentialName $SqlUsername = $SqlCredential.UserName $SqlPass = $SqlCredential.GetNetworkCredential().Password #Get email assets if required if ($SendErrorEmail) {

#Get email to credentials $EmailCredential = Get-AutomationPSCredential -Name $FromEmailCredentialName $FromEmail = $cred.UserName #Get email list $ErrorEmailRecipientList = Get-AutomationVariable -Name $ToEmailAssetName #split if there is a delimter (mutliple emails) if ($ErrorEmailRecipientList -like '*|*') {

$ErrorEmailRecipients = $ErrorEmailRecipientList.Split('|') } else {

$ErrorEmailRecipients = $ErrorEmailRecipientList } }

The rest of our code will need to be contained in an inlinescript block to allow us to use the functions we

will use as they are not valid in workflows. This next code segment sets up the connection and executes

the stored procedure. Note that will setting up the $Conn variable, all parameters must be in the same

line of code.

#Run SQL Query inlinescript {

$haveError = 0 # Define the connection to the SQL Database # connection timeout of 7200 is 2 hours. Adjust if needed. $Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$using:SqlServer, $using:SqlServerPort; Database=$using:Database; User ID=$using:SqlUsername; Password=$using:SqlPass; Trusted_Connection=False; Encrypt=True; Connect Timeout=7200;") $outputDataTable = New-Object System.Data.DataTable

[string[]] $ColumnNames try {

# Open Connection $Conn.Open() $Cmd=new-object system.Data.SqlClient.SqlCommand

$Cmd.Connection = $Conn $mandText = 'EXEC ' + $using:SprocName + ' ' + $using:Parameters + '; SELECT @@ROWCOUNT;'

$mandTimeout = 7200 #command timeout set to 2 hours. Adjust if needed.

# create data adapter from the SQL command $sqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $Cmd

# declare dataset to hold data $dataSet = New-Object System.Data.DataSet # print sql command Write-Output($mandText) # fill the adapter $sqlDataAdapter.Fill($dataSet) | out-null

# records returned? if ($dataSet.Tables[0].Rows.Count -gt 0) {

$outputDataTable = $dataSet.Tables[0] } else {

$outputDataTable = "SQL query successfully executed." } }

The following code block has our catch statement, which will send out an error email to the emails

contained in the email variable you set up earlier, using the email credential set up prior. I also have a

finally code block that closes the connection with the database. Note that this example is for an

Office365 email account. You will need to change the SmtpServer value for different email services.

For example, to send email from a gmail account, SmtpServer should be set as smtp..

catch {

$haveError = 1 Write-Error -Message "Error while executing the SQL command." Write-Error -Message $_.Exception.Message Write-Error -Message $_.Exception.StackTrace #Send error email here if that is required. if ($using:SendErrorEmail) {

$EmailBody = "Error occurred while executing " + $using:SprocName if ($using:Paraemters) {

$EmailBody = $EmailBody + " with Parameters " + $using:Paraemeters } $EmailBody = $EmailBody + "." + $_.Exception.Message + "" + $_.Exception.StackTrace $EmailBodyString = $EmailBody | Out-String $subject = $using:Sprocname + " Failure Notification"

Send-MailMessage -To $using:ErrorEmailRecipients -Subject $subject -Body $EmailBodyString -UseSsl -Port 587 -SmtpServer "smtp." -From $using:FromEmail BodyAsHtml -Credential $Using:EmailCredential

} }

finally {

if ($Cmd -ne $null) {

$Cmd.Dispose } # Close the SQL connection $Conn.Close() $Conn.Dispose() }

Note that the Send-MailMessage call must have all parameters in one line of code.

The final block of code selects the correct value to return based on whether or not there was an error. It will return the result from the stored procedure and the affected row count or a message that the stored procedure failed.

if($haveError -eq 0) {

return $outputDataTable | Format-Table $a } else {

return $using:SprocName + " Failed." } } }

Now, you have a modular runbook that we can call with a set of parameters.

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download