CODEWITHSUNDEEP
sql-serverpowershellpowershell-2.0
Saikiran Paramkusham
Saikiran Paramkusham

Reputation: 89

Variable value not showing up

After executing the following script I'm unable to view the value of the property name in the $demo1 variable, but for the variable $demo I'm able to see the property values for all the properties. Can anyone help me with this one?

Note: Both the variables are of same type (Selected.System.Data.DataRow).

$demo.drive is working, $demo1.name or $demo1.log_size_in_mb or $demo1.db_size_in_mb is not working.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.server')
$db = New-Object ('Microsoft.SqlServer.Management.Smo.Database')

$db = $serverinstance.Databases.Item("Master")

$ds = $db.ExecuteWithResults("xp_fixeddrives")

$ds1 = $db.ExecuteWithResults("select dbid
,d.name
,d.compatibility_level
,d.recovery_model_desc
,convert(decimal(18,2),(sum(size)*8)/1024.0) as db_size_in_mb
,(select (size*8)/1024.0 from sys.sysaltfiles where dbid=saf.dbid and groupid=0) as log_size_in_mb
from sys.sysaltfiles saf
join sys.databases d on saf.dbid=d.database_id
where groupid>0
group by dbid,d.name,d.compatibility_level,d.recovery_model_desc")

for ($i=0; $i -lt $ds.Tables.Count; $i++) {
    $res = $ds.Tables[$i]
}

for ($i=0; $i -lt $ds1.Tables.Count; $i++) {
    $res1 = $ds1.Tables[$i]
}

$demo1 = @()
$demo  = $res | select PSComputerName, Drive, 'MB Free'
$demo1 = $res1 | select Name, recovery_model_desc, db_size_in_mb, log_size_in_mb

$demo1.name

Upvotes: 0

Views: 228

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200503

You're using PowerShell v2 and $demo1 contains an array (multiple table rows). Automatic unrolling (the ability to access properties or methods of array elements by calling the property or method on the array object) was introduced with PowerShell v3.

In PowerShell v2 $array.Name will try to get the value of a property Name of the array object itself. Since the array object doesn't have such a property this returns $null.

What you need to do is get the desired property of the individual array elements, for instance like this:

$demo1 | Select-Object -Expand Name

or like this:

$demo1 | ForEach-Object { $_.Name }

Upvotes: 1

Related Questions