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:

Please, [do not post images of errors](https://meta.stackoverflow.com/a/285557/503046). — vonPryz, Sep 09 '20 at 07:01
Try ```”Username” = $using:DatabaseUsername``` and see if that works. It might be a scoping issue... — mclayton, Sep 09 '20 at 07:30
@mclayton Its working. Thanks. Can you please tell me how to concatenate two variables i.e. $using:SystemWorkingDir + $using:$OngoingChangesScriptLocation? — user13624867, Sep 09 '20 at 08:09
@vonPryz I just wondering posting images of error is against the policy? — user13624867, Sep 09 '20 at 08:14
No, it's just immensely unhelpful. Help us to help you I think is the message. — codaamok, Sep 09 '20 at 09:12
I'll just quote one bullet point with added emphasis from the linked meta article and leave this question at that. "You're asking us to _volunteer our time for free_ to _solve your problem_." — vonPryz, Sep 09 '20 at 09:12

score 2 · Accepted Answer · answered Sep 09 '20 at 14:31

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
    ...
}

score 0 · Answer 2 · answered Sep 27 '22 at 21:41

According to microsoft Using Local Variable in remote command it says

For variables in a remote command created in the local session, use the Using scope modifier Syntax $Using:<VariableName>. PowerShell assumes that the variables in remote commands were created in the remote session.

Simple - Example:

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

2 Answers2