Recently, I had an opportunity to work with one of our enterprise client's to integrate data between a web portal and Microsoft CRM 2013 using Microsoft BizTalk Server
2013. I’ve often used the following two SQL queries to validate and map data between the source system and Microsoft CRM.
The first query will return all CRM entities:
SELECT DISTINCT(EntityView.Name) AS EntityName FROM LocalizedLabelView AS LocalizedLabelView_2 INNER JOIN AttributeView ON LocalizedLabelView_2.ObjectId = AttributeView.AttributeId RIGHT OUTER JOIN EntityView INNER JOIN LocalizedLabelView AS LocalizedLabelView_1 ON EntityView.EntityId = LocalizedLabelView_1.ObjectId ON AttributeView.EntityId = EntityView.EntityId WHERE LocalizedLabelView_1.ObjectColumnName = 'LocalizedName' ORDER BY EntityName
|
The second query will return all attributes that relate to
the provided entity:
SELECT EntityView.Name AS EntityName, AttributeView.Name AS AttributeName, LocalizedLabelView_2.Label AS AttributeDisplayName FROM LocalizedLabelView AS LocalizedLabelView_2 INNER JOIN AttributeView ON LocalizedLabelView_2.ObjectId = AttributeView.AttributeId RIGHT OUTER JOIN EntityView INNER JOIN LocalizedLabelView AS LocalizedLabelView_1 ON EntityView.EntityId = LocalizedLabelView_1.ObjectId ON AttributeView.EntityId = EntityView.EntityId WHERE LocalizedLabelView_1.ObjectColumnName = 'LocalizedName' AND EntityView.Name IN ('Account') ORDER BY AttributeName
|
2009-2017 Phidiax, LLC - All Rights Reserved