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://learn.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?

Hi Did you get a chance to try out below answer? how did it go? — Levi Lu-MSFT
– Levi Lu-MSFT, Commented Jun 24, 2020 at 8:39

Shamrai Aleksander · Accepted Answer · 2020-06-17 13:20:56Z

2

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:

answered Jun 17, 2020 at 13:20

Shamrai Aleksander

16.6k4 gold badges27 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gReX Over a year ago

This does work, also with injecting devops-variables to the sql-Script. Searching for an easier Multiline-SQL Way, the "SQL Server database deploy" with inline sql is not the way :-(

Levi Lu-MSFT · Accepted Answer · 2020-06-18 04:15:03Z

1

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：

answered Jun 18, 2020 at 4:15

Levi Lu-MSFT

30.8k3 gold badges42 silver badges55 bronze badges

1 Comment

gReX Over a year ago

tried this with the "SQL Server database deploy"-Task, does not print the infos. Closed an not fixed Bug: github.com/microsoft/azure-pipelines-tasks/issues/14043 points out, that the "SQL Server database deploy" with inline sql does not emit the -verbose flag to the powershell.

Collectives™ on Stack Overflow

Azure Pipelines - logging commands from SQL script

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related