Differences

This shows you the differences between two versions of the page.

--- windows:powershell:updateinstance [2013/10/15 20:45] – created haidong
+++ windows:powershell:updateinstance [2018/05/15 03:00] (current) – external edit 127.0.0.1
@@ Line 6: / Line 6: @@
 <code powershell updateInstance.ps1>
-$InstanceList = Invoke-Sqlcmd -Query "exec Windows.Instance_Select_InstanceID_InstanceName" -ServerInstance "sql1" -Database "SysMetrics"
+function updateInstanceSQL($i) {
-$InstanceList | ForEach-Object {
+    $InstanceName = $i.InstanceName
+    $InstanceID = $i.InstanceID
+    $result = Invoke-Sqlcmd -ServerInstance $InstanceName -Query "SELECT SERVERPROPERTY('Edition')"
+    $InstanceEdition = $result.column1
+    $result = Invoke-Sqlcmd -ServerInstance $InstanceName -Query "SELECT SERVERPROPERTY('EditionID')"
+    $InstanceEditionID = $result.column1
+    $result = Invoke-Sqlcmd -ServerInstance $InstanceName -Query "SELECT SERVERPROPERTY('ProductVersion')"
+    $InstanceVersion = $result.column1
+    $result = Invoke-Sqlcmd -ServerInstance $InstanceName -Query "SELECT SERVERPROPERTY('ProductLevel')"
+    $InstanceServicePack = $result.column1
+    if ($InstanceName.Contains("\")) {
+        $serviceName = "mssql`$" + $InstanceName.split("\")[1]
+	$service = Get-WmiObject win32_service -ComputerName $InstanceName.split("\")[0] | where {$_.Name -eq $serviceName}}
+    else {
+	$serviceName = "mssqlserver"
+	$service = Get-WmiObject win32_service -ComputerName $InstanceName | where {$_.Name -eq $serviceName}}
-    $InstanceName = $_.InstanceName
+    $StartupAcct = $service.StartName
-    $InstanceID = $_.InstanceID
+    $sql = "EXEC Windows.Instance_Update $InstanceID, '$InstanceEdition', '$InstanceEditionID', '$InstanceVersion', '$InstanceServicePack', '$StartupAcct'"
+	return $sql}
-        Try {
+$InstanceList = Invoke-Sqlcmd -Query "exec Windows.Instance_Select_InstanceID_InstanceName" -ServerInstance "sql1" -Database "JiMetrics"
-           $result = Invoke-Sqlcmd -ServerInstance $InstanceName -Query "SELECT SERVERPROPERTY('Edition')"
+$InstanceList | ForEach-Object {
-           $InstanceEdition = $result.column1
+     try {
-           $result = Invoke-Sqlcmd -ServerInstance $InstanceName -Query "SELECT
+		 $sql = updateInstanceSQL($_)
-SERVERPROPERTY('EditionID')"
+		 Invoke-Sqlcmd -Query $sql -ServerInstance "sql1" -Database "JiMetrics"}
-           $InstanceEditionID = $result.column1
+	 Catch [Exception] {Continue}}
-           $result = Invoke-Sqlcmd -ServerInstance $InstanceName -Query "SELECT SERVERPROPERTY('ProductVersion')"
-           $InstanceVersion = $result.column1
-           $result = Invoke-Sqlcmd -ServerInstance $InstanceName -Query "SELECT SERVERPROPERTY('ProductLevel')"
-           $InstanceServicePack = $result.column1
-           $sql = "EXEC Windows.Instance_Update $InstanceID,
-'$InstanceEdition', '$InstanceEditionID', '$InstanceVersion', '$InstanceServicePack'"
-           Invoke-Sqlcmd -Query $sql -ServerInstance "sql1" -Database "SysMetrics"
-        }
-    Catch { Return }
-}
 </code>