Octopus Deploy - SQL - Execute Scripts Ordered step giving Exception

Question

In Octopus deploy I have added a step in process to run the stored procedure with library script “SQL - Execute Scripts Ordered step”.

When I’m providing the script to execute the stored procedure it is throwing the below Exception:

Exception calling “ReadAllText” with “1” argument(s): “The specified path, file name, or both are too long. The fully qualified file name must be less than 260 characters, and the directory name must be less than 248 characters.” Closing connection

I believe this is because of the large script as text I've provided to execute in field “SQL Script File”. As shown in examples I can run script directly. So I’m providing the stored procedure execution script but in library's PowerShell scipt -

$content = [IO.File]::ReadAllText($OctopusParameters[‘SqlScriptFile’])

ReadAllText is expecting something less than 260 characters.

One solution I can think of is to provide the execution script as a file within package itself. But this will be the last resort.

How can I run the stored procedure directly from the step in process?

Answer 1

Apparantly [IO.File]::ReadAllText($OctopusParameters[‘SqlScriptFile’]) is expecting file path as SqlScriptFile. I updated the library's powershell script to take the full sql script from field "SQL Script File" as parameter and passed it directly to the function.

$content= $OctopusParameters['SqlScriptFile']
        Execute-SqlQuery -query $content

providing below the full powershell script for reference:

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $OctopusParameters['ConnectionString']
Register-ObjectEvent -inputobject $connection -eventname InfoMessage -action {
    write-host $event.SourceEventArgs
} | Out-Null

function Execute-SqlQuery($query) {
    $queries = [System.Text.RegularExpressions.Regex]::Split($query, "^\s*GO\s*`$", [System.Text.RegularExpressions.RegexOptions]::IgnoreCase -bor [System.Text.RegularExpressions.RegexOptions]::Multiline)

    $queries | ForEach-Object {
        $q = $_
        if ((-not [String]::IsNullOrWhiteSpace($q)) -and ($q.Trim().ToLowerInvariant() -ne "go")) {            
            $command = $connection.CreateCommand()
            $command.CommandText = $q
            $command.CommandTimeout = $OctopusParameters['CommandTimeout']
            $command.ExecuteNonQuery() | Out-Null
        }
    }
}

Write-Host "Connecting"
try {
    $connection.Open()
    Write-Host "Executing script in" $OctopusParameters['SqlScriptFile']
    # $content = [IO.File]::ReadAllText($OctopusParameters['SqlScriptFile'])
    $content= $OctopusParameters['SqlScriptFile']
    Execute-SqlQuery -query $content
}
catch {
    if ($OctopusParameters['ContinueOnError']) {
        Write-Host $_.Exception.Message
    }
    else {
        throw
    }
}
finally {
    Write-Host "Closing connection"
    $connection.Dispose()
}

Answer 2

The SQL - Execute Scripts Ordered template is meant to be run against a folder of SQL script files.

The SQL - Execute Script template may be a better match for your scenario.