Dependancy for invoke-sqlcmd

Habitat Help
1

Hi All, trying to figure out what dependancy I need to use in habitat to use invoke-sqlcmd

2

Your best option here I think s to use the sqlserver powershell module. However there are some unobvious nuances when it comes to using this module in a Habitat hook. The key issue being that Habitat hooks run inside of powershell core but the Invoke-SqlCmd cmdlet uses functionality only available in Windows Powershell. So the best course of action here is to install and use the module inside of a Windows Powershell context. How do you do that in a Habitat hook? Use Invoke-Command from powershell core to enter into a Windows Powershell runspace:

Invoke-Command -ComputerName localhost -EnableNetworkAccess {
    $ProgressPreference="SilentlyContinue"
    Write-Host "Checking for nuget package provider..."
    if(!(Get-PackageProvider -Name nuget -ErrorAction SilentlyContinue -ListAvailable)) {
        Write-Host "Installing Nuget provider..."
        Install-PackageProvider -Name NuGet -Force | Out-Null
    }
    Write-Host "Checking for SqlServer PS module..."
    if(!(Get-Module SqlServer -ListAvailable)) {
        Write-Host "Installing SqlServer PS Module..."
        Install-Module SqlServer -Force -AllowClobber | Out-Null
    }

    {{#if bind.database}}
    $server = "{{bind.database.first.sys.ip}}\\"
    $server += "{{bind.database.first.cfg.instance}},"
    $server += "{{bind.database.first.cfg.port}}"
    
    Invoke-Sqlcmd -ServerInstance $server -Database Master -Username sa -Password Pass@word1 -InputFile {{pkg.svc_var_path}}\sql_scripts\a_bunch_of_sql.sql
    {{/if}}
}

We are effectively “remoting” to localhost in order to enter a Windows Powershell process.

1 Like