SQL Execute Script step doesn't display results in Octopus log

[bdjurkic (Banchy)]

Hi guys,

I am using step “SQL - Execute Script” from the library. I get the output count value “1” but do not have any results from the SQL query. I tried to add one more PS script after to display the result from the query but did not get anything.

Write-Host #{Octopus.Action[Execute Query].Output.SQLOutput-1} # my step name

I thought also to add a while loop and modify the library step but that didn’t work (might be I am doing it wrong). I tried adding after this section:

Write-Host “Executing script”
#Execute-SqlQuery -query $OctopusParameters[‘SqlScript’]

######################### To add this:

	   $exec = $(Execute-SqlQuery -query $OctopusParameters['SqlScript'])
    $sqlCommand = New-Object System.Data.SQLClient.SQLCommand
    $SqlCommand.Connection = $connection
    $SqlCommand.CommandText = $exec
	   $Reader = $sqlCommand.ExecuteReader()
	   while ($Reader.Read()) {
	   Write-Host $Reader.GetValue(0)
	  }

#########################

Library step: [SQL - Execute Script]( Octopus Deploy Library)

Any thoughts?

K

[finnian.dempsey]

Hey @bdjurkic,

Great to hear from you again, thanks for reaching out!

I’ve just tested this step and believe I know what might be going on… It looks like the Octopus Output variables are being populated from the values captured via the SqlInfoMessageEventHandler and so it looks like query results will need to be printed in order to be captured in an output variable.

In my tests I found the following allowed for me to PRINT select results via a variable by using the following SQL command:

Declare @projectId VARCHAR(MAX);
Select @projectId= (select Id from dbo.Project where Name like '%Test%');
Print @projectId;

And then I could reference that PRINTED result in a script step that followed by running the following:

write-host #{Octopus.Action[SQLExecute].Output.SQLOutput-1}

Let me know how you get on or if you have any questions at all!

Best Regards,

[bdjurkic (Banchy)]

Hi,

Thank you for your reply but I still can’t make it work. I am using something like this with project variables but getting exceptions when executing the query in step.
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

DECLARE @Db varchar(200) = ‘#{DB.CoreDatabaseName}’;
PRINT ‘Using ’ + @Db + ’ database’

DECLARE @q varchar(MAX);
SELECT @q= (SELECT [ValueName], [Value] FROM [Db].[dbo].[Values] WHERE ValueName LIKE ‘%Host’);
PRINT @q;

What I am doing wrong?

[donny.bell]

Hi @bdjurkic,

Thank you for getting back to us.

I tested this locally in my test environment and it looks like the error is stemming from the SELECT statement’s multiple columns [ValueName], [Value]. If you use just a single column this should work as long as the SELECT query returns a single row. Otherwise, you may end up an error such as:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

If the purpose of the query is to print the information, it may be easier to declare a table in this case.

Hopefully this provides a bit more context around the messages you are getting. Let us know if you have any additional questions.

Best Regards,
Donny

[bdjurkic (Banchy)]

Hi,

I got what I wanted when running my query on the SQL directly. But when posted to Octopus, there is no output.

DECLARE @tempTable1 Table
DECLARE @tempResults1 Table
INSERT INTO @TempTable1
WHILE (go through all databases to get some data)

DECLARE @tempTable2 Table
DECLARE @tempResults2 Table
INSERT INTO @TempTable2
WHILE (go through all databases to get some data)

Then I summarize the results which are displayed in 4 culoms (dbname, col1, col2, col3)
SELECT DISTINCT colums1, coulms2,… SUBSTRING(something)

Any ideas on how to see this in output as in step to display doesn’t;t show anything

Write-Host #{Octopus.Action[Step-Name].Output.SQLOutput-1}

Thanks,
Branko

[donny.bell]

Hi @bdjurkic,

Thank you for getting back to us.

There is a little note for the Step Template that says:

Text output by the PRINT statement in SQL will be logged to the deployment log. Use ‘GO’ to separate multiple commands

I tested this locally in various scenarios and could only get PRINT statements to show up in the logs.

Since the Output variable relies on the PRINT statement, you’ll likely need to iterate through the values in the table similar to this example I found on StackOverFlow. Unfortunately, I’m unable to find a more straightforward way to print table values at this time. However, I’m currently working with our Solutions team internally to see if there’s more we can do here from the Step Template side to allow this possibility.

Let me know if you have any additional questions. I’ll get back to you once we discuss this in more depth internally.

Best Regards,
Donny Bell