如何将SQL行读取为PowerShell参数

Question

在Azure上，我通过Powershell runbook从100个Azure sql数据库中的一个容器运行多个.sql文件。

我希望Powershell读取服务器名称和数据库名称

状态

Servername	Databasename
服务器-01	DB-01	进程
服务器-01	DB-02	跳过
服务器-02	DB-03	进程

一种方法是将数据库列表加载到DataTable中，并对每个查询的列表进行迭代。根据您的身份验证方法更改下面示例代码中的$databaseListConnectionString，并根据需要设置连接AccessToken。

# get database list
$databaseListConnectionString = "Data Source=YourServer;Initial Catalog=YourDatabase"
$databaseListQuery = "SELECT ServerName, DatabaseName FROM dbo.DatabaseList WHERE Status = 'Process';"
$dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($databaseListQuery, $databaseListConnectionString)
$dataAdapter.SelectCommand.Connection.AccessToken = $access_token
$databaseList = New-Object System.Data.DataTable
[void]$dataAdapter.Fill($databaseList)
# Get the blob container
$blobs = Get-AzStorageContainer -Name $containerName  -Context $ctx | Get-AzStorageBlob 
# Download the blob content to localhost and execute each one 
foreach ($blob in $blobs) {
{
$file = Get-AzStorageBlobContent -Container $containerName -Blob $blob.Name  -Destination "." -Context $ctx 
Write-Output ("Processing file :" + $file.Name)
$query = Get-Content -Path $file.Name
foreach($database in $databaseList.Rows) {
Invoke-Sqlcmd -ServerInstance "$($database.ServerName)" -Database "$($database.DatabaseName)" -Query $query -AccessToken $access_token
Write-Output ("This file is executed :" + $file.Name)
}
}
}