Set Octopus output variable from sql script

Question

I created octopus ranbook and one of the steps is run sql script. I need get result from sql procedure and set it to output variable which value I need to use on second step. When I try set output variable like Set-OctopusVariable -name "Name" -value $result right after sql script it fails because octopus try execute it like sql. I use SQL - Execute Script template and looks like I can't mix sql code and Set-OctopusVariable together. Can someone help with this issue

Tried use Set-OctopusVariable. Sql script works correctly, I logged results and it's ok, but I can't get it on another step of ranbook

Answer 1

The variable values are passed back from the task log to the server via an Octopus service message.

You can just use the PRINT command to write a formatted message, however, the values passed to the service message need to be base64 encoded.

Create a sql function to convert the values to base64:

CREATE FUNCTION ConvertToBase64 (@Input VARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
   RETURN (
       SELECT CONVERT(VARBINARY(MAX),@Input) FOR XML PATH(''), BINARY BASE64
       );
END

Then in your sql script you can write the service message:

DECLARE @name NVARCHAR(MAX) = (SELECT [dbo].ConvertToBase64('varname'))
DECLARE @value NVARCHAR(MAX) = (SELECT [dbo].ConvertToBase64('varvalue'))

PRINT '##octopus[setVariable name=''' + @name + ''' value=''' + @value + ''']'

As long as the sql execution output is currently being written to STDOUT it will end up in the task log and should produce a variable output.