Public/ps1/Sql/Start-ApprxrJobSql.ps1
|
<#
.SYNOPSIS Executes a SQL job for Apprxr using provided task information. .DESCRIPTION Connects to the configured SQL database and executes the command specified in the taskInformation parameter. Returns the result or an error message. .PARAMETER taskInformation An object containing the command and other job parameters to execute in SQL. .EXAMPLE Start-ApprxrJobSql -taskInformation @{ command = 'SELECT * FROM Users' } .NOTES Used for running SQL jobs as part of Apprxr job automation. #> function Start-ApprxrJobSql { param ($taskInformation) if (-not $taskInformation.command) { return @{ message= "No command found" sucess= $false } } $sqlConfiguration = Get-ApprxrSqlConfiguration $sqlConn = New-Object System.Data.SqlClient.SqlConnection $sqlConn.ConnectionString = $sqlConfiguration.connectionString $ErrorActionPreference = "Stop" try { $sqlConn.Open() $sqlcmd = $sqlConn.CreateCommand() $sqlcmd.Connection = $sqlConn $query = $taskInformation.Command # if a channel id is specified write-host "$taskInformation.ChannelId" if ($taskInformation.ChannelId) { if (-not $sqlConfiguration.channelId) { return } elseif (-not $sqlConfiguration.channelId -eq $taskInformation.ChannelId) { return } } $sqlcmd.CommandText = $query $sqlresult = $sqlcmd.ExecuteReader() $table = new-object System.Data.DataTable $table.Load($sqlresult) $returnValue = @{ data= @($table | select $table.Columns.ColumnName) success= $true } } catch { Log("Connection issue with getting the data. $_") $returnValue = @{ message= $_ success= $false } } $ErrorActionPreference = "Continue" $sqlConn.Close() return $returnValue } |