Error when adding more servers to execute query in Powershell

Question

I m able to execute this powershell scripts when i just put a single server but when i add two servers or more , i m getting error as below . Does anyone have idea on this ? Fyi, this sql query will select data from a multiple database which have same table and insert into another table in different server

$tableName = "dbo.a_category"
$tableName2 = "dbo.b_category"
$SQLServer ="DB01","DB02"

$databases = invoke-sqlcmd -ServerInstance $SQLServer -Database "master" -Query "select name from sys.databases where name like '%cms%'"

foreach ($database in $databases)
{
    $DBname = $database.name
    #Execute scripts
    $dataset = Invoke-DbaQuery -SqlInstance $SQLServer -Database $database.name -file "C:\queryset\copy.sql" -As DataSet
$dataset | Write-DbaDbTableData -SqlInstance "DB15" -Database testdb -Table $tableName2

    $database.name
}

Answer 1

Hi,

According to the help file of the Invoke-Sqlcmd cmdlet, the "-ServerInstance" parameter only accepts a PSObject, not an array.

Please refer to this link for more detals.

Invoke-Sqlcmd

Best Regards,

Ian Xue

---

If the Answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.