Sql-server – `Invoke-SqlCmd` does not display RAISERROR messages where `sqlcmd` does

powershellsql server

I switched from sqlcmd to Invoke-SqlCmd, and suddenly my RAISERROR messages disappeared. When I run it with sqlcmd, they appear. Is there any way to make them visible with Invoke-SqlCmd?

RAISERROR (N'Hello World',0,1) WITH NOWAIT;

Invoke-SqlCmd -Server $env:MSSQL_SERVERNAME -Username $env:MSSQL_USERNAME -Password $env:MSSQL_PASSWORD -Database "mydatabase" -Query "EXEC dbo.myprocedure" -OutputSqlErrors $true;

/opt/mssql-tools/bin/sqlcmd -S $env:MSSQL_SERVERNAME -U $env:MSSQL_USERNAME -P $env:MSSQL_PASSWORD -d mydatabase -Q "EXEC dbo.myprocedure";

Best Answer

According to the documentation for Invoke-Sqlcmd, you can use -verbose to display the output messages. From that post:

Example 5: Run a query and display verbose output

PS C:\> Set-Location "SQLSERVER:\SQL\MyComputer\MainInstance"
PS C:\> Invoke-SqlCmd -Query "PRINT N'abc'" -Verbose
VERBOSE: abc

I have bat file processes that use this technique and pipe the output of the messages to a file. Example:

-Verbose 4>&1> 'C:\AperioSync\SyncOutput.txt'"

Related Question