How to connect to, query, and deal with the results of a SQL server. Source: systemcentercentral.com
Declare Variables:
$SQLServer = [server\instance]
$SQLDB = [database]
$SQLQuery = "select [columns] from [table]"
Connect using integrated security and run query. Results stored in $DataSet:
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDB; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
Handle each row of results in $DataSet. 1st Field is [0], 2nd field is [1], etc. Can specify .ToString(), .Trim(), .DateTime, calculations, etc.:
foreach ($row in $DataSet.Tables[0].Rows)
{
$Field1 = $row[0].ToString()
Write-Host $Field1
}
