CODEWITHSUNDEEP
powershell
Cataster
Cataster

Reputation: 3551

Why are items in the array being skipped?

I have a table

table

I have the following script

$Result = Query "SELECT [databasename], [servertypeA], [servertypeB] from table GROUP BY [databasename], [servertypeA], [servertypeB]"

$Servers = @()
$DB = @()

foreach($row in $Result)
{   
    $i++
    $DB += $row.Item("databasename")
    $Servers += $row.Item("servertypeA")
    $Servers += $row.Item("servertypeB")

    cmd /c "PS.bat $somescript.ps1 $($Servers[-1]) $($DB[-1])"
}

this results in:

db1 serverx
db1 servery
db2 serverx
db2 servery

it should be

db1 server1
db1 serverx
db1 servery
db2 server2
db2 serverx
db2 servery

why is servertypeA being skipped, i.e. server1 and server2 in $Server?

I also tried this

$Result = Query "SELECT distinct [databasename], [servertypeA], [servertypeB] from table"

and its generating the same result

Upvotes: 0

Views: 52

Answers (3)

FSCKur
FSCKur

Reputation: 1070

Simples! In your loop you are doing 2 additions to the $Servers array, but only one call to your external script, in which you pass in only the last item in the $Servers array.

If you can forgive me straying beyond the strict question you have asked, how come you have elected to call Powershell, from cmd.exe, from Powershell? This would be a more usual approach:

foreach($row in $Result)
{   
    $i++
    $DB = $row.Item("databasename")
    $Servers = $row.Item("servertypeA"), $row.Item("servertypeB")

    & "$somescript.ps1" @($Servers) $DB
}

The value of eliminating the round-trip through cmd.exe is in:

  • saving wasted processes
  • ability to debug
  • ability to capture all streams
  • helpful error messages

I'm assuming that you define $somescript elsewhere. The exact syntax of the & call will depend on whether you've nicely defined your param block - it's always good to have something like this:

param
(
    [string[]]$Servers
    [string]$DB
)

EDIT

Since you've clarified what you need to pass in to $somescript, try this:

foreach($row in $Result)
{   
    $DB = $row.Item("databasename")
    & "$somescript.ps1" $row.Item("servertypeA") $DB
    & "$somescript.ps1" $row.Item("servertypeB") $DB
}

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 416169

The big change is to the query, to get the results you want, but note this also let me greatly simplify the loop:

$Result = Query "SELECT DISTINCT [databasename], [serverTypeA] As Server from table  UNION SELECT DISTINCT [databasename], [serverTypeB] from table"

foreach($row in $Result)
{   
    $DB = $row.Item("databasename")
    $Server = $row.Item("Server")

    cmd /c "PS.bat $somescript.ps1 $Server $DB"
}

Upvotes: 1

Hiten004
Hiten004

Reputation: 2491

replace this below code

$DB += $row.Item("databasename")
$Servers += $row.Item("servertypeA")
$Servers += $row.Item("servertypeB")

with 

$DB += $row.Item("databasename")
$Servers += $row.Item("servertypeA")
$DB += $row.Item("databasename")
$Servers += $row.Item("servertypeB")

Upvotes: 1

Related Questions