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
}
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
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.