Hi niners,
Came up with a little MSH script (MSH is sooo cool) that uses sql to retrieve a list of object that can be used together with all the other command line tools.
A simple call:
$connectionString = "Server=(local)\SQLEXPRESS;Database=AdventureWorks;Integrated Security='SSPI'"
$sqlText = "SELECT * FROM Production.Product"
Get-Sql $connectionString $sqlText
Format as table
Get-Sql $connectionString $sqlText | format-table
Export CSV
Get-Sql $connectionString $sqlText | export-csv "test.csv"
Loop Thru Items
$products = (Get-Sql $connectionString $sqlText)
foreach ($product in $products) {
write-host $product.Name
}
Heres the actual function:
function Get-Sql {
param ([string]$conn, [string]$sql)
# setup connection
$connObj = new-object System.Data.SqlClient.SqlConnection
$connObj.ConnectionString = $conn
# setup command
$cmdObj = new-object System.Data.SqlClient.SqlCommand
$cmdObj.Connection = $connObj
$cmdObj.CommandText = $sql
$connObj.Open()
$reader = $cmdObj.ExecuteReader()
while ($reader.Read()) {
$result = new-object System.Management.Automation.MshObject
for ($i=0;$i -lt ($reader.FieldCount) ;$i++) {
$fld = new-object System.Management.AUtomation.MshNoteProperty $reader.GetName($i), $reader[$i]
$result.MshObject.Members.Add($fld);
}
$result
}
$reader.Close()
$reader.Dispose()
$connObj.Close();
}
Enjoy