Inventorying SQL Servers on your network

TABLE OF CONTENTS    Methods to inventory SQL Servers    Reading registered SQL Servers in Enterprise Manager    Inventorying SQL Server information    Inventory script 1    Inventory script 2

Methods to inventory SQL Servers

Return to Table of Contents

There are several ways to enumerate SQL Servers on your network. Each of these methods has its own limitations as the below table illustrates.

Method	Source	Reads CNU	Instances	Lists Offline Servers	Spurious (local)
isql –L	SQL Server	Yes	No	No	No
osql –L	SQL Server	Yes	Yes	Yes	Yes
EnumSQLSvr	SQLDev.net	No	No	No	No
ListSQLSvr	SQLDev.net	Yes	Yes	Yes	Yes
ListAvailableSQLServers()	SQLDMO	Yes	Yes	Yes	Yes

Methods isql and osql are command-line utilities that ship with SQL Server. isql uses DBLib and it is not instance aware. It does a local broadcast to discover SQL Servers on the network, so it does not return a list of offline servers. osql consults the domain master browser for a list of SQL Servers that have announced themselves on the network. It does not return a list of offline servers nor does it return a list of SQL Server instances. osql will return a spurious server listed as local in addition to your local SQL Servers' netbios names, which can be annoying. Both isql and osql will include servers listed in your Client Network Utility (CNU) applet. EnumSQLSvr and ListSQLSvr are utilities available at SQLDev.net, owned by Gert Drapers, program manager in Microsoft's SQL Server Development Group. EnumSQLSvr uses DBlib and it is not instance aware. It does not read the CNU and, as it uses a broadcast, it does not list offline SQL Servers. ListSQLSvr does read the CNU, it is instance aware and it consults the local domain master browser to return a list of SQL Servers that are offline and online. The local domain master browser builds a list of all the computers (including SQL Servers) on the local segment or subnet. It communicates with other domain master browsers, which builds lists of all the computers on their local subnets. These master browsers swap lists to build complete lists of all the computers on a local domain. They do not contain lists of computers on other domains. ListAvailableSQLServers() is a SQLDMO method that does the same local master browser lookup used by ListSQLSvr and osql –L. Code Sample 1 illustrates its usage. Code Sample 1 Set oSQLApp =CreateObject("SQLDMO.Application") Set oNames = oSQLApp.ListAvailableSQLServers() For i = 1 To oNames.Count wscript.echo oNames.Item(i) Next

Reading registered SQL Servers in Enterprise Manager

Return to Table of Contents

The problem with all of the above methods is that they only return SQL Servers on your local segment -- which, for the most part, means they can only enumerate the SQL Servers on your subnet or read the list maintained by the local domain master browser. Each method discussed above will only return SQL Servers that are part of your local domain or workgroup. For SQL Servers that are not part of your domain, you may want to read servers registered in Enterprise Manager on your desktop, server or remote server list. To do so, connect to Enterprise Manager, click on Microsoft SQL Servers, then click Tools, Options and, in the Server Registration Option, notice "Read From Remote Server" selection.

Inventorying SQL Server information

Return to Table of Contents

Now that we know how to build lists of all the SQL Servers running on your network, we need to collect information on them, like version and service pack information. Please see Script 1 below, which consults a list of SQL Servers stored in a table called ServerList and builds a batch file called SQLServer.cmd. You build this list using the above methods. Also see Script 2, which inventories these SQL Servers and stores the information in a table called SQLServers. The schema for ServerList is simply the following:

Create Table ServerList
(
ServerName varchar(50)
)

The schema for SQLServers is as follows.
CREATE TABLE dbo.SQLServers (
 ServerName varchar (50) NOT NULL CONSTRAINT PKSQLServers Primary Key,
 ServerID int IDENTITY (1, 1) NOT NULL ,
 AnsiNulls bit NULL ,
 AutoReconnect bit NULL ,
 AutoStart bit NULL ,
 BlockingTimeout int NULL ,
 CodePage int NULL ,
 Collation varchar (100) NULL ,
 CommandTerminator varchar (10) NULL ,
 HostName varchar (100) NULL ,
 InstanceName varchar (100) NULL ,
 IsClustered bit NULL ,
 IsFullTextInstalled bit NULL ,
 Language varchar (50) NULL ,
 LoginTimeout int NULL ,
 MaxNumericPrecision int NULL ,
 NetName varchar (50) NULL ,
 NetPacketSize int NULL ,
 ODBCPrefix bit NULL ,
 ProductLevel varchar (10) NULL ,
 QueryTimeout int NULL ,
 QuotedIdentifier bit NULL ,
 RegionalSetting bit NULL ,
 Adsp bit NULL ,
 AgentLogFile varchar (100) NULL ,
 AutostartDTC bit NULL ,
 AutostartLicensing bit NULL ,
 AutostartMail bit NULL ,
 AutostartServer bit NULL ,
 BackupDirectory varchar (100) NULL ,
 CaseSensitive bit NULL ,
 CharacterSet varchar (50) NULL ,
 ErrorLogPath varchar (100) NULL ,
 MailAccountName varchar (100) NULL ,
 MailPassword varchar (100) NULL ,
 MasterDBPath varchar (100) NULL ,
 NP varchar (50) NULL ,
 NTEventLogging bit NULL ,
 NumberOfProcessors int NULL ,
 PerfMonMode int NULL ,
 PhysicalMemory int NULL ,
 RegisteredOrganization varchar (100) NULL ,
 RegisteredOwner varchar (100) NULL ,
 ReplicationInstalled bit NULL ,
 RpcEncrypt bit NULL ,
 RpcList bit NULL ,
 RpcMaxCalls int NULL ,
 RpcMinCalls int NULL ,
 SNMP bit NULL ,
 SNMPExtensionAgents bit NULL ,
 SNMPExtensionAgentsData varchar (100) NULL ,
 SortOrder varchar (250) NULL ,
 SpxFlag bit NULL ,
 SpxPort int NULL ,
 SQLCurrentVersion varchar (50) NULL ,
 SQLDataRoot varchar (100) NULL ,
 SQLRootPath varchar (100) NULL ,
 SuperSocketEncrypt bit NULL ,
 SuperSocketList varchar (50) NULL ,
 TapeLoadWaitTime int NULL ,
 TcpFlag bit NULL ,
 TcpPort int NULL ,
 ViaListenInfo varchar (50) NULL ,
 ViaRecognizedVendors varchar (100) NULL ,
 ViaVendor varchar (100) NULL ,
 ServiceName varchar (100) NULL ,
 StartupAccount varchar (100) NULL ,
 Status varchar (10) NULL ,
 TranslateChar bit NULL ,
 TrueName varchar (100) NULL ,
 VersionMajor int NULL ,
 VersionMinor int NULL ,
 VersionString varchar (300) NULL 
) ON PRIMARY
GO

This collection allows you to create a repository of information for the SQL Servers on your network. You can query this to determine which servers are below a service pack level or determine the patch level of individual SQL Servers.

Inventory Script 1

Return to Table of Contents

option explicit

dim objConnection, objRecordSet, ConnectionString, objFso, LogFileName, 
CommandFile
set objConnection = CreateObject("ADODB.Connection")
set objRecordSet=CreateObject("ADODB.RecordSet")
set objFso=CreateObject(" Scripting.FileSystemObject")

LogFileName="c:\SQLServer.cmd"

ConnectionString="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security 
Info=False;Data Source=.;Initial Catalog=ServerInventory;" 
objConnection.ConnectionString = ConnectionString
objConnection.Open 

set CommandFile=objFSO.CreateTextFile (LogFileName,true)

objRecordSet.Open "Select * from ServerList", objConnection, 3, 1

do while not objRecordSet.Eof
wscript.echo objRecordSet(0)
CommandFile.WriteLine "SQLServer.vbs " & objRecordSet(0) & " > c:\LogFiles\" & 
objRecordSet(0) & ".txt" 
objRecordSet.MoveNext
loop

Inventory Script 2

Return to Table of Contents

option explicit 

Const adCmdText = 1
Const adExecuteNoRecords = 128 

dim objSQLServer, objConnection, objCommand, strInsert, strUpdate, AnsiNulls, Adsp, 
RegisteredOrganization, RpcMaxCalls, RpcMinCalls, SpxFlag
dim SpxPort, ConnectionString, SuperSocketEncrypt, ServerName

dim objRecordSet

set objSQLServer = CreateObject("SQLDMO.SQLServer")
set objConnection = CreateObject("ADODB.Connection")
set objCommand = CreateObject("ADODB.Command")
set objRecordSet=CreateObject(" ADODB.RecordSet")

ServerName = wscript.arguments(0)

objSQLServer.LoginSecure = "true"
objSQLServer.Connect ServerName

ConnectionString="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security 
Info=False;Data Source=.;Initial Catalog=ServerInventory;" 
objConnection.ConnectionString = ConnectionString
objConnection.Open 

objRecordSet.Open "Select * from SQLServers where ServerName='" & 
ServerName  & "'", objConnection, 3, 1

wscript.echo objRecordSet.RecordCount

if objRecordSet.RecordCount = 0 then
 wscript.echo "server not in list, inserting"
 Insert
else
 wscript.echo "server in list, updating"
 Update
end if 

objRecordSet.Close

set objSQLServer = Nothing
set objRecordSet = Nothing
set objCommand = Nothing
set objConnection = Nothing


Sub Insert
strInsert = strInsert & "insert into SQLServers (ServerName, AnsiNulls, AutoReconnect, 
AutoStart, BlockingTimeout, CodePage, Collation, CommandTerminator,"
strInsert = strInsert & "HostName, InstanceName, IsClustered, IsFullTextInstalled, 
Language, LoginTimeout, MaxNumericPrecision, NetName, NetPacketSize, " 
strInsert = strInsert & "ODBCPrefix, ProductLevel, QueryTimeout, QuotedIdentifier, 
RegionalSetting, Adsp, AgentLogFile, AutostartDTC, AutostartLicensing, "
strInsert = strInsert & "AutostartMail, AutostartServer, BackupDirectory, CaseSensitive, 
CharacterSet, ErrorLogPath, MailAccountName, MailPassword, " 
strInsert = strInsert & "MasterDBPath, NP, NTEventLogging, NumberOfProcessors,
PerfMonMode, PhysicalMemory, RegisteredOrganization, RegisteredOwner, "
strInsert = strInsert & "ReplicationInstalled, RpcEncrypt, RpcMaxCalls, RpcMinCalls,
 SNMP, SNMPExtensionAgents, " 
strInsert = strInsert & "SNMPExtensionAgentsData, SortOrder, SpxFlag, SpxPort, 
SQLCurrentVersion, SQLDataRoot, SQLRootPath, "
strInsert = strInsert & "SuperSocketEncrypt, SuperSocketList, TapeLoadWaitTime, 
TcpFlag, TcpPort, ViaListenInfo, ViaRecognizedVendors, ViaVendor, " 
strInsert = strInsert & "ServiceName, StartupAccount, Status, TranslateChar, "
strInsert = strInsert & "TrueName, VersionMajor, VersionMinor, VersionString) values ('"
strInsert = strInsert & ServerName & "'," 
if isnull(objSQLServer.AnsiNulls) then AnsiNulls=false else AnsiNulls=true end if
strInsert = strInsert & CInt(AnsiNulls) & ","
strInsert = strInsert & CInt(objSQLServer.AutoReconnect) & "," & CInt( objSQLServer.AutoStart) & ","
strInsert = strInsert & objSQLServer.BlockingTimeout & "," & objSQLServer.CodePage 
& ",'"
strInsert = strInsert & objSQLServer.Collation & "','"  & objSQLServer.CommandTerminator & "','"
strInsert = strInsert & objSQLServer.HostName & "','" & objSQLServer.InstanceName & "',"
strInsert = strInsert & CInt( objSQLServer.IsClustered) & "," & CInt(objSQLServer.IsFullTextInstalled) & ",'"
strInsert = strInsert & objSQLServer.Language & "'," & objSQLServer.LoginTimeout & "," 
strInsert = strInsert & objSQLServer.MaxNumericPrecision & ",'" & 
objSQLServer.NetName & "',"
strInsert = strInsert & objSQLServer.NetPacketSize & "," & 
CInt( objSQLServer.ODBCPrefix) & ",'"
strInsert = strInsert & objSQLServer.ProductLevel & "'," & 
objSQLServer.QueryTimeout & ","
strInsert = strInsert & CInt(objSQLServer.QuotedIdentifier ) & "," & 
CInt(objSQLServer.RegionalSetting) & ","
if isnull(objSQLServer.Registry.Properties("Adsp")) then Adsp=false else Adsp=true end if
strInsert = strInsert & CInt(Adsp) & ",'"  & 
objSQLServer.Registry.Properties("AgentLogFile") & "',"
strInsert = strInsert & CInt(objSQLServer.Registry.Properties("AutostartDTC")) & ","& 
CInt(objSQLServer.Registry.Properties ("AutostartLicensing")) & ","
strInsert = strInsert & CInt(objSQLServer.Registry.Properties("AutostartMail")) & "," & 
CInt(objSQLServer.Registry.Properties("AutostartServer")) & ",'"  
strInsert = strInsert & objSQLServer.Registry.Properties("BackupDirectory") & "'," & 
CInt(objSQLServer.Registry.Properties("CaseSensitive")) & ",'"
strInsert = strInsert & objSQLServer.Registry.Properties("CharacterSet") & "','"  & 
objSQLServer.Registry.Properties("ErrorLogPath") & "','"
strInsert = strInsert & objSQLServer.Registry.Properties ("MailAccountName") & "','" & 

objSQLServer.Registry.Properties("MailPassword") & "','"

strInsert = strInsert & objSQLServer.Registry.Properties("MasterDBPath") & "','" & 

objSQLServer.Registry.Properties("NP") & "',"
strInsert = strInsert & CInt(objSQLServer.Registry.Properties("NTEventLogging")) & "," & 

objSQLServer.Registry.Properties ("NumberOfProcessors") & ","

strInsert = strInsert & objSQLServer.Registry.Properties("PerfMonMode") & "," &
objSQLServer.Registry.Properties("PhysicalMemory") & ",'" 
if objSQLServer.VersionMajor =7 then
 if isnull(objSQLServer.Registry.Properties("RegisteredOrganization")) then 
  RegisteredOrganization =" " 
 else 
  RegisteredOrganization=objSQLServer.Registry.Properties ("RegisteredOrganization") 
 end if 
else
 RegisteredOrganization=" "
end if 
strInsert = strInsert & RegisteredOrganization & "','"
strInsert = strInsert & objSQLServer.Registry.Properties ("RegisteredOwner") & "',"
strInsert = strInsert & CInt(objSQLServer.Registry.Properties("ReplicationInstalled")) & ","
strInsert = strInsert & CInt(objSQLServer.Registry.Properties ("RpcEncrypt")) & ","
if isnull(objSQLServer.Registry.Properties("RpcMaxCalls")) then RpcMaxCalls=0 else RpcMaxCalls=1 end if
strInsert = strInsert & RpcMaxCalls & "," 
if isnull(objSQLServer.Registry.Properties("RpcMinCalls")) then RpcMinCalls=0 else RpcMinCalls=1 end if 
strInsert = strInsert & RpcMinCalls & ","
strInsert = strInsert & Cint(objSQLServer.Registry.Properties ("SNMP")) & ","
strInsert = strInsert & Cint(objSQLServer.Registry.Properties("SNMPExtensionAgents")) & ",'"
strInsert = strInsert & objSQLServer.Registry.Properties ("SNMPExtensionAgentsData") & "','"
strInsert = strInsert & objSQLServer.Registry.Properties("SortOrder") & "',"
if isnull(objSQLServer.Registry.Properties("SpxFlag")) then SpxFlag = 0 else SpxFlag = 1 end if 
strInsert = strInsert & SpxFlag & ","
if isnull(objSQLServer.Registry.Properties("SpxPort")) then SpxPort = 0 else SpxPort = 1 end if 
strInsert = strInsert & SpxPort & ",'" 
strInsert = strInsert & objSQLServer.Registry.Properties("SQLCurrentVersion") & "','"
strInsert = strInsert & objSQLServer.Registry.Properties("SQLDataRoot") & "','" 
strInsert = strInsert & objSQLServer.Registry.Properties("SQLRootPath") & "',"
if isnull(objSQLServer.Registry.Properties("SuperSocketEncrypt")) then
 SuperSocketEncrypt = 0 else SuperSocketEncrypt = 1 
strInsert = strInsert & SuperSocketEncrypt & ",'"
strInsert = strInsert & objSQLServer.Registry.Properties("SuperSocketList") & "',"
strInsert = strInsert & objSQLServer.Registry.Properties ("TapeLoadWaitTime") & ","
strInsert = strInsert & CInt(objSQLServer.Registry.Properties("TcpFlag")) & ","
strInsert = strInsert & objSQLServer.Registry.Properties ("TcpPort") & ",'"
strInsert = strInsert & objSQLServer.Registry.Properties("ViaListenInfo") & "','"
strInsert = strInsert & objSQLServer.Registry.Properties("ViaRecognizedVendors") & "','" 
strInsert = strInsert & objSQLServer.Registry.Properties("ViaVendor") & "','"
strInsert = strInsert & objSQLServer.ServiceName & "','"
strInsert = strInsert & objSQLServer.StartupAccount & "','"
Select Case objSQLServer.Status
Case 0
  strInsert = strInsert & "Unknown" & "',"
Case 1
  strInsert = strInsert & "Running" & "'," 
Case 2 
  strInsert = strInsert & "Paused" & "',"
Case 3 
  strInsert = strInsert & "Stopped" & "',"
Case 4
  strInsert = strInsert & "Starting" & "'," 
Case 5 
  strInsert = strInsert & "Stopping" & "',"
Case 6 
  strInsert = strInsert & "Continuing" & "',"
Case 7 
  strInsert = strInsert & "Pausing" & "'," 
End Select 
strInsert = strInsert & CInt(objSQLServer.TranslateChar) & ",'"
strInsert = strInsert & objSQLServer.TrueName & "',"
strInsert = strInsert & objSQLServer.VersionMajor & ","
strInsert = strInsert & objSQLServer.VersionMinor & ",'"
strInsert = strInsert & objSQLServer.VersionString & "')"
wscript.echo strInsert

objCommand.ActiveConnection = objConnection
objCommand.CommandText = strInsert
objCommand.CommandType = adCmdText
objCommand.Execute , , adExecuteNoRecords

end sub

sub Update
strUpdate = strUpdate & " Update SQLServers set AnsiNulls= "
if isnull(objSQLServer.AnsiNulls) then AnsiNulls=false else AnsiNulls=true end if
strUpdate = strUpdate & Cint(AnsiNulls) & "," 
strUpdate = strUpdate & " AutoReconnect = " & CInt(objSQLServer.AutoReconnect) & ", "
strUpdate = strUpdate & " AutoStart = " & CInt(objSQLServer.AutoStart) & "," 
strUpdate = strUpdate & " BlockingTimeout =" & objSQLServer.BlockingTimeout & "," 
strUpdate = strUpdate & " CodePage =" & objSQLServer.CodePage & "," 
strUpdate = strUpdate & " Collation = '" & objSQLServer.Collation & "', "
strUpdate = strUpdate & " CommandTerminator = '" & objSQLServer.CommandTerminator & "'," 
strUpdate = strUpdate & " HostName ='" &  objSQLServer.HostName & "',"  
strUpdate = strUpdate & " InstanceName ='" & objSQLServer.InstanceName & "'," 
strUpdate = strUpdate & " IsClustered = "  & CInt(objSQLServer.IsClustered) & "," 
strUpdate = strUpdate & " IsFullTextInstalled = " & CInt(objSQLServer.IsFullTextInstalled ) & ","
strUpdate = strUpdate & " Language ='" & objSQLServer.Language & "'," 
strUpdate = strUpdate & " LoginTimeout = " & objSQLServer.LoginTimeout & "," 
strUpdate = strUpdate & " MaxNumericPrecision = " & objSQLServer.MaxNumericPrecision & ","
strUpdate = strUpdate & " NetName ='" & objSQLServer.NetName & "',"  
strUpdate = strUpdate & " NetPacketSize = " & objSQLServer.NetPacketSize & "," 
strUpdate = strUpdate & " ODBCPrefix = " & CInt(objSQLServer.ODBCPrefix) & "," 
strUpdate = strUpdate & " ProductLevel = '" & objSQLServer.ProductLevel & "'," 
strUpdate = strUpdate & " QueryTimeout = " & objSQLServer.QueryTimeout & "," 
strUpdate = strUpdate & " QuotedIdentifier = " & CInt(objSQLServer.QuotedIdentifier) & "," 
strUpdate = strUpdate & " RegionalSetting =" & CInt(objSQLServer.RegionalSetting ) & ","

if isnull(objSQLServer.Registry.Properties("Adsp")) then Adsp=false else Adsp=true end if

strUpdate = strUpdate & " Adsp =" & CInt(Adsp) & "," 
strUpdate = strUpdate & " AgentLogFile = '" & objSQLServer.Registry.Properties("AgentLogFile") & "'," 
strUpdate = strUpdate & " AutostartDTC = " & CInt(objSQLServer.Registry.Properties("AutostartDTC")) & ","
strUpdate = strUpdate & " AutostartLicensing = " & CInt( objSQLServer.Registry.Properties("AutostartLicensing")) & ","
strUpdate = strUpdate & " AutostartMail = " & CInt(objSQLServer.Registry.Properties("AutostartMail")) & "," 
strUpdate = strUpdate & " AutostartServer = " & CInt(objSQLServer.Registry.Properties("AutostartServer")) & "," 
strUpdate = strUpdate & " BackupDirectory = '" & objSQLServer.Registry.Properties("BackupDirectory") & "'," 
strUpdate = strUpdate & " CaseSensitive = "& CInt(objSQLServer.Registry.Properties("CaseSensitive")) & "," 
strUpdate = strUpdate & " CharacterSet = '" & objSQLServer.Registry.Properties("CharacterSet") & "',"  
strUpdate = strUpdate & " ErrorLogPath = '" & objSQLServer.Registry.Properties ("ErrorLogPath") & "'," 
strUpdate = strUpdate & " MailAccountName = '" & objSQLServer.Registry.Properties("MailAccountName") & "'," 
strUpdate = strUpdate & " MailPassword ='" & objSQLServer.Registry.Properties("MailPassword") & "'," 

strUpdate = strUpdate & " MasterDBPath ='" & objSQLServer.Registry.Properties("MasterDBPath") & "'," 
strUpdate = strUpdate & " NP = '" & objSQLServer.Registry.Properties("NP") & "'," 
strUpdate = strUpdate & " NTEventLogging = " & CInt(objSQLServer.Registry.Properties ("NTEventLogging")) & "," 
strUpdate = strUpdate & " NumberOfProcessors = " & objSQLServer.Registry.Properties("NumberOfProcessors") & "," 
strUpdate = strUpdate & " PerfMonMode = " & objSQLServer.Registry.Properties("PerfMonMode") & "," 
strUpdate = strUpdate & " PhysicalMemory =" & objSQLServer.Registry.Properties("PhysicalMemory") & "," 

if objSQLServer.VersionMajor =7 then
 if isnull(objSQLServer.Registry.Properties("RegisteredOrganization")) then 
  RegisteredOrganization =" " 
 else 
  RegisteredOrganization=objSQLServer.Registry.Properties ("RegisteredOrganization") 
 end if 
else
 RegisteredOrganization=" "
end if 

strUpdate = strUpdate & " RegisteredOrganization ='" & RegisteredOrganization & "'," 
strUpdate = strUpdate & " RegisteredOwner ='" & objSQLServer.Registry.Properties ("RegisteredOwner") & "',"
strUpdate = strUpdate & " ReplicationInstalled = " & 
CInt(objSQLServer.Registry.Properties("ReplicationInstalled")) & "," 
strUpdate = strUpdate & " RpcEncrypt =" & CInt(objSQLServer.Registry.Properties("RpcEncrypt"))  & ","

if isnull(objSQLServer.Registry.Properties("RpcMaxCalls")) then RpcMaxCalls=0 else RpcMaxCalls=1 end if

strUpdate = strUpdate & " RpcMaxCalls =" &  RpcMaxCalls & ","
if isnull(objSQLServer.Registry.Properties("RpcMinCalls")) then RpcMinCalls=0 else RpcMinCalls=1 end if 

strUpdate = strUpdate & " RpcMinCalls =" &  RpcMinCalls & ","
strUpdate = strUpdate & " SNMP =  " & Cint(objSQLServer.Registry.Properties("SNMP")) & "," 
strUpdate = strUpdate & " SNMPExtensionAgents  = " & 
Cint(objSQLServer.Registry.Properties("SNMPExtensionAgents")) & ","
strUpdate = strUpdate & " SNMPExtensionAgentsData = '" & 
objSQLServer.Registry.Properties("SNMPExtensionAgentsData") & "',"
strUpdate = strUpdate & " SortOrder ='" & objSQLServer.Registry.Properties("SortOrder") & "'," 

if isnull(objSQLServer.Registry.Properties("SpxFlag")) then SpxFlag = 0 else SpxFlag = 1 end if 
strUpdate = strUpdate & " SpxFlag = " & SpxFlag & ","
if isnull(objSQLServer.Registry.Properties ("SpxPort")) then SpxPort = 0 else SpxPort = 1 end if 
strUpdate = strUpdate & " SpxPort = "  & SpxPort & ","
strUpdate = strUpdate & " SQLCurrentVersion = '" & objSQLServer.Registry.Properties("SQLCurrentVersion") & "',"
strUpdate = strUpdate & " SQLDataRoot ='" & objSQLServer.Registry.Properties("SQLDataRoot") & "'," 
strUpdate = strUpdate & " SQLRootPath = '" & objSQLServer.Registry.Properties("SQLRootPath") & "',"
if isnull(objSQLServer.Registry.Properties("SuperSocketEncrypt")) then 
SuperSocketEncrypt = 0 else SuperSocketEncrypt = 1 
strUpdate = strUpdate & " SuperSocketEncrypt =" & SuperSocketEncrypt & ","
strUpdate = strUpdate & " SuperSocketList ='" & objSQLServer.Registry.Properties("SuperSocketList") &"'," 
strUpdate = strUpdate & " TapeLoadWaitTime =" & objSQLServer.Registry.Properties("TapeLoadWaitTime") &","
strUpdate = strUpdate & " TcpFlag =" & CInt(objSQLServer.Registry.Properties ("TcpFlag")) &","
strUpdate = strUpdate & " TcpPort =" & objSQLServer.Registry.Properties("TcpPort") &","
strUpdate = strUpdate & " ViaListenInfo = '" & objSQLServer.Registry.Properties("ViaListenInfo") &"',"
strUpdate = strUpdate & " ViaRecognizedVendors ='" & objSQLServer.Registry.Properties("ViaRecognizedVendors") & "'," 
strUpdate = strUpdate & " ViaVendor ='" & objSQLServer.Registry.Properties("ViaVendor") & "',"
strUpdate = strUpdate & " ServiceName ='" & objSQLServer.ServiceName & "',"
strUpdate = strUpdate & " StartupAccount = '" & objSQLServer.StartupAccount & "'," 
Select Case objSQLServer.Status
Case 0
  strUpdate = strUpdate & " Status='" & "Unknown" & "'," 
Case 1
  strUpdate = strUpdate & " Status='" &  "Running" & "',"
Case 2 
  strUpdate = strUpdate & " Status='" & "Paused" & "'," 
Case 3 
  strUpdate = strUpdate & " Status='" & "Stopped" & "',"
Case 4
  strUpdate = strUpdate & " Status='" & "Starting" & "'," 
Case 5 
  strUpdate = strUpdate & " Status='" & "Stopping" & "',"
Case 6 
  strUpdate = strUpdate & " Status='" & "Continuing" & "'," 
Case 7 
  strUpdate = strUpdate & " Status='" & "Pausing" & "',"
End Select 
strUpdate = strUpdate & " TranslateChar = " & CInt(objSQLServer.TranslateChar ) & ","
strUpdate = strUpdate & " TrueName = '" & objSQLServer.TrueName & "',"
strUpdate = strUpdate & " VersionMajor = " & objSQLServer.VersionMajor & "," 
strUpdate = strUpdate & " VersionMinor = " & objSQLServer.VersionMinor & ","
strUpdate = strUpdate & " VersionString = '" & objSQLServer.VersionString  & "'" 
strUpdate = strUpdate & " where ServerName='" & ServerName & "'"

wscript.echo strUpdate

About the author: Hilary Cotter has been involved in IT for more than 20 years as a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and subsequently studied both economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and is currently working on books on merge replication and Microsoft search technologies. More information from SearchSQLServer.com Book Excerpts: A first look at SQL Server 2005 for Developers: Chapter 6, 'Security' Step-by-Step Guide: How to patch SQL Server Downloads: To access free Windows IT software downloads related to SQL Server visit our Download Library

Rate this Tip To rate tips, you must be a member of SearchSQLServer.com. Register now to start rating these tips. Log in if you are already a member. Digg This!     StumbleUpon     Del.icio.us    RELATED CONTENT SQL Server Security Meet compliance requirements with improved database security practices Hardening the network and OS for SQL Server security Securing the server and database in SQL Server SQL Server security made simple and sensible Blog: Protect your databases from the internal threat Setting up SQL Server Service Broker for secure communication The keys to database backup protection for SQL Server Understanding transparent data encryption in SQL Server 2008 The fine line between not encrypting your databases and breach notification Securing SQL Server with access control, login monitoring and DDL triggers Database Management and Administration Meet compliance requirements with improved database security practices Hardening the network and OS for SQL Server security Securing the server and database in SQL Server How SQL Server 2008 components impact SharePoint implementations Troubleshooting Distributed Transaction Coordinator errors in SQL Server Achieving high availability and disaster recovery with SharePoint databases Clearing the Windows page file and its effect on server performance Deploying a SQL Server virtual appliance for Microsoft Hyper-V How to create SQL Server virtual appliances for Hyper-V Push vs. pull: Configuring SQL Server replication RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary data corruption  (SearchSQLServer.com) data hiding  (SearchSQLServer.com) RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.