• Login
• Become a member
• RSS
• Part of the TechTarget network

 
As a SQL Server DBA, you must not only be concerned with the SQL Servers you manage, but also with the non-production machines running Microsoft SQL Server 2000 Desktop and Personal editions.

For instance, you may be vigilant about making sure the

latest patches are applied to a SQL Server, but one of your developer's laptops may be running MSDE unpatched for weeks or months on end. Before you know it, you could be afflicted with worms or viruses, like the Slammer virus that damaged so many unpatched SQL Servers in 2003.

In this tip I'll examine several methods to discover all the SQL Servers running on your network, and we will look at a script that does minimal inventory of your SQL Servers. The following table of contents will help you navigate.

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

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

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

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

 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

 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

 

---

 

Join the conversationComment

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.

All Rights Reserved,Copyright 2005 - 2013, TechTarget