Tests/SQLexportToFile.ps1
|
[CmdletBinding(SupportsShouldProcess = $True)] Param( ) #region Init #Load default arguemts for this script from the dfp setting files. #Command prompt arguments will override any settings function GetLocalDefaultsFromDfpFiles($CallerInvocation) { #Load script default settings foreach($settingsFile in (Get-SettingsFiles $CallerInvocation ".dfp")) { #Write-Host "File: [$settingsFile]" if (Test-Path $settingsFile) { $settings = Get-Content $settingsFile #Enumerate settingsfile rows foreach($row in $settings) { #Remarked lines are not processed if (($row -match "=") -and ($row.Trim().SubString(0,1) -ne "#")) { $key = $row.Split('=')[0] $var = Get-Variable $key -ErrorAction SilentlyContinue if ($var -and !($var.Value)) { try { $var.Value = Invoke-Expression $row.SubString($key.Length+1) } Catch { $ex = $PSItem $ex.ErrorDetails = "Err adding $key from $settingsFile. " + $PSItem.Exception.Message throw $ex } } #Write-Host "$($var.Value)" } } } } } #Load the module get-module PSJumpStart | Remove-Module; Import-Module PSJumpStart #Get Local variable default values from external DFP-files GetLocalDefaultsFromDfpFiles($MyInvocation) #Get global deafult settings when calling modules $PSDefaultParameterValues = Get-GlobalDefaultsFromDfpFiles($MyInvocation) #endregion Msg "Start Execution" Msg "Test a stored procedure call" $query="Exec [dbo].[CustOrdersOrders] 'OCEAN'" Invoke-SqlQuery $query | Select-Object -ExpandProperty DataSet | Select-Object -ExpandProperty Tables | Out-DataTableToFile Msg "SQL query with multiple output tables" $query="Select LastName,FirstName FROM [dbo].[Employees] Select * FROM [dbo].[Shippers] " Invoke-SqlQuery $query | Select-Object -ExpandProperty DataSet | Select-Object -ExpandProperty Tables | ForEach-Object { Out-DataTableToFile -FileName "$($_.TableName) Mixed double.csv" -DataTable $_ } Msg "End Execution" |