User Tools

Site Tools


windows:powershell:updateinstance

Differences

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

Link to this comparison view

Next revision
Previous revision
windows:powershell:updateinstance [2013/10/15 15:45]
haidong created
windows:powershell:updateinstance [2014/06/16 20:03]
haidong Refactoring and test cases
Line 6: Line 6:
  
 <code powershell updateInstance.ps1>​ <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>​ </​code>​
windows/powershell/updateinstance.txt · Last modified: 2018/05/14 22:00 (external edit)