This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
windows:powershell:updateinstance [2013/11/04 21:22] haidong Change SysMetrics to JiMetrics |
windows:powershell:updateinstance [2018/05/14 22:00] (current) |
||
---|---|---|---|
Line 6: | Line 6: | ||
<code powershell updateInstance.ps1> | <code powershell updateInstance.ps1> | ||
+ | function updateInstanceSQL($i) { | ||
+ | $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}} | ||
+ | |||
+ | $StartupAcct = $service.StartName | ||
+ | $sql = "EXEC Windows.Instance_Update $InstanceID, '$InstanceEdition', '$InstanceEditionID', '$InstanceVersion', '$InstanceServicePack', '$StartupAcct'" | ||
+ | return $sql} | ||
+ | |||
$InstanceList = Invoke-Sqlcmd -Query "exec Windows.Instance_Select_InstanceID_InstanceName" -ServerInstance "sql1" -Database "JiMetrics" | $InstanceList = Invoke-Sqlcmd -Query "exec Windows.Instance_Select_InstanceID_InstanceName" -ServerInstance "sql1" -Database "JiMetrics" | ||
$InstanceList | ForEach-Object { | $InstanceList | ForEach-Object { | ||
- | + | try { | |
- | $InstanceName = $_.InstanceName | + | $sql = updateInstanceSQL($_) |
- | $InstanceID = $_.InstanceID | + | Invoke-Sqlcmd -Query $sql -ServerInstance "sql1" -Database "JiMetrics"} |
- | + | Catch [Exception] {Continue}} | |
- | Try { | + | |
- | $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 | + | |
- | + | ||
- | $sql = "EXEC Windows.Instance_Update $InstanceID, | + | |
- | '$InstanceEdition', '$InstanceEditionID', '$InstanceVersion', '$InstanceServicePack'" | + | |
- | Invoke-Sqlcmd -Query $sql -ServerInstance "sql1" -Database "JiMetrics" | + | |
- | } | + | |
- | Catch { Return } | + | |
- | } | + | |
</code> | </code> |