serving the solutions day and night


Monday, June 16, 2014

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.


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


( 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,


 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


SQL Server Last executed query history

    deqs.last_execution_time AS [Time],
    dest.TEXT AS [Query]
    sys.dm_exec_query_stats AS deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
    deqs.last_execution_time DESC

Thursday, June 12, 2014

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
              declare @sql as nVarchar(max)
              set @sql = 'SELECT fullname, contactid FROM (' + @CRM_FilteredContact+ ') as fc'
             fullname - fullname
             contactid - contactid
             @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" -

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"

Wednesday, June 11, 2014

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  & "}"

Tuesday, June 3, 2014

Asset Managment Concept for HTML5

If you load a same file multiple times. Each load will hit disk/web, and load into memory.
This can become a large performance burden as each from disk retrieve can have performance costs.
So, we need fast and flexibility to help us get larger amounts of data into memory. If a file had been loaded in memory already, so that we can return the in memory version,instead of going back to hitting the disk/web/multiple times.It is called an asset loading manager concept.

File I/O functions in JavaScript are by definition asynchronous.

Assest Managment Need
1)Cached assest management
2)Async Callbacks on load
3)Batched assest Loading