Get SQLPS Data in a variable goes very long

Tagged: 

This topic contains 2 replies, has 2 voices, and was last updated by Profile photo of Luca Wirths Luca Wirths 9 months, 3 weeks ago.

  • Author
    Posts
  • #35256
    Profile photo of Luca Wirths
    Luca Wirths
    Participant

    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
    }
    
    
    
    
  • #35259
    Profile photo of Lauras Juozulynas
    Lauras Juozulynas
    Participant

    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
    
  • #35260
    Profile photo of Luca Wirths
    Luca Wirths
    Participant

    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.

You must be logged in to reply to this topic.