How to read SQL rows as PowerShell parameters

Question

On Azure, I am running multiple .sql files from a container in 100s of Azure SQL Databases via Powershell runbook.

I want Powershell to read the server name and the database name to run the scripts from my SQL Server table that looks like this:

Servername	Databasename	Status
Server-01	DB-01	Process
Server-01	DB-02	Skip
Server-02	DB-03	Process

In my current version of the Powershell script, it can read the files in the container and run them in a given server and database:

# 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
        Invoke-Sqlcmd -ServerInstance "Server-01.database.windows.net" -Database "DB-01" -Query $query -AccessToken $access_token
        Write-Output ("This file is executed :" + $file.Name) 
    }

I am looking for a method that will read the rows from the table and feed them into the -ServerInstance and -Database fields in the Invoke-Sqlcmd. Ideally it can filter out the Skip rows.

Is your intent to run the same script against each database in the SQL table with Status = 'Process'? — Dan Guzman
– Dan Guzman, Commented Nov 3, 2022 at 10:47
@DanGuzman Yes, same script in multiple databases in multiple servers where it says 'Process' — Strayda
– Strayda, Commented Nov 3, 2022 at 10:50

Dan Guzman · Accepted Answer · 2022-11-03 14:33:31Z

2

One method is to load the database list into a DataTable and iterate over the list for each query. Change the $databaseListConnectionString in the example code below per your authentication method and set the connection AccessToken if/as needed.

# 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)
        }
    }
}

edited Nov 3, 2022 at 14:33

answered Nov 3, 2022 at 11:11

Dan Guzman

46.9k3 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Strayda Over a year ago

Many thanks for the answer. I've been trying this method with my authentication method and failing. The SQL Server - Runbook connection is done via a Private endpoint and I am using an access token for my "Invoke-Sqlcmd" command. Is there a way I can follow a similar way for the "$databaseListConnectionString" here?

Dan Guzman Over a year ago

@Strayda, try adding this line after instantiating the new SqlDataAdapter: $dataAdapter.SelectCommand.Connection.AccessToken = $access_token. I think you can remove ;Integrated Security=SSPI from the connection string with the token.

Strayda Over a year ago

You are a hero! Thank you very much!

Dan Guzman Over a year ago

Thanks for confirming. I'll edited my answer for your use case.

Collectives™ on Stack Overflow

How to read SQL rows as PowerShell parameters

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related