I had the requirement to create a power shell script that would query a SQL database. It turned out to be very easy indeed…
# Create SqlConnection object, define connection string, and open connection
$con = New-Object System.Data.SqlClient.SqlConnection
$con.ConnectionString = "Server=Livesqlserver; Database=WebCDB; Integrated Security=true"
$con.Open()
First create the connection…
$cmdSelect = "SELECT DATEDIFF(day, update_date, getdate()) as datedifference, DATENAME(dw, update_date) as theday , count(*)as TotalMails FROM mail_tbl where sent=1 and mail_type='XTRANET' and DATEDIFF(day, update_date, getdate()) < 7 group by DATEDIFF(day, update_date, getdate()), DATENAME(dw, update_date) order by datedifference desc"
$da = New-Object System.Data.SqlClient.SqlDataAdapter($cmdSelect, $con)
Create the SQL you want to return values on..
$dt = New-Object System.Data.DataTable
$da.Fill($dt) | Out-Null
Fill a dataset with the results
Foreach ($row in $dt.rows)
{ Write-Host $row.theday $row.TotalMails }
Print out the results… easy
