public/Invoke-SqlCmdWithMessages.ps1
|
Function Invoke-SqlCmdWithMessages { <# .SYNOPSIS Executes a SQL statement much like in SQL Server Management Studio .DESCRIPTION Executes a SQL statement much like in SQL Server Management Studio providing output of e.g. rows affected and print statements as soon as it is outputted from sqlcmd.exe, not just at the end of the entire script. The output is intended for debugging and auditing. If the output is intended for presentation or rework, then please use the function: Invoke-SqlQuery To ensure 'live' messages in the SQL statement, then use RAISERROR('message text',10,1) WITH NOWAIT over PRINT statements. In case of the errors in the SQL statement, the line number in the error refers to the source query. Using the OutputFile parameter over *> C:\tmp\error.txt provides prettier error messages. .EXAMPLE Invoke-SqlCmdWithMessages -Query 'select 0' -ServerInstance 'localhost' -Database master -OutputFile C:\tmp\test2.txt .INPUTS TO DO .OUTPUTS All output from sqlcmd.exe + an exitcode from sqlcmd.exe .LINK https://github.com/DennisWagner/SQLServerDevOpsTools .NOTES Written by (c) Dennis Wagner Kristensen, 2021 https://github.com/DennisWagner/SQLServerDevOpsTools This PowerShell script is released under the MIT license http://www.opensource.org/licenses/MIT #> Param ( [Parameter(Mandatory=$true)]$Query, #[Parameter(Mandatory=$true)]$InputFile, [Parameter(Mandatory=$false)]$OutputFile, [Parameter(Mandatory=$true)]$ServerInstance, [Parameter(Mandatory=$true)]$Database, [Parameter(Mandatory=$false)]$QueryTimeout ) BEGIN { Write-Verbose "Executing query using sqlcmd.exe" If (-not $OutputFile) { Write-Warning "When outputting to the console, any PRINT or RAISERROR statements will result in a NativeCommandError from SQLCMD.EXE on the first occurrence. This has not functional effect." } $SqlArgs = "" $SqlArgs += " -S ""$ServerInstance""" $SqlArgs += " -d ""$Database""" $SqlArgs += " -Q ""$($Query.replace('""', '""""""""'))""" $SqlArgs += " -b" # sets LASTEXITCODE in case of error $SqlArgs += " -E" # use trusted connection ~ windows security # r1 = redirect errors to stderr, r0 = redirect errors to stdout. # r0/r1 has no effect if -o is used # r0 = pretty SQL errors, but error on first PRINT or RAISERROR. # r1 = ugly SQL errors, but no error on PRINT or RAISERROR. Sends output during execution $SqlArgs += " -r1" If ($QueryTimeout) { $SqlArgs += " -t $QueryTimeout" # query timeout } If ($OutputFile) { $SqlArgs += " -o ""$OutputFile""" } $CmdExp = "& sqlcmd.exe $SqlArgs" } PROCESS { # Execute the external exe in an invoke-command to get output during execution, and not just after the execution is completed Invoke-Command -ScriptBlock { Invoke-Expression $CmdExp $ExitCode = $LASTEXITCODE If ($ExitCode -gt 0) { Throw "The command/statement contained one or more errors. See previous output or the output file for more info." } } } END { } } |