SQL

by

RootComponent types in the solution.xml file in Dynamics CRM 2011

You can check what valid RootComponent types in the solution.xml are by running the following SQL query in CRM database. These are valid components of CRM solution.


SELECT SM.AttributeValue AS Type, SM.Value AS Description
FROM StringMap (NOLOCK) SM
WHERE SM.AttributeName = 'componenttype'
AND SM.ObjectTypeCode = 7103

You will see RootComponents in solution.XML under RootComponents tag. Some of the examples given below.

<RootComponent type="1" schemaName="account" />
<RootComponent type="9" schemaName="componentstate" />
<RootComponent type="20" id="{0317be9c-d4f7-e111-8df7-005056912a77}" />
<RootComponent type="29" id="{028b2b03-b97d-4f1d-ba4a-2869b9961884}" />
<RootComponent type="36" id="{07b94c1d-c85f-492f-b120-f0a743c540e6}" />
<RootComponent type="37" id="{77ecdfda-3e70-429d-b174-1352b981c5f2}" />
<RootComponent type="38" id="{49331898-0752-4670-ba0a-3ce95c6c88cd}" />
<RootComponent type="39" id="{1133f868-d749-1033-a46c-0019b9204ed3}" />
<RootComponent type="50" schemaName=":RibbonDiffXml" />
<RootComponent type="60" id="{2578569a-af22-e211-b1fc-0050569176bd}" />
<RootComponent type="61" schemaName="new_CommonLibrary" />
<RootComponent type="62" />
<RootComponent type="63" id="{01857a9d-32c1-e111-b3aa-005056912a77}" />
<RootComponent type="70" id="{3b89c550-de8f-e211-b442-005056910270}" />
<RootComponent type="91" schemaName="Plugins, Version=1.0.0.0, Culture=neutral, PublicKeyToken=adcb72a31fd3810b" />
<RootComponent type="92" id="{056320de-38a4-e111-876e-0050569102cb}" />
Type Description
1 Entity
2 Attribute
3 Relationship
4 Attribute Picklist Value
5 Attribute Lookup Value
6 View Attribute
7 Localized Label
8 Relationship Extra Condition
9 Option Set
10 Entity Relationship
11 Entity Relationship Role
12 Entity Relationship Relationships
13 Managed Property
20 Role
21 Role Privilege
22 Display String
23 Display String Map
24 Form
25 Organization
26 Saved Query
29 Workflow
31 Report
32 Report Entity
33 Report Category
34 Report Visibility
35 Attachment
36 Email Template
37 Contract Template
38 KB Article Template
39 Mail Merge Template
44 Duplicate Rule
45 Duplicate Rule Condition
46 Entity Map
47 Attribute Map
48 Ribbon Command
49 Ribbon Context Group
50 Ribbon Customization
52 Ribbon Rule
53 Ribbon Tab To Command Map
55 Ribbon Diff
59 Saved Query Visualization
60 System Form
61 Web Resource
62 Site Map
63 Connection Role
70 Field Security Profile
71 Field Permission
90 Plugin Type
91 Plugin Assembly
92 SDK Message Processing Step
93 SDK Message Processing Step Image
95 Service Endpoint
Advertisement
by

Entity Type Codes in Dynamics CRM 2011

You can retrieve the type codes for all entities by running the following SQL Query in CRM database.


select * from EntityView order by ObjectTypeCode

Type codes below 10000 are reserved for Microsoft CRM systementities. Custom entities have a value greater than or equal to 10,000. Note that custom entity object type codes may change during import and are not guaranteed to be the same between systems.

The following table shows the type codes for system entities.

Entity Name ObjectTypeCode
Account 1
Contact 2
Opportunity 3
Lead 4
Annotation 5
BusinessUnitMap 6
Owner 7
SystemUser 8
Team 9
BusinessUnit 10
PrincipalObjectAccess 11
RolePrivileges 12
SystemUserLicenses 13
SystemUserPrincipals 14
SystemUserRoles 15
AccountLeads 16
ContactInvoices 17
ContactQuotes 18
ContactOrders 19
ServiceContractContacts 20
ProductSalesLiterature 21
ContactLeads 22
TeamMembership 23
LeadCompetitors 24
OpportunityCompetitors 25
CompetitorSalesLiterature 26
LeadProduct 27
RoleTemplatePrivileges 28
Subscription 29
FilterTemplate 30
PrivilegeObjectTypeCodes 31
SalesProcessInstance 32
SubscriptionSyncInfo 33
SubscriptionTrackingDeletedObject 35
ClientUpdate 36
SubscriptionManuallyTrackedObject 37
TeamRoles 40
PrincipalEntityMap 41
SystemUserBusinessUnitEntityMap 42
PrincipalAttributeAccessMap 43
PrincipalObjectAttributeAccess 44
PrincipalObjectAccessReadSnapshot 90
RecordCountSnapshot 91
Incident 112
Competitor 123
DocumentIndex 126
KbArticle 127
Subject 129
BusinessUnitNewsArticle 132
ActivityParty 135
UserSettings 150
ActivityMimeAttachment 1001
Attachment 1002
InternalAddress 1003
CompetitorAddress 1004
CompetitorProduct 1006
Contract 1010
ContractDetail 1011
Discount 1013
KbArticleTemplate 1016
LeadAddress 1017
Organization 1019
OrganizationUI 1021
PriceLevel 1022
Privilege 1023
Product 1024
ProductAssociation 1025
ProductPriceLevel 1026
ProductSubstitute 1028
SystemForm 1030
UserForm 1031
Role 1036
RoleTemplate 1037
SalesLiterature 1038
SavedQuery 1039
StringMap 1043
UoM 1055
UoMSchedule 1056
SalesLiteratureItem 1070
CustomerAddress 1071
SubscriptionClients 1072
StatusMap 1075
DiscountType 1080
KbArticleComment 1082
OpportunityProduct 1083
Quote 1084
QuoteDetail 1085
UserFiscalCalendar 1086
SalesOrder 1088
SalesOrderDetail 1089
Invoice 1090
InvoiceDetail 1091
SavedQueryVisualization 1111
UserQueryVisualization 1112
RibbonTabToCommandMap 1113
RibbonContextGroup 1115
RibbonCommand 1116
RibbonRule 1117
RibbonCustomization 1120
RibbonDiff 1130
ReplicationBacklog 1140
FieldSecurityProfile 1200
FieldPermission 1201
SystemUserProfiles 1202
TeamProfiles 1203
AnnualFiscalCalendar 2000
SemiAnnualFiscalCalendar 2001
QuarterlyFiscalCalendar 2002
MonthlyFiscalCalendar 2003
FixedMonthlyFiscalCalendar 2004
Template 2010
ContractTemplate 2011
UnresolvedAddress 2012
Territory 2013
Queue 2020
License 2027
QueueItem 2029
UserEntityUISettings 2500
UserEntityInstanceData 2501
IntegrationStatus 3000
ConnectionRole 3231
ConnectionRoleAssociation 3232
ConnectionRoleObjectTypeCode 3233
Connection 3234
Equipment 4000
Service 4001
Resource 4002
Calendar 4003
CalendarRule 4004
ResourceGroup 4005
ResourceSpec 4006
ConstraintBasedGroup 4007
Site 4009
ResourceGroupExpansion 4010
InterProcessLock 4011
EmailHash 4023
DisplayStringMap 4101
DisplayString 4102
Notification 4110
ActivityPointer 4200
Appointment 4201
Email 4202
Fax 4204
IncidentResolution 4206
Letter 4207
OpportunityClose 4208
OrderClose 4209
PhoneCall 4210
QuoteClose 4211
Task 4212
ServiceAppointment 4214
Commitment 4215
UserQuery 4230
RecurrenceRule 4250
RecurringAppointmentMaster 4251
EmailSearch 4299
List 4300
ListMember 4301
Campaign 4400
CampaignResponse 4401
CampaignActivity 4402
CampaignItem 4403
CampaignActivityItem 4404
BulkOperationLog 4405
BulkOperation 4406
Import 4410
ImportMap 4411
ImportFile 4412
ImportData 4413
DuplicateRule 4414
DuplicateRecord 4415
DuplicateRuleCondition 4416
ColumnMapping 4417
PickListMapping 4418
LookUpMapping 4419
OwnerMapping 4420
ImportLog 4423
BulkDeleteOperation 4424
BulkDeleteFailure 4425
TransformationMapping 4426
TransformationParameterMapping 4427
ImportEntityMapping 4428
RelationshipRole 4500
RelationshipRoleMap 4501
CustomerRelationship 4502
CustomerOpportunityRole 4503
Audit 4567
EntityMap 4600
AttributeMap 4601
PluginType 4602
PluginTypeStatistic 4603
PluginAssembly 4605
SdkMessage 4606
SdkMessageFilter 4607
SdkMessageProcessingStep 4608
SdkMessageRequest 4609
SdkMessageResponse 4610
SdkMessageResponseField 4611
SdkMessagePair 4613
SdkMessageRequestField 4614
SdkMessageProcessingStepImage 4615
SdkMessageProcessingStepSecureConfig 4616
ServiceEndpoint 4618
AsyncOperation 4700
WorkflowWaitSubscription 4702
Workflow 4703
WorkflowDependency 4704
IsvConfig 4705
WorkflowLog 4706
ApplicationFile 4707
OrganizationStatistic 4708
SiteMap 4709
ProcessSession 4710
WebWizard 4800
WizardPage 4802
WizardAccessPrivilege 4803
TimeZoneDefinition 4810
TimeZoneRule 4811
TimeZoneLocalizedName 4812
Solution 7100
Publisher 7101
PublisherAddress 7102
SolutionComponent 7103
Dependency 7105
DependencyNode 7106
InvalidDependency 7107
Post 8000
PostRole 8001
PostRegarding 8002
PostFollow 8003
PostComment 8005
PostLike 8006
Report 9100
ReportEntity 9101
ReportCategory 9102
ReportVisibility 9103
ReportLink 9104
TransactionCurrency 9105
MailMergeTemplate 9106
ImportJob 9107
WebResource 9333
SharePointSite 9502
SharePointDocumentLocation 9508
Goal 9600
GoalRollupQuery 9602
Metric 9603
RollupField 9604
by

Get Entity OptionSet values in Dynamics CRM 2011

Here is the SQL query you can use to get any entity OptionSet values in Dynamics CRM 2011.

Pass entityname in where condition to get that entity optionset values

Example SQL query to get account entity optionset values


SELECT * FROM FilteredStringMap (NOLOCK)
WHERE FilteredViewName='FilteredAccount'

FilteredStringMap

by

Get Merged Accounts/Contacts/Leads in Dynamics CRM 2011

Here is the SQL query and FetchXML to get merged Accounts.

--Get Merged Accounts
SELECT FA.name,fa.masterid AS 'SubOrdinate Account',FA.masteraccountidname AS 'Master Account' 
FROM FilteredAccount (NOLOCK) FA 
WHERE Fa.merged=1

<fetch version='1.0' output-format='xml-platform' mapping='logical' no-lock='true' distinct='false'>
  <entity name='account'>
    <attribute name='name' alias='SubOrdinate_Account' />
    <attribute name='masterid' alias='Master_Account' />
    <filter>
      <condition attribute='merged' operator='eq' value='1' />
    </filter>
  </entity>
</fetch>

Here is the SQL query and FetchXML to get merged Contacts.

--Get Merged Contacts
SELECT FC.fullname AS 'SubOrdinate Contact',FC.mergedname AS 'Master Contact' 
FROM FilteredContact (NOLOCK) FC 
WHERE FC.merged=1

<fetch version='1.0' output-format='xml-platform' mapping='logical' no-lock='true' distinct='false'>
  <entity name='contact'>
    <attribute name='name' alias='SubOrdinate_Contact' />
    <attribute name='masterid' alias='Master_Contact' />
    <filter>
      <condition attribute='merged' operator='eq' value='1' />
    </filter>
  </entity>
</fetch>

Here is the SQL query and FetchXML to get merged Leads.

--Get Merged Leads
SELECT FL.fullname AS 'SubOrdinate Lead',FL.mergedname AS 'Master Lead' 
FROM FilteredLead (NOLOCK) FL 
WHERE FL.merged=1

<fetch version='1.0' output-format='xml-platform' mapping='logical' no-lock='true' distinct='false'>
  <entity name='lead'>
    <attribute name='name' alias='SubOrdinate_Lead' />
    <attribute name='masterid' alias='Master_Lead' />
    <filter>
      <condition attribute='merged' operator='eq' value='1' />
    </filter>
  </entity>
</fetch>
by

Field Datatypes available in Dynamics CRM 2011

Following datatypes are available in Dynamics CRM 2011 and corresponding SQL Server datatypes are shown in the following table.

CRM Datatype SQL Server Datatype
Single line of text Nvarchar
Option set Int
Two options Bit
Whole number Int
Floating point number Float
Decimal number Decimal
Currency Money
Multiple lines of text nvarchar(max)
Date and time Datetime
Lookup Uniqueidentifier
by

SQL Timeout solutions in Dynamics CRM 2011

Mostly this kind of problems could come from Plugins, if there are any plugins executed on that action.

Increase the default timeout

Registry on CRM applicationserver(s)

  1. Open CRM Web Server(where CRM is installed)
  2. Open regedit (start – > write ( regedit) ) and press ok
  3. Go to HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSCRM
  4. Add DWORD name OLEDBTimeout with value 86400 ( 24 hours) select decimal
  5. Add DWORD name ExtendedTimeout with value 1000000. The maximum value can be 2,147,483,647 and the default value is 30 seconds

These changes in register should fix the SQL timeout.

  1. Web.config
    1. <httpRuntime executionTimeout=”300″ />
      1. .NET 3.0: “timespan” attribute. The default is “00:01:50” (110 seconds)
      2. .NET 3.5 and 4.0: an integer in seconds. Default is 110 seconds.
      3. Specifies the maximum number of seconds that a request is allowed to execute before being automatically shut down by ASP.NET.
    2. IIS/ASP.NET configuration
      1. IIS 6.0 -> Website Properties -> ASP.NET tab -> Edit Configuration button -> Application tab -> Request execution timeout (seconds)
        1. Related to this type of situation in CRM 4.0:http://blogs.msdn.com/b/crm/archive/2008/11/20/asp-net-2-0-50727-0-warning-event-id-1309-due-to-request-time-out.aspx
    3. When using the CRM SDK, there are also timeout settings that can be set via custom code:
      1. CRM 4.0 example:
        1. CrmService service = new CrmService();
        2. service.Timeout = 300;
        3. In milliseconds and default is 100,000
      2. CRM 2011:
        1. ServiceProxy.Timeout property (Timespan)
    4. Identify the SQL query which takes long time to execute, when user clicks on “Resolve Case” button
    5. Use the SQL Server Management Studio and SQL Profiler to identify the query execution plan and to get recommendations about possible indexes
    6. Run the SQL profiler after settings the following events?

Identify the SQL Query

RPC: Completed
SQL: Batch Completed
SQL: Batch Starting

  1. Make sure you check the text data filed and the CRM database name filed and filer the profiler trace for CRM databases only so that it can only capture the CRM related query execution in the trace.
  2. Go to the point where you are relay to reproduce the issue and then turn on the Tracing from the profiler. Stop the tracing immediately after the issue reproduced.
  3. After we find out the query and entity which is taking too long to execute, we can create indexes on that entity

Note: There are 2 important things to take into consideration when you are adding indexes:

    1. It is unsupported to add SQL indexes to the MSCRM database. My view on this is that, as long as the index does not implement any constraints (i.e. it’s not a UNIQUE index) then you will affect the stability of CRM; you may however need to drop the index prior to upgrading CRM.
    2. Although adding an index may improve the performance of one query, it can adversely affect other SQL operations – most obviously data updates. There is no easy solution to this,though the SQL Profiler can help you if you capture and analyse a representative sample of SQL operations

Change the Plugin code

  1. If the Plugin code (e.g. through a RetrieveMultiple request) is used to query the data
  2. For example, RetrieveMultiple requests on activities are not necessarily processed very efficiently by CRM (the problem is the way that is accesses the activity parties) , sometimes DB locks can happen using this.
  3. As far as I know FetchXml works more efficiently and makes significant improvements than RetrieveMultiple request. It gives closer control over the joins used. We can also specify SQL Server DB no locks in FetchXml

<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”false” no-lock=”true” ></fetch>