View callExportReport.ps1
[String] $dateFrom = $(Get-Date).AddMonths(-1).ToString("yyyyMM01 00:00:00") | |
[String] $dateTo = Get-Date -Format "yyyyMM01 00:00:00" | |
[String] $month = $dateFrom.Substring(0,6) | |
echo "$dateFrom $dateTo $month"; | |
& "X:\Scripts\exportReport.ps1" -databaseServer "mydbserver" -databaseName "mydb" -sqlFile "mySqlFile.sql" -fileName "FancyExport" -fileNameSupplement "_$month" -parameters "DECLARE @dateTimeFrom DATETIME = '$dateFrom', @dateTimeTo DATETIME = '$dateTo';" |
View exportReport.ps1
#exportReport.ps1 | |
<# | |
.SYNOPSIS | |
Takes a query from a SQL file, adds parameters to it, executes it on a SQL Server and writes the result into an Excel file. Return value 0 indicates successful execution, Return value -1 indicates errors. | |
.DESCRIPTION | |
This script enables exporting query results to excel from a machine, where Excel is not installed. | |
This is done leveraging the module ExcelPSLib. | |
System requirements: | |
- Powershell version 3 or greater with modules dbatools and ImportExcel installed |
View exportReport_Parameters.ps1
param( | |
[Parameter(Mandatory=$TRUE)] [ValidateNotNullOrEmpty()] [string] $databaseServer, | |
[Parameter(Mandatory=$TRUE)] [ValidateNotNullOrEmpty()] [string] $databaseName, | |
[Parameter(Mandatory=$TRUE)] [ValidateNotNullOrEmpty()] [string] $sqlFile, | |
[Parameter(Mandatory=$FALSE)] [ValidateNotNullOrEmpty()] [string] $parameters = '', | |
[Parameter(Mandatory=$FALSE)] [ValidateNotNullOrEmpty()] [string] $fileName = 'Export', | |
[Parameter(Mandatory=$FALSE)] [ValidateNotNullOrEmpty()] [string] $worksheetName = 'Export', | |
[Parameter(Mandatory=$FALSE)] [ValidateNotNullOrEmpty()] [string] $mailRecipient = 'martin.guth@3c-d.de', | |
[Parameter(Mandatory=$FALSE)] [ValidateNotNullOrEmpty()] [string] $fileNameSupplement = '_' + $(Get-Date -Format yyyyMMdd_HHmmss) | |
) |
View testImportExcel
Import-Module dbatools; | |
Import-Module ImportExcel; | |
$credential = Get-Credential; | |
# get results from table | |
$result= Invoke-DbaQuery -SQLInstance localhost -Database master -Query "SELECT * FROM sys.tables" -SqlCredential $credential ; | |
# debug the output | |
$result | Out-GridView; | |
$begin = Get-Date; |
View testExcelPSLib.ps1
Import-Module dbatools; | |
Import-Module ExcelPSLib; | |
$credential = Get-Credential; | |
# get results from table | |
$result= Invoke-DbaQuery -SQLInstance localhost -Database master -Query "SELECT * FROM sys.tables" -SqlCredential $credential ; | |
# debug the output | |
$result | Out-GridView; | |
$begin = Get-Date; |