PowerShell - ItemArray

Question

PowerShell - ItemArray

ReamerXXVI

Ten Centuries

Points: 1379
More actions
November 11, 2020 at 4:59 am

#3806670
Can anyone please explain to me what .ItemArray does in PowerShell?
I read the doco but I don't understand it.
I have an array of SQL instances using mixed names i.e. server.domain, server.domain\sqlinstance, server.domain\sqlinstance, portnumber
I used a foreach loop to pass the items in the array to an Invoke-Sqlcmd statement. I couldn't connect to any of the instances until I created a new array from the original one using .ItemArray.
To make things a bit clearer, this is the code:
```
$InstancesToCheck = Invoke-Sqlcmd -Query "SELECT [InstanceName] FROM [mgmtdb].[dbo].[hostlist]" -ServerInstance "XXX\YYY"

$Servers = $InstancesToCheck.ItemArray

foreach($SQLInstance in $Servers){   

    Invoke-Sqlcmd -Query "SELCET name FROM sys.databases" -ServerInstance "$SQLInstance"

}
```

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16017 More actions · Answer 1

you do need to practice more powershell - and try out the combination of commands that you have available to look at the objects returned - main thing is to play around with code and google for examples of multiple ways to achieve the same result.

It is time consuming sometimes to learn but it is well worth it on the long run.

with regards to your particular example no need for the intermediary conversion to an array.

$InstancesToCheck = Invoke-Sqlcmd -Query "SELECT [InstanceName] FROM [mgmtdb].[dbo].[hostlist]" -ServerInstance "XXX\YYY"


foreach($SQLInstance in $InstancesToCheck){   

    Invoke-Sqlcmd -Query "SELECT name FROM sys.databases" -ServerInstance "$($SQLInstance.InstanceName)"

}

Note that I enclosed the variable $SQLInstance.InstanceName with a $() - this is because parsing the variable within quotes would consider "$SQLInstance" alone without expanding the property you are after.

ReamerXXVI Ten Centuries Points: 1379 More actions · Answer 2

Thanks Frederico I've had a play and done some more research online so I have a better understanding now, and you're right I do need some more PowerShell practice.

I'm still not sure what .ItemArray does though ?!?!?!

frederico_fonseca SSCoach Points: 16017 More actions · Answer 3

as per documentation (https://docs.microsoft.com/en-us/dotnet/api/system.data.datarow.itemarray?view=net-5.0) it converts each column of a row onto a array of objects - and for each row on the recordset being retrieved it adds each column to the array.

its usage is quite limited in my opinion

so a select like the one below which retrieves 5 rows with 3 columns each generates a 15 entry array

$InstancesToCheck = Invoke-Sqlcmd -Query "SELECT top 5 name, case when name = 'sysrscols' then null else name end as name2, object_id FROM master.sys.objects" -ServerInstance "*** ADD INSTANCE NAME HERE***"
$itemarray = $InstancesToCheck.ItemArray
write-host ItemArray
$itemarray
Write-Host InstancesToCheck
$InstancesToCheck

# to see the type of array we can use the Get-Member (GM) to return the types.
# to see it correctly formatted issue the command below manually after running the content above on powershell_ise
# $itemarray|gm

issuing the $itemarray|gm command above gives the following output (some lines removed)

   TypeName: System.String

Name             MemberType            Definition                                                                                    
----             ----------            ----------                                                                                    
Clone            Method                System.Object Clone(), System.Object ICloneable.Clone()                                       
CompareTo        Method                int CompareTo(System.Object value), int CompareTo(string strB), int IComparable.CompareTo(S...

   TypeName: System.DBNull

Name          MemberType Definition                                                                                                  
----          ---------- ----------                                                                                                  
Equals        Method     bool Equals(System.Object obj)                                                                              
GetHashCode   Method     int GetHashCode()                                                                                           

   TypeName: System.Int32

Name        MemberType Definition                                                                                                    
----        ---------- ----------                                                                                                    
CompareTo   Method     int CompareTo(System.Object value), int CompareTo(int value), int IComparable.CompareTo(System.Object obj),...
Equals      Method     bool Equals(System.Object obj), bool Equals(int obj), bool IEquatable[int].Equals(int other)

ReamerXXVI Ten Centuries Points: 1379 More actions · Answer 4

Thanks excellent, thanks very much for the detailed reply.