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!

CRM 2011–Managed Solution–Transport Gotchas

Today, I would like to share some gotchas of managed solution while transporting the customization changes on another organization:

1. You cannot activate/deactivate the views and transport the same status on production server via overwriting the managed solution.

2. You cannot setup default public view and transport the same status on production server via overwriting the managed solution.

3. Similarly, you cannot setup default dashboard and transport the same status on production server via overwriting the managed solution.

4. To expand or collapse tabs on forms, like you cannot expand Notes & Activities tab although you tick “Expand this tab by default” on entity form.
In a same manner, you cannot collapse the tabs like General, Notes & Activities etc. although you unchecked “Expand this tab by default”.

5. Start Auditing on any of your entities is not being transported on production server.

The above points are in my knowledge. Please feel free to append this list with your experiences as well.

Dynamics CRM 2011 - Odd behaviour of Product default lookup view on Order Product record

Yesterday, I faced a weird behaviour of order product entity on production server & in fact did not visualize the same during development phase. This is related to a default lookup view of existing product on Order Product entity record. In Dynamics CRM a default lookup view for this entity is “Products in Parent Price List”. Means you will only be allowed to select the products of Price List that was selected on Order record (Parent record). On yesterday I found that the default view was changed to “Product Lookup View” and it now allows you to select products of all Price Lists. Weird…. After goggling for an hour I came to know that this is because I wrote some JavaScript code on change event of this lookup field to do some calculation of pricing based on product selection. So when you write custom code on the OnChange event of such "special" lookups CRM loses its normal behaviour. Workaround

• Create a new temporary unmanaged solution on development organization.
• Add an Order Product entity to this solution.
• Export this solution as unmanaged.
• Unzip the customization files.
• · In customiztaion.xml, change the default view GUID back to “Products in Parent Price List” ({BCC509EE-1444-4A95-AED2-128EFD85FFD5}) by replacing the current GUID of “Product Lookup” ({{8BA625B2-6A2A-4735-BAB2-0C74AE8442A4}}).
• Import the solution back to the development organization & publish the changes.
• You can now validate the default lookup view for Product lookup.
• Now, export your original unmanaged solution as Managed to be imported on production server.

Referred links http://social.msdn.microsoft.com/Forums/en-US/crm/thread/5eac1b84-ca66-41ba-9da7-06bbc49ffdad#e6908e92-e5a5-4d57-bc54-2fcaf393b7e2  http://blogs.msdn.com/b/apurvghai/archive/2012/02/15/exisitng-product-lookup-lists-all-the-products.aspx