How to collect cluster Information using TSQL

Sometime back I was involved in a project which was to collect information for the servers we are supporting – creating inventory of servers. Logging into each server and collecting information is bit tedious when you have hundreds of server in your environment.  I have spent sometime and created a script that does this work for me, however, I still need to connect to the server – but using SSMS/Query Window and execute the script. And then the script will provide me the details I needed.
This inventory should have details like below for cluster and stand alone instances:
Server Name            
OS Name               
OS Edition   
OS Patch Level   
SQL Server IP       
Is Clustered   
Node1_Name        
Node1_IP           
Node2_Name           
Node2_IP           
SQL Server Edition   
SQL Server Patch Level       
Server Time Zone       
SQL Server Version   
SQL Server Platform   
Processor Core       
Physical Memory       
Service Account Name        
Domain               
Looks good ? Below is the version 1 of this script.

/*
IP address portion using : http://www.sqlservercentral.com/Forums/Topic150196-8-1.aspx
REMOVE sp_configure parameters if you are executing this script on SQL Server 2000

Created By : Hemantgiri S. Goswami | http://www.sql-server-citation.com
Date  : 24th March 2013
Version  : 1.0

Tested ON:
Windows Server  >> 2003, 2008, 2008 R2 
SQL Server  >> 2000, 2005, 2008, 2008 R2, 2012 

*/
sp_configure 'show advanced options',1
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'xp_cmdshell',1
RECONFIGURE WITH OVERRIDE
GO
DECLARE @TimeZone   NVARCHAR(100)
  ,@ProductVersion SYSNAME
  ,@PlatForm  SYSNAME
  ,@Windows_Version SYSNAME
  ,@Processors  SYSNAME
  ,@PhysicalMemory SYSNAME
  ,@ServiceAccount SYSNAME
  ,@IPAddress  SYSNAME
  ,@DOMAIN  SYSNAME
  ,@MachineType  SYSNAME
  ,@SQLServerIP  VARCHAR(255)
  ,@CMD   VARCHAR(100)
  ,@Node1   VARCHAR(100)
  ,@Node2   VARCHAR(100)
  ,@Node1IP  VARCHAR(100)
  ,@Node2IP  VARCHAR(100)
  ,@OSEdition  VARCHAR(100)
  ,@OSVersion  VARCHAR(100)
  ,@OSName  VARCHAR(100)
  ,@OSPatchLevel  VARCHAR(100)
   
CREATE TABLE #TempTable
 (
  [Index] VARCHAR(2000),
  [Name] VARCHAR(2000),
  [Internal_Value] VARCHAR(2000),
  [Character_Value] VARCHAR(2000)
 ) ;

INSERT INTO #TempTable
EXEC xp_msver;

-- Replace @Value_Name to N'TimeZoneKeyName' when running on Windows 2008 
EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
       @value_name   = N'StandardName', 
       @value        = @TimeZone output

EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',
       @value_name   = N'ObjectName', 
       @value        = @ServiceAccount output

EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SYSTEM\CurrentControlSet\Control\ProductOptions',
       @value_name   = N'ProductType', 
       @value        = @MachineType output

EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SYSTEM\CurrentControlSet\Services\Tcpip\Parameters',
       @value_name   = N'Domain', 
       @value        = @DOMAIN output
       
EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'CLUSTER\NODES\1',
       @value_name   = N'NodeName', 
       @value        = @Node1 output
       
EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'CLUSTER\NODES\2',
       @value_name   = N'NodeName', 
       @value        = @Node2 output              

EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SOFTWARE\Microsoft\Windows NT\CurrentVersion',
       @value_name   = N'ProductName', 
       @value        = @OSName output              
       
create table #OSEdition (VALUe varchar(255),OSEdition varchar(255), data varchar(100)) 
insert into #OSEdition 
EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SYSTEM\CurrentControlSet\Control\ProductOptions',
       @value_name   = N'ProductSuite'  
SET @OSEdition = (SELECT TOP 1 OSedition  FROM #OsEdition)       
               
EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SOFTWARE\Microsoft\Windows NT\CurrentVersion',
       @value_name   = N'CSDVersion', 
       @value        = @OSPatchLevel output  

set @cmd = 'ping ' + @Node1 
create table #Node1IP (grabfield varchar(255)) 
insert into #Node1IP exec master.dbo.xp_cmdshell @cmd 


set @cmd = 'ping ' + @Node2
create table #Node2IP (grabfield varchar(255)) 
insert into #Node2IP exec master.dbo.xp_cmdshell @cmd 
 
set @cmd = 'ping ' + @@servername
create table #SQLServerIP (grabfield varchar(255)) 
insert into #SQLServerIP exec master.dbo.xp_cmdshell @cmd 

SET  @SQLServerIP=( 
      SELECT substring(grabfield,  charindex('[',grabfield)+1, charindex(']',grabfield)-charindex('[',grabfield)-1) 
      from #SQLServerIP  where left(grabfield,7) = 'Pinging' 
    )
SET  @Node1IP    =(
       SELECT substring(grabfield,  charindex('[',grabfield)+1, charindex(']',grabfield)-charindex('[',grabfield)-1) 
       from #Node1IP  where left(grabfield,7) = 'Pinging' 
     )
       
SET  @Node2IP  =( 
       SELECT substring(grabfield,  charindex('[',grabfield)+1, charindex(']',grabfield)-charindex('[',grabfield)-1) 
       from #Node2IP  where left(grabfield,7) = 'Pinging' 
    )
       
SET  @ProductVersion = (SELECT Character_Value from #TempTable where [INDEX]=2)
SET  @Platform  = (SELECT Character_Value from #TempTable where [INDEX]=4)
SET  @Windows_Version= (SELECT Character_Value from #TempTable where [INDEX]=15)
SET  @Processors  = (SELECT Character_Value from #TempTable where [INDEX]=16)
SET  @PhysicalMemory = (SELECT Character_Value from #TempTable where [INDEX]=19)


SELECT 
  ServerName   = @@SERVERNAME  
 ,OSName    = @OSName
 ,OSEdition   = @OSEdition
 ,OSPatchLevel          = @OSPatchLevel
 ,SQLServerIP          = @SQLServerIP
 ,IsClustered          = SERVERPROPERTY('IsClustered')
 ,Node1_Name   = @Node1
 ,Node1_IP   = @Node1IP 
 ,Node2_Name   = @Node2
 ,Node2_IP   = @Node2IP 
 ,SQLServerEdition         = SERVERPROPERTY('Edition')
 ,SQLServerLevel          = SERVERPROPERTY('ProductLevel')  
 ,ServerTimeZone          = @TimeZone 
 ,SQLServerVersion         = @ProductVersion
 ,SQLServerPlatform         = @PlatForm
 ,ProcessorCore          = @Processors
 ,PhysicalMemory          = @PhysicalMemory 
 ,ServiceAccountName         = @ServiceAccount 
 ,WKS_Server   = @MachineType
 ,Domain    = @DOMAIN
 
GO 
DROP TABLE #Node1IP
DROP TABLE #NODE2IP
DROP TABLE #SQLServerIP
DROP TABLE #TempTable
DROP TABLE #OSEdition 
GO


sp_configure 'xp_cmdshell',0
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'show advanced options',0
RECONFIGURE WITH OVERRIDE
GO

Please do post back your feed back for this script, I will try my best to update and post back new version for this script.
- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

photo credit: Skimaniac via photopin cc

Read More

SQL Server – Generate Calendar using TSQL

Introduction
Recently, I was asked to develop a SSRS based report for the Event Management module in MS Dynamics CRM 2011. The idea was to show a Calendar for the selected month and each cell of the calendar should display the scheduled events of that day.
Showing the events in the required format in each cell was not a big issue. The main challenge was to generate a dynamic grid of Calendar. Luckily, the CRM was deployed on-premises and I got a chance to use TSQL to generate the Calendar.
Implementation
Below is the TSQL which I came up with to generate the Calendar -

DECLARE @Month AS INT = 4 --Set the MONTH for which you want to generate the Calendar.
DECLARE @Year AS INT = 2013 --Set the YEAR for which you want to generate the Calendar.
--Find and set the Start & End Date of the said Month-Year
DECLARE @StartDate AS DATETIME = CONVERT(VARCHAR, @Year) + RIGHT('0' + CONVERT(VARCHAR, @Month), 2) + '01'
DECLARE @EndDate AS DATETIME = DATEADD(DAY, - 1, DATEADD(MONTH, 1, @StartDate));

WITH Dates
AS (
 SELECT @StartDate Dt
 
 UNION ALL
 
 SELECT DATEADD(DAY, 1, Dt)
 FROM Dates
 WHERE DATEADD(DAY, 1, Dt) <= @EndDate
 ),
Details
AS (
 SELECT DAY(Dt) CDay,
  DATEPART(WK, Dt) CWeek,
  MONTH(Dt) CMonth,
  YEAR(Dt) CYear,
  DATENAME(WEEKDAY, Dt) DOW,
  Dt
 FROM Dates
 )
--Selecting the Final Calendar
SELECT Sunday,
 Monday,
 Tuesday,
 Wednesday,
 Thursday,
 Friday,
 Saturday
FROM (
 SELECT CWeek,
  DOW,
  CDay
 FROM Details
 ) D
PIVOT(MIN(CDay) FOR DOW IN (
   Sunday,
   Monday,
   Tuesday,
   Wednesday,
   Thursday,
   Friday,
   Saturday
   )) AS PVT
ORDER BY CWeek

Output:





Hope, this will help!

Read More