This shows you the differences between two versions of the page.
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> |