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; |
Loading
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
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 |
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) | |
) |
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';" |
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; |