Recently got a request to produce a report everyday that would be used for supporting a process. The report is only needed for a short time while we add more features. A change request is already underway, but our release cycle is long enough to require a daily task of creating a report.
I wanted a quick automated report that would give me the data I need, this way I wouldn’t be manually executing a query every day of the week. The solution was a quick PowerShell script that exports the data to a CSV file, set up as a scheduled task. I imported the SQL from a file in this case because the query is rather ugly..
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Push-Location; Import-Module SQLPS -DisableNameChecking; Pop-Location $SQLServer = "MyServer" $DBName = "MyDatabase" $dirPath = "\MyFolder\" $query = [IO.File]::ReadAllText($dirPath + "MyDataQuery.sql") $timer = (Get-Date -Format yyyy-MM-dd) $ReportFile = $dirPath + "rpt_" + $timer + ".html" [string]$UserName [string]$Password # if username isn't set, windows auth will be used instead $auth=@{} if($UserName){$auth=@{UserName=$UserName;Password=$Password}} # Remove any duplicate reports if (Test-Path -Path $ReportFile -PathType Leaf) { Remove-Item $ReportFile -Force } Invoke-Sqlcmd -ServerInstance $SQLServer -Database $DBName -Query $query @Auth | Export-CSV -Path $ReportFile -NoTypeInformation |
Its a quick and simple solution, and saves 20-30 minutes a day, and most importantly, it only took 1/2 hour to implement. Since the release is at least two weeks away, I’ve saved myself at least 4 1/2 hours for other things.