O/S Configurations from SQL Server

The T-SQL code snippet belows returns details regarding the Operating System using the NETSH utility. The details returned range from the Windows Directory to the Service Pack Version and everything in between.

CREATE TABLE #os
(
token NVARCHAR(100) NULL
)

INSERT INTO #os (token)
EXEC   master.dbo.xp_cmdshell ‘netsh diag SHOW os /p’

SELECT  LTRIM(SUBSTRING(token, 1, CHARINDEX(‘=’, token)-1)) AS property,
SUBSTRING(token, CHARINDEX(‘=’, token)+1, 100) AS value
FROM  #os
WHERE  token LIKE ‘%=%’

DROP TABLE #os

 

SQLDMO to List SQL Server Configs

The SQLDMO Script below will list all of the SQL Server configuration options and their config_value and run_value just as per the sp_configure Stored Procedure:

Dim oSQLServer
Dim cv
Set oSQLServer = CreateObject(“SQLDMO.SQLServer”)

oSQLServer.LoginSecure = True
oSQLServer.Connect “(local)”

With oSQLServer.Configuration
.ShowAdvancedOptions = True
For Each cv In .ConfigValues
Msgbox cv.Name & “, ” & cv.RunningValue & “, ” & cv.CurrentValue
Next
End With

oSQLServer.DisConnect
Set oSQLServer = Nothing

Determine the Members of Each ServerRole

The VBScript extract below will determine the members of each SQL Server ServerRole for a list of servers provided in an input file.

Option Explicit

 

‘ File containing list of servers to check

Const SERVER_LIST = “c:\serverlist.txt”

‘ File to write the results to

Const OUTPUT = “c:\output.txt”

Const ForReading = 1

Const ForWriting = 2

 

Dim oFSO

Set oFSO = CreateObject(“Scripting.FileSystemObject”)

 

Dim oSQLServer

Set oSQLServer = CreateObject(“SQLDMO.SQLServer”)

 

oSQLServer.LoginSecure = True

 

Dim oFSORead

Set oFSORead = oFSO.OpenTextFile(SERVER_LIST, ForReading, False)

 

Dim oFSOWrite

Set oFSOWrite = oFSO.OpenTextFile(OUTPUT, ForWriting, True)

 

Dim i

Dim srvRole

Dim server

Dim results

 

‘ Loop through each server in the Serverlist file

Do While Not oFSORead.AtEndOfStream

server = oFSORead.ReadLine

 

oSQLServer.Connect server

 

‘ For each SQL Server ServerRole determine the members of each role

‘ and write the results to the Output file in a comma delimited format

‘ ie. servername, serverrole, login

For Each srvRole In oSQLServer.ServerRoles

Set results = srvRole.EnumServerRoleMember

For i = 1 To results.Rows

oFSOWrite.WriteLine server & “, ” & srvRole.Name & “, ” & results.GetColumnString(i, 1)

Next

Next

 

oSQLServer.DisConnect

Loop

 

oFSORead.Close

 

Set oFSO = Nothing

Set oSQLServer = Nothing

Random Records from a Table

If you have ever had the need to return random records from a table such a sample data for test cases then the following T-SQL statement may come in handy.

SELECT TOP 10 * FROM northwind.dbo.orders ORDER BY NEWID()

 
This query will return ten random records from the Northwind Orders table.

As an aside a sort column in an ORDER BY can be specified as a name or column alias, an expression, or a nonnegative integer representing the position of the name, alias, or expression in the select list.

What Service Pack is That

A commonly asked question is how to identify the SQL Server Service Pack that is installed. It is possible to identify what version is being run based on the build number returned from the @@VERSION configuration function.

ie.
SELECT @@VERSION
Retruns:
Microsoft SQL Server  2000 – 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: )

The following KB article details how to do determine the Service Pack level from the build number http://support.microsoft.com/default.aspx?scid=kb;en-us;q321185 .

However one of the often overlooked function is the ProductLevel property name of the SERVERPROPERY function.

SELECT SERVERPROPERTY(‘ProductLevel’)
Returns:
——–
SP3
(1 row(s) affected)