Azure Pipelines - logging commands from SQL script

Question

I am trying to log some messages from a TSQL script running via Azure Pipelines, for instance, before creating a table we check if table already exists and if so we simply print a message and skip table creation...

there are good articles explaining how to access Azure Pipelines Logging Commands from BASH or PowerShell, for instance this article: https://docs.microsoft.com/en-us/azure/devops/pipelines/scripts/logging-commands?view=azure-devops&tabs=bash

but how to output messages to the pipeline logs from within TSQL statement itself?

I will try with RAISERROR ( e.g. RAISERROR('Table [dbo].[ReportHistory] already exists!', 0, 1) WITH NOWAIT; ) hopefully works better than PRINT command, has anyone had similar issue and how did he resolve it?

Answer 1

You can run your scripts through PowerShell Invoke-Sqlcmd with -Verbose key. Here is small example for PowerShell task:

$server = "$(servername)"
$dbname = "$(dbname)"
$u = "$(username)"
$p = "$(password)"
$filename = "testfile.sql"

$filecontent = "RAISERROR('Table [dbo].[ReportHistory] already exists!', 0, 1) WITH NOWAIT;`r`nGO`r`n"

Set-Content -Path $filename -Value $filecontent

Write-Host '##[command] Executing file... ', $filename    

#Execution of SQL packet 
try 
{
    Invoke-Sqlcmd -InputFile "$filename" -ServerInstance $server -Database $dbname -Username "$u" -Password "$p" -QueryTimeout 36000 -Verbose 
} 
catch 
{ 
    Write-Host "##[error]" $Error[0] 
    Write-Host "##[error]----------\n"
} 

Result:

Answer 2

You can use Azure Pipelines Logging Commands together with PRINT and RAISERROR commands.

The logging commands syntax ##vso[task..] is the reserved keywords in Azure devops piplines. When ##vso[task..] is found in the tasks' output stream, Azure devops pipeline will execute the logging commands.

So that you can output messages to the pipeline logs from within TSQL statement using logging commands with PRINT or RAISERROR. See below example:

PRINT N'##vso[task.logissue type=warning]Table already exists.';

RAISERROR('##vso[task.logissue type=warning]Table [dbo].[ReportHistory] already exists!',0,1);

See below output messages in pipeline log：