Suppose I have a table
I have the following script
Function Query($Query) {
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$Server;Initial Catalog=$Database;Integrated Security=SSPI"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandText = $Query
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$a=$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0] }
$Result = Query "SELECT [databasename], [servertypeA] from table GROUP BY [databasename], [servertypeA]"
$Servers = @()
$DB = @()
foreach($row in $Result)
{
$i++
$DB += $row.Item("databasename")
$Servers += $row.Item("servertypeA") #here i want to also store for servertypeB (so that Server array would store server1,serverx,servery and in next iteration server2,serverx,servery)
cmd /c "PS.bat $somescript.ps1 $($Servers[-1]) $($DB[-1])"
}
I would like to add servertypeB to my $Servers array, something like this:
$Servers += $row.Item("servertypeA") + $row.Item("servertypeB")
so that $Server
array would store server1,serverx,servery
and in next iteration server2,serverx,servery
I am not sure if that is possible, and there is another issue: I would have to include servertypeB
in the $Result
query:
$Result = Query "SELECT [databasename], [servertypeA], [servertypeB] from table GROUP BY [databasename], [servertypeA]"
but wouldnt groupby
select only one row because servertypeA
and databasename
are duplicated so it would pick distinct row, which means servery
would be left out? and if I remove groupby then its gonna select server1 and server2 twice which i dont want to have as duplicate
I was suggested an answer as following:
SELECT [databasename], ServerTypeName =[servertypeA], ServerTypeGroup='A' from table GROUP BY [databasename], [servertypeA]
UNION
SELECT [databasename], ServerTypeName =[servertypeB], ServerTypeGroup='B' from table GROUP BY [databasename], [servertypeB]
But i dont know how to integrate that with the script i have. am i supposed to use the identifiers in row.item?