Building SQL Server Inventory - Professional SQL Server 2008 Administration with Windows PowerShell - page 341

,[totalPhysicalMemory] = @totalPhysicalMemory
,[countryCode] = @countryCode
,[lastBootUpTime] = @lastBootUpTime
,[locale] = @locale
,[OS] = @OS
,[version] = @version
,[servicePackMajorVersion] = @servicePackMajorVersion
,[servicePackMinorVersion] = @servicePackMinorVersion
,[buildNumber] = @buildNumber
,[installDate] = @installDate
,[totalVisibleMemorySize] = @totalVisibleMemorySize
,[totalVirtualMemorySize] = @totalVirtualMemorySize
,[pagingFileSize] = @pagingFileSize
,[IP1] = @IP1
,[IP2] = @IP2
,[IP3] = @IP3
,[IP4] = @IP4
,[IP5] = @IP5
,[IP6] = @IP6
,[updateDate] = GETDATE()
WHERE hostName = @hostName
SET @ERRORCODE = @@ERROR
IF @ERRORCODE <> 0
BEGIN
SET @ERRMSG = 'Insert failed - ' + OBJECT_NAME(@@PROCID)
SET @ERRMSG = @ERRMSG + ' Error Code: ' + RTRIM(CONVERT(CHAR, @ERRORCODE))
RAISERROR (@ERRMSG, 16, 1)
RETURN (-1)
END
ELSE
RETURN (0)
GO
After the Hosts table has been defined and the associated stored procedure has been created, we need
a script, Upsert-Host.ps1 , to insert a SQL Server host record into the inventory when we are about to
install an SQL Server instance on it. This host can be a standalone host or a node in a SQL Server cluster.
After the installation, if you run the script against the same host for the second time, the script will update
the information for the existing host record. The usage of this script is as follows:
Upsert-Host -hostName <string[]> -region <string[]> -location <string[]> -primaryBU
<string[]> [-description <string[]>]
The complete script, which is available for download from the Wrox website for this topic at
www.wrox.com , is as follows:
# ===========================================================
#
# NAME: Upsert-Host.ps1
#
# AUTHOR: Yan and MAK
# DATE : 6/8/2008
#
# COMMENT: This script inserts a SQL Server host record into inventory.