Cannot validate argument on parameter 'Username'. The argument is null or empty. Provide an argument that is not null or empty

Question

I am trying to execute Powershell script (7.0) file using Powershell 7-Preview which iterates through all the databases and execute SQL Server script in all the DBs.

The script fetches all the databases correctly, however, when Parallel block executes, I am getting this error (please find the below screenshot for details).

Cannot validate argument on parameter 'Username'. The argument is null or empty. Provide an argument that is not null or empty, and then try the command again.

Command - C:\GitHub\TempApp\CompanyTemplate\bin\debug\DeploymentScripts\PowerShell\DeployCompanyDatabases.ps1

Param
(
    [string]$SystemWorkingDir,
    [string]$DatabaseUsername,
    [string]$DatabasePassword,
    [string]$DacpacLocation,
    [string]$OngoingChangesScriptLocation,
    [string]$PreDeploymentBugFixScriptLocation,
    [string]$DropExternalTablesScriptLocation
)

    $SystemWorkingDir = "C:\GitHub\TempAPP\CompanyTemplate\bin\Debug" 
    $DatabaseUsername = "tempDB"
    $DatabasePassword = "tempPassword" 
    $DacpacLocation = "CompanyTemplate.dacpac" 
    $OngoingChangesScriptLocation = "DeploymentScripts\OnGoingDataChanges.sql" 
    $PreDeploymentBugFixScriptLocation = "DeploymentScripts\PreDeployment.sql"
    $DropExternalTablesScriptLocation = "DeploymentScripts\DropExternalTables.sql"
    [string]$ServerInstance = "tempDB"

$GetDatabasesParams = @{
    "Database" = "master"
    "ServerInstance" =  "tempDB"
    "Username" = "$DatabaseUsername"
    "Password" = "$DatabasePassword"
    "Query" = "select [name] from sys.databases where [name] like 'Company%'"
    "ConnectionTimeout" = 9999
}

echo "Retrieving company database names"
[string[]]$DatabaseNames = Invoke-Sqlcmd @GetDatabasesParams | select -expand name

[int]$ErrorCount = 0
$DatabaseNames | ForEach-Object -Parallel {
    try 
    {
        $OngoingChangesScriptParams = @{
            "Database" = "$_"
            "ServerInstance" = "$ServerInstance"
            "Username" = "$DatabaseUsername"
            "Password" = "$DatabasePassword"
            "InputFile" ="$SystemWorkingDir\$OngoingChangesScriptLocation" 
            "OutputSqlErrors" = 1
            "QueryTimeout" = 9999
            "ConnectionTimeout" = 9999
        }

        Invoke-Sqlcmd @OngoingChangesScriptParams       
    }
    catch
    {
        $ErrorCount++
        echo "Internal Error. The remaining databases will still be processed."
        echo $_.Exception|Format-List -force
    }
}

Error:

Answer 1

This question isn't quite a duplicate of Send string parameters to a Start-Job script block, but the answer is pretty much the same as https://stackoverflow.com/a/63702220/3156906...

The problem is that the $DatabaseUsername and $DatabasePassword, etc, variables inside your foreach-object script block are in a different scope and are effectively different variables to the ones in the main script.

You can fix this by using the Using scope modifier

For any script or command that executes out of session, you need the Using scope modifier to embed variable values from the calling session scope, so that out of session code can access them.

So instead of:

$DatabaseUsername = "myusername"

foreach-object -parallel {
    ...
    "Username" = $DatabaseUsername
    ...
}

try

$DatabaseUsername = "myusername"

foreach-object -parallel {
    ...
    "Username" = $using:DatabaseUsername
    ...
}