View callExportReport.ps1
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
[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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |