COLLECTING HOST MACHINE 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 )

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!

Dynamics CRM 2011 - Propagate the plug-in invalid exception message in JavaScript code.

Recently I came across an issue where I would like to propagate the plug-in InvalidPluginExcecutionException message via JavaScript code. Based on the client requirements I need to trigger a plug-in on click of custom ribbon button on the Opportunity entity. Hence on custom button click I have written a web service call that updates one of custom attributes of an Opportunity entity. So, when system will update this field via client side web service call, the registered plug-in will be triggered automatically. Usually your InvalidPluginExcecutionException message inside your plug-in code is displayed to user in a standard CRM message box. In my case I am unable to see the same because the plug-in is being triggered via JavaScript web service call. Below is the code snippet that executes my query.

//Asynchronous AJAX function to update a CRM record using OData

  $.ajax({

    type: "POST",

    contentType: "application/json; charset=utf-8",

    datatype: "json",

    data: jsonEntity,

    url: serverUrl + ODATA_ENDPOINT + "/" + odataSetName + "(guid'" + id + "')",

    beforeSend: function (XMLHttpRequest) {

      //Specifying this header ensures that the results will be returned as JSON.             

      XMLHttpRequest.setRequestHeader("Accept", "application/json");

      //Specify the HTTP method MERGE to update just the changes you are submitting.             

      XMLHttpRequest.setRequestHeader("X-HTTP-Method", "MERGE");

    },

    success: function (data, textStatus, XmlHttpRequest) {

      //The MERGE does not return any data at all, so we'll add the id 

      //onto the data object so it can be leveraged in a Callback. When data 

      //is used in the callback function, the field will be named generically, "id"

      data = new Object();

      data.id = id;

      if (successCallback) {

        successCallback(data, textStatus, XmlHttpRequest);

      }

    },

    error: function (XmlHttpRequest, textStatus, errorThrown) {

      if (errorCallback) {

        errorCallback(XmlHttpRequest, textStatus, errorThrown);

      }

      else {

        ErrorHandler(XmlHttpRequest, textStatus, errorThrown);

      }

    }

  }); 

I have validated all the 3 objects i.e. XmlHttpRequest, textStatus and errorThrown inside errorCallback method to seek the plug-in error message with no success. However I can see the same error message via fiddler!!
Finally, I found a way to parse the response text of xmlHttpRequest and get the exact error message. Below is the code that works for me to alert the plug-in message from client side code.

function ErrorHandler(xmlHttpRequest, textStatus, errorThrown) {

  alert(JSON.parse(xmlHttpRequest.responseText).error.message.value);

}

I hope this will be helpful.

SQL Server – Generating PERMUTATIONS using T-Sql

Were you ever asked to generate string Permutations using TSql? I was recently asked to do so, and the logic which I could manage to come up at that point is shared in the below script.

DECLARE @Value AS VARCHAR(20) = 'ABCC' --Mention the text which is to be permuted

DECLARE @NoOfChars AS INT = LEN(@Value)

DECLARE @Permutations TABLE(Value VARCHAR(20)) --Make sure the size of this Value is equal to your input  string length (@Value)

;WITH NumTally AS (--Prepare the Tally Table to separate each character of the Value.

  SELECT 1 Num

  UNION ALL

  SELECT 

    Num + 1 

  FROM 

    NumTally 

  WHERE 

    Num < @NoOfChars

),Chars AS ( --Separate the Characters

SELECT

  Num,

  SUBSTRING(@Value,Num,1) Chr

FROM

  NumTally  

)

--Persist the Separated characters.

INSERT INTO @Permutations

SELECT Chr FROM Chars

--Prepare Permutations

DECLARE @i AS INT = 1

WHILE(@i < @NoOfChars)

BEGIN

  --Store the Permutations

  INSERT INTO @Permutations

  SELECT DISTINCT --Add DISTINCT if required else duplicate Permutations will be generated for Repeated  Chars.

    P1.Value + P2.Value

  FROM 

    (SELECT Value FROM @Permutations WHERE LEN(Value) = @i) P1 

  CROSS JOIN 

    (SELECT Value FROM @Permutations WHERE LEN(Value) = 1) P2

  --Increment the Counter.      

  SET @i += 1  

  --Delete the Incorrect Lengthed Permutations to keep the table size under control.

  DELETE FROM @Permutations WHERE LEN(Value) NOT IN (1,@i)

END

--Delete InCorrect Permutations.

SET @i = 1

WHILE(@i <= @NoOfChars)

BEGIN

  --Deleting Permutations which has not used "All the Chars of the given Value".

  DELETE 

  FROM 

    @Permutations

  WHERE

    Value NOT LIKE '%' + SUBSTRING(@Value,@i,1) +'%'

  --Deleting Permutations which have repeated incorrect character.  

  DELETE 

  FROM 

    @Permutations

  WHERE

    LEN(Value) - LEN(REPLACE(Value,SUBSTRING(@Value,@i,1),'')) != 

    LEN(@Value) - LEN(REPLACE(@Value,SUBSTRING(@Value,@i,1),''))

  SET @i += 1  

END

--Selecting the generated Permutations. 

SELECT Value FROM @Permutations

Hope, this script helps!

Please share your suggestions if you have any to improve this logic.

Steps to change Server URL/Machine name after deploying Dynamics CRM 2011

Recently, I came across a situation where I needed to rename my development machine having MS Dynamics CRM 2011 & SQL server 2008 installed on it. If you change the machine name casually then you will not be able to browse any of the existing organizations. Also, the CRM Asynchronous service won’t be started. Ideally, there are only 3 simple steps, which if followed correctly, can bring all Organizations again up & running with a new url. Below are the steps – Word of Caution: Please take backups of your registry and Database before making any change to them or take help of the concerned Administrator to help you. 1. Rename the Machine

• Rename the Machine and change it IP Address if required.
• Restart the machine.

2. Update the Registry entry

• Open the Registyr Editor using - Start > Run > Regedit.exe
• Then Move to KEY_LOCAL_MACHINE\Software\Microsoft\MSCRM node and
• Double Click on configdb key and change the DataSource from OldServerName to NewServerName
• Double Click on ServerUrl key and change the URL to point to the NewServerName

3. Update the details in MSCRM_CONFIG database

• Open SQL Server Management Studio and run the below script -

USE [MSCRM_CONFIG]

GO

UPDATE [Server] SET [Name] = 'NewServerName';

UPDATE ConfigSettings SET [HelpServerUrl] = 'http://NewServerName:5555/';

UPDATE Organization SET

[ConnectionString] = REPLACE([ConnectionString],'OldServerName ','NewServerName’),

[SqlServerName] = '

NewServerName ',

[SrsUrl] = '

http://NewServerName /reportserver';

• Restart the machine.

Disclaimer: Following of the above steps is purely the reader’s sole decision and in no case the author could be held responsible for loss of any kind arising after following.
Referred Link:
http://weblogs.asp.net/navaidakhtar/archive/2012/03/09/Microsoft-Dynamics-CRM-2011-_2F00_-4.0-Configuration-in-Case-of-Machine-rename-_2F00_-CRM-Database-server-change-_2F00_-Domain-Controller-Change.aspx

SQL Server – TSql to find Records matching certain criteria in all the tables of a DB.

Generally, we try to find out records matching a certain criteria from a single or few tables. However, there are times when we need to find out records matching a criteria from all the tables of a SQL Database and today I will explain you a simple way to retrieve those records.

Recently, I was asked by my colleague, who was working on a MS Dynamics CRM migration project, to let him know the records which were created after a particular date in the source. So that, he could analyze only those records and strategize the Migration process.

I quickly opened up the SSMS and came up with the below script -

USE <DBName> --Replace this with the actual DBName

Go

DECLARE @ColumnName AS VARCHAR(50) = 'CreatedOn' --The name of the column on which you need to put the criteria

DECLARE @Criteria AS VARCHAR(50) = 'CONVERT(DATE,' + @ColumnName + ') >= ''20130225''' -- The Actual criteria/WHERE Clause of the query

--The below will list the TSQL Statements which could be copied & executed in a separate query window.

SELECT 

  'IF EXISTS(SELECT 1 FROM ' + T.name + ' WHERE ' + @Criteria + ') ' +

  'SELECT ''' + T.name + ''' TableName, * FROM ' + T.name + ' WHERE ' + @Criteria 

FROM 

  sys.columns C

INNER JOIN sys.tables T

  ON T.object_id = C.object_id   

WHERE 

  C.name = @ColumnName

The above script will list down the SELECT statements which could be copied and executed in a separate query window connecting to the same Database. On execution, you will get the list of records from each table base on the specified criteria.

Hope, this helps!