PowerShell :: Microsoft.Azure.Commands.Sql.Database.Model.AzureSqlDatabaseModel.DatabaseName-CodePudding

I wrote a script that allows me to query the whole Azure database park:

#$ErrorActionPreference = 'SilentlyContinue'
# Connect to Azure
$azureAccount = Connect-AzAccount 
# Get Azure Access Token (we will use this to query the databasees)
#$azureToken = Get-AzAccessToken -ResourceUrl https://database.windows.net
$access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
# Queries will be picked up from here
$folderPath = '.\Queries'
# Choose how to format each date ("yyyy-MM-dd") or ("yyyy-MM-dd HH:mm:ss")
$DateTime = (Get-Date).ToString("yyyy-MM-dd")
# List Azure Sunscriptions
Get-Azsubscription | ForEach-Object -Begin { $a = 1 } -Process {"$a $($_.Name)"; $a  }
$SubscriptionChoice = Read-Host -Prompt "Copy/paste the name of the Subscription that you want to investigate. If more than one separate them by a coma, Type `"All`" if you want to target all of them"
# Iterate into subscriptoins and print names
foreach ($gs in $SubscriptionChoice) {
    Select-Azsubscription -Subscription "$gs" | Out-Null
    Write-Host "Let's browse into Azure Sunscription: " -NoNewline
    Write-Host (Get-AzContext).Subscription.Name -ForegroundColor green
    # Fins all Azure SQL Server
    Get-AzSqlServer | ForEach-Object -Begin { $a = 1 } -Process {"$a $($_.ServerName)"; $a  }
    $SqlServerChoice = Read-Host -Prompt "Copy/paste the name of the SQL Server that you want to investigate. If more than one separate them by a coma, Type `"All`" if you want to target all of them"
    
    if ($SqlServerChoice = "All"){
        $SqlServerChoice = Get-AzSqlServer
        }
    
    Foreach ($server in $SqlServerChoice){
        $DatabaseChoice = Get-AzSqlDatabase -ServerName $server.ServerName -ResourceGroupName $server.ResourceGroupName | Where-Object DatabaseName -NE "master"
        Foreach ($database in $DatabaseChoice){
            (Get-ChildItem $folderPath | sort-object {if (($i = $_.BaseName -as [int])) {$i} else {$_}} ).Foreach{
            Invoke-Sqlcmd -ServerInstance $server.FullyQualifiedDomainName -Database $database.DatabaseName -AccessToken $access_token -InputFile $psitem.FullName | Export-Csv -Path ".\Results\$psitem.csv" -Append -NoTypeInformation
            write-host "Executing $psitem on $database.DatabaseName"
            }
        }
    }   
}

However each time the query is executed against a database the Write-Hosts returns:

Executing DTU_to_vCore.sql on Microsoft.Azure.Commands.Sql.Database.Model.AzureSqlDatabaseModel.DatabaseName

Here a picture:

This Write-Hosts comes from the line:

write-host "Executing $psitem on $database.DatabaseName"

In which you can find the two variables:

$psitem : which is the name of the file that contains the query
$database.DatabaseName : which should be the database name but instead of printing the database name is printing Microsoft.Azure.Commands.Sql.Database.Model.AzureSqlDatabaseModel.DatabaseName

Why one of the two variable is not interpreted?

CodePudding user response：

You need to encapsulate your variable property in a subexpression operator $().

write-host "Executing $psitem on $($database.DatabaseName)"

This is because only simple variables get expanded in an expandable string.

References

Only simple variable references can be directly embedded in an expandable string. Variables references using array indexing or member access must be enclosed in a subexpression.

Source: about_Quoting_Rules

Subexpression operator $( )

Returns the result of one or more statements. For a single result, returns a scalar. For multiple results, returns an array. Use this when you want to use an expression within another expression. For example, to embed the results of command in a string expression.

PS> "Today is $(Get-Date)"
Today is 12/02/2019 13:15:20

PS> "Folder list: $((dir c:\ -dir).Name -join ', ')"
Folder list: Program Files, Program Files (x86), Users, Windows

Source: about_Operators