SQL Server - Common Table Expressions (CTE)

It's a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. It is not stored as an object and lasts only for the duration of the query. A CTE can be self-referencing and can be referenced multiple times in the same query.

Syntax

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

SELECT <column_list> FROM expression_name;

Multiple cTE

WITH expression_name1 [ ( column_name [,...n] ) ],
     expression_name2 [ ( column_name [,...n] ) ]   

select <column_list> FROM expression_name1
union all
select <column_list> FROM expression_name2

select <column_list> FROM expression_name1 en1 inner join expression_name2 en2 on en1.column_name= en2.column_name

SQL Server - Shortcut to add alias for all the columns

In SSMS, for example

I have a sql query,

select
    BusinessEntityID,
    Bonus,
    SalesLastYear
from
    Sales.SalesPerson

 now  i added alias Sales.SalesPerson sp, want to apply alias name to all the columns quickly, press ALT+Shift button and hold them down while i move my cursor to the lower right of the block.

Now i change to SalesPerson alias name to all column name

select
    SalesPerson.BusinessEntityID,
    SalesPerson.Bonus,
    SalesPerson.SalesYTD,
    SalesPerson.SalesLastYear
from
    Sales.SalesPerson

SQL Server Last executed query history

SELECT

    deqs.last_execution_time AS [Time],

    dest.TEXT AS [Query]

FROM

    sys.dm_exec_query_stats AS deqs

    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

ORDER BY

    deqs.last_execution_time DESC

http://msdn.microsoft.com/en-us/library/ms188068.aspx

 

Dynamics CRM - Passing crm records id and Display report in the "Run on Current Record" menu section

1) Created SSRS report
2) Created 2 DataSets
    i) DSContact

          Query
              declare @sql as nVarchar(max)
              set @sql = 'SELECT fullname, contactid FROM (' + @CRM_FilteredContact+ ') as fc'
              exec(@sql)

          Field
             fullname - fullname
             contactid - contactid
          Parameters
             @CRM_FilteredContact - [@CRM_FilteredContact]

     ii) DSAuditHistory - sql query passing parameters @Contactid [@ContactID]

Dynamic CRM 2011/2013 Audit Report in SSRS

My client want the particular contact entity attributes audit history in the horizontal way SSRS report.
CRM will create one line record in the audit entity for each action (new, update or delete records).
Refer "Dynamics CRM - Audit Entity" - http://makdns.blogspot.com/2014/06/dynamics-crm-audit-entity.html

1)Step created dynamics sql query

declare @contactid uniqueidentifier="00000000-0000-0000-0000-000000000000"> Declare @AuditTable table (id int identity(1,1), AuditId uniqueidentifier, ObjectTypeCode int, AttributeMask varchar(max) null,
        ChangeData varchar(max), CreatedOn datetime)
declare @AuditColumnResult table (id int identity(1,1), rid int null, AuditId uniqueidentifier, Increment int, ColumnKey int,
        ColumnName varchar(max), OldValue varchar(max), NewValue varchar(max), ObjectTypeCode int, createdon datetime)
declare @AuditHisoty table (AuditId uniqueidentifier, Increment int, ColumnKey int, ColumnName varchar(max), Value varchar(max))

Dynamics CRM - Audit Entity

AuditBase table or Audit entity are captured previous data of records.

CRM will create one line record in the audit entity for each action (new, update or delete records).

For example, First Name  Last Name
New Record -  FNC             LNC  => entity record
Old Record -  FN~LN             => audit stored, for example audit id 2, here first name new value is current entity record value  "FNC" and old value is current audit record "FN".
Old Record -  F~L             => audit stored, for example audit id 1, here first name new value is next audit record value  "FN" and old value is current audit record "F"

Convert Dynamics CRM Entity GUID in SSRS

When i create hyperlink i passed  GUID (Fields!contactid.Value) in the query string, the link is not working. I tried to convert GUID to string, still the link is not working.

Finally i used CType function to concatenation with the querystring, it works.

 = Parameters!CRM_URL.Value + "/tools/audit/audit_details.aspx?_CreateFromId=&_CreateFromType=2&id={" & CType(Fields!contactid.Value, GUID).ToString  & "}"

MS Dynamics CRM - Change Grid view background color

My client wants to change the grid view color based on the contact entity address type value is "Ship To". So i added a button in the contact entity "<ribbondiffxml>", but i didn't make it display, you can see there is no command in the "<displayrules>". I added new <customrule> to call the javascript("dns_contact") function ("BackGroundColorGrid") to change the color. Make sure you added jquery in your webresources.

Same way you can change color for the grid.

MS Dynamics CRM - Attach SSRS Report in Email Attachments

This blog will explain to call SSRS report, convert SSRS report content to HTML and place it in the body of the email, same SSRS report will convert to PDF and attached to the email.

It contains 2 class, one is email and call EmailAttachment() function to generate email. Second class name is ReportServerCredentials, it extends IReportServerCredentials, code at the end of blog.

using System.ServiceModel.Description;
using System.Security.Principal;
using System.Net;
using Microsoft.Reporting.WebForms;
using Microsoft.Xrm.Sdk;
using Microsoft.Crm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Client;

MS Dyanmics CRM - Add Notes Attachment in Email

//See the previous blog about how to create new email

GUID emailID = CreateEmail(toUsers, ccUsers, subject, body);
//Pass contactID
Guid contactID = new Guid(paramValue);

private AddNotesAttachmentinEmail(GUID emailID, GUID contactID)
{
    using (XrmServiceContext xsContext = new XrmServiceContext(CRMServiceProxy))
    {
        var qry = from anno in xsContext.AnnotationSet
              where anno.contact_Annotations.contactid == contactID
              && anno.IsDocument == true
              && anno.MimeType != null
              select new
              {
              anno.Subject,
              anno.MimeType,
              anno.FileName,
              anno.DocumentBody
              };

        foreach (var rec in qry)
        {
        ActivityMimeAttachment ama = new ActivityMimeAttachment();
        ama.Subject = rec.Subject;
        ama.ObjectId = new EntityReference("email", emailID);
        ama.ObjectTypeCode = "email";
        ama.FileName = rec.FileName;
        ama.Body = rec.DocumentBody;
        ama.MimeType = rec.MimeType;
        CRMServiceProxy.Create(ama);
        }
    }
}

MS Dynamics CRM - Create Email

List<ActivityParty> ccUsers = CCUsers(string ccEmails);
List<ActivityParty> toUsers = DNSTeamUsers();

CreateEmail(toUsers, ccUsers, subject, body);

//Create Email address, email will be in Draft format.

private Guid CreateEmail(List<ActivityParty> toUsers, List<ActivityParty> ccUsers, string subject, string body)
{
    Guid orgid, userid;
    SystemUserEntity.WhoAmIResponse(out orgid, out userid);

    ActivityParty fromUser = new ActivityParty
    {
    PartyId = new EntityReference(SystemUser.EntityLogicalName, userid)
    };

    Email email = new Email();
    email.From = new ActivityParty[] { fromUser };
  
    if (toUsers!=null)
    email.To = toUsers.ToArray();
  
    if (ccUsers!=null)
    email.Bcc = ccUsers.ToArray();
 
    email.Subject = subject;

    email.Description = body;

    //Assign regarding    
    email.RegardingObjectId = new EntityReference(contact.EntityLogicalName, contactid);
   
    //connect OrganizationServiceProxy
    Guid emailGUID = CRMServiceProxy.Create(email);
    return emailGUID;
}

MS Dynamics CRM for Outlook Client - The server address (URL) is not a Microsoft Dynamics CRM Server

In Outlook Client, i clicked the Configure Microsoft Dynamics CRM for Outlook



i entered the server url, then press "Test Connection" button, i got the "Ther server address (URL) is not ac MS Dynamics CRM Server error.