Run SQL Query with Powershell

runSqlQuery_PS.ps1

# Setting up the connection string (suggest using a sql account vs Windows login)
$dataSource = "<server name>"   
$user = "username"
$pwd = "password"
$database = "<database name>"
$connectionString = "Server=$dataSource;uid=$user; pwd=$pwd;Database=$database;Integrated Security=False;"

#Create the query and store it in a variable
$query = "SELECT [<ColumnName>] FROM [<DatabaseName>].[dbo].[<tablename>]"

#Opening the connection
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$command = $connection.CreateCommand()

#Run the query
$command.CommandText  = $query

#Where to stick the results
$result = $command.ExecuteReader()
$JTtable = new-object System.Data.DataTable
$JTtable.Load($result)

# Formatting the results
$format = @{Expression={$_.Name};Label="Job Title";width=50}

# Getting the final output
return $JTtable
# Write-Host $JTtable (this can be used to write to other properties)