Skip to content

Instantly share code, notes, and snippets.

martin-guth

Block or report user

Report or block martin-guth

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
@martin-guth
martin-guth / callExportReport.ps1
Created Nov 21, 2019
An example for a call of the script exportReport.ps1
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';"
@martin-guth
martin-guth / exportReport.ps1
Last active Nov 26, 2019
Complete PowerShellScript for exporting Reports to Excel using ImportExcel
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
@martin-guth
martin-guth / exportReport_Parameters.ps1
Last active Nov 21, 2019
Just the parameters for exportReport
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)
)
@martin-guth
martin-guth / testImportExcel
Last active Nov 20, 2019
Basic test script on how to export to Excel using the module ImportExcel from a SQL query fired off by dbatools
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;
You can’t perform that action at this time.