#First do ServerNow I just needed to figure out how to get that dataset output to HTML, but I just wanted a chunk that I could insert it into a generic template. That's where the Fragment option came into play.
$dataSource = "Server"
# Database name
$database = "DatabaseName"
# Create a string variable with all our connection details
$connectionDetails = "Provider=sqloledb; Data Source=$dataSource; Initial Catalog=$database; Integrated Security=SSPI;"
# Connect to the data source using the connection details and T-SQL command we provided above, and open the connection
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails
# Get the results of our sql command into a DataSet object
$cmdTotal = [System.IO.File]::ReadAllText("C:\files\SQL\Total.sql")
$cmd = New-Object System.Data.OleDb.OleDbCommand $cmdTotal,$connection
$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $cmd
$dsTotalTime = New-Object System.Data.DataSet
# Close the connection
$htmlTotal = $dsTotalTime.Tables | Select-Object -Expand Rows | ConvertTo-HTML "Column2","Column3" -Fragment
Once that piece was complete the rest was a little bit of formatting and logic. Oh, and the scheduling part. It wasn't difficult but it didn't work the first time either. I created a scheduled task using this batch file:
powershell.exe "& 'C:\files\powershell\reports.ps1'"
That's it. Hope this is useful for someone. You can download the files here.