I recently wanted to run some SQL on a regular basis, like you would do with SSRS and subscriptions. However, I didn't have an implementation of SSRS installed, a server to install it to, or a desire to install it on my local machine to simply get a report at a regular interval. So, I thought through other options and pretty quickly decided to try to use PowerShell and a scheduled task, outputting my results to HTML. I had already written the queries I wanted to use for the data, so my next step was to figure out how to do this in PowerShell. I was pretty quickly able to get a connection established to SQL using the queries now saved to SQL files.

#First do Server
$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
$connection.Open()

# 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
$dataAdapter.Fill($dsTotalTime)




# Close the connection


$connection.Close()


Now 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.

$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.