Get SQLPS Data in a variable goes very long

PowerShell Help
1

Hi

I have to do a powershell that return values from the SQL-Server Settings.
If i run the sqlps part on the server, then i get the result after about 2 sec. But if i wanna store them in a variable, it takes about an hour. Does anyone have an idea why it takes so long?

Here is the code.
$a should be the array where all data is stored

Thanks

 $a = sqlps.exe {
    CD \sql\localhost\
    $Instanzen = dir
    $return = @{}
    foreach($inst in $Instanzen){
        $pschildd = $inst.pschildname
        $srv = get-item $pschildd
        $srvInfo = $srv.Information
        $nodename = $srvInfo.netname
        $osversion = $srvInfo.OSVersion
        $Processor = "" 
        $countProcessor= $srvInfo.Processors
        $sqlV=  $srvInfo.ResourceVersionString                    
        $sqlsp= $srvInfo.productlevel
        $Instanz =  $srv
        $serverColl =  $srvInfo.Collation           
        $dbs=$srv.Databases
        $TotRam =  $srvInfo.PhysicalMemory               
        $minRam =  $srv.Configuration.MinServerMemory.ConfigValue
        $maxRam = $srv.Configuration.MaxServerMemory.ConfigValue
        
        $return.$pschildd += ($nodename,$osversion,$Processor,$countProcessor,$sqlV,$sqlsp,$Instanz, $serverColl, $dbs, $TotRam,$minram, $maxRam)
    }
    return $return
}
2

Hi,

Can’t say exact reason, but what I did I just stopped using “sqlps.exe” and used “import-module sqlps”.
Works quite fast, though I have only one instance on my test SQL server.
Here is what I have changed:

#$a = sqlps.exe {                             #--removed this line
    import-module sqlps                    #--importing sqlps modules instead of calling sqlps.exe
    #CD \sql\localhost\              #--removed this because for me path is \sql\"servername"\
    $localhost = hostname                # -- get server name
    $sqlpath = "\sql\${localhost}\"       #-- Form a path
    cd $sqlpath                               #-- Go to path
    $Instanzen = dir
    $return = @{}
    foreach($inst in $Instanzen){
        $pschildd = $inst.pschildname
        $srv = get-item $pschildd
        $srvInfo = $srv.Information
        $nodename = $srvInfo.netname
        $osversion = $srvInfo.OSVersion
        $Processor = "" 
        $countProcessor= $srvInfo.Processors
        $sqlV=  $srvInfo.ResourceVersionString                    
        $sqlsp= $srvInfo.productlevel
        $Instanz =  $srv
        $serverColl =  $srvInfo.Collation           
        $dbs=$srv.Databases
        $TotRam =  $srvInfo.PhysicalMemory               
        $minRam =  $srv.Configuration.MinServerMemory.ConfigValue
        $maxRam = $srv.Configuration.MaxServerMemory.ConfigValue
        
        $return.$pschildd += ($nodename,$osversion,$Processor,$countProcessor,$sqlV,$sqlsp,$Instanz, $serverColl, $dbs, $TotRam,$minram, $maxRam)
    }
    return $return
#}                                            # --removed because of first line removal
3

Hi Lauras,

Thanks a lot. Works perfect.

I tryed for debug, to export all to a file before the return. Works normal. But i think there is a time-out in the return, no idea where.
Anyway it works now.
Have a nice day.