Retrieve more than 5000 records in Dynamics 365


We know there is limitation that by default we can get maximum 5000 records in a single web service call but by using paging cookie concept we can get more then 5000 records. Sharing sample code how to use it.

public static List<StudentRecord> ReturnStudentListTest(IOrganizationService service, Int16 pageNumber, Int16 fetchCount)
{
List<StudentRecord> StudentList = new List<StudentRecord>();
string fetchStudent = string.Empty;
string pagingCookie = null;

if (fetchCount == 0)
{
fetchCount = 100;
}
if (pageNumber == 0)
{
pageNumber = 1;
}

fetchStudent = “<fetch version=’1.0′ output-format=’xml-platform’ mapping=’logical’ distinct=’true’>” +
” <entity name=’contact’>” +
” <attribute name=’contactid’ />” +
” <attribute name=’fullname’ />” +
” <order attribute=’fullname’ descending=’false’ />” +
” <filter type=’and’>” +
” <condition attribute=’statecode’ operator=’eq’ value=’0′ />” +
” </filter>” +
” </entity>” +
“</fetch>”;

while (true)
{
string fetchXmlWithPaging = Helper.CreateXml(fetchStudent, pagingCookie, pageNumber, fetchCount);
var fetchExpression = new FetchExpression(fetchXmlWithPaging);
EntityCollection studentCollection = service.RetrieveMultiple(fetchExpression);
if (studentCollection.Entities.Count > 0)
{
foreach (var record in studentCollection.Entities)
{
try
{
StudentRecord studentRecord = new StudentRecord();
studentRecord.studentId = record.Contains(CRMConstants.Contact.studentId) ? ((Guid)record.Attributes[CRMConstants.Contact.studentId]).ToString() : string.Empty;
studentRecord.fullname = record.Attributes.Contains(CRMConstants.Contact.fullname) ? record.Attributes[CRMConstants.Contact.fullname].ToString().ToUpper() : String.Empty;
StudentList.Add(studentRecord);
}
catch (FaultException<Microsoft.Xrm.Sdk.OrganizationServiceFault> ex)
{
throw new Exception(ex.Detail.Message);
}
catch (Exception ex)
{
throw new InvalidCastException(ex.Message);
}
}
if (studentCollection.MoreRecords)
{
pageNumber++;
pagingCookie = studentCollection.PagingCookie;
}
else
break;
}
}
return StudentList;
}

Please refer the below URL for details.
https://msdn.microsoft.com/en-us/library/mt269606.aspx
https://softchief.com/2016/02/20/retrieve-more-than-5000-records-in-dynamics-crm/

Hope it helps.

Retrieve Data Using Web API and FetchXml


Sharing sample code to retrieve data using Web API and using FetchXml to build the query instead of oData. In this example, I am retrieving contact based on email/mobile no and calling function form an entity’s ribbon button to validate duplicate record.

function ValidateContact() {
var mobilePhone = Xrm.Page.getAttribute(“mc_contactnumber”).getValue();
var email = Xrm.Page.getAttribute(“emailaddress”).getValue();
var clientURL = Xrm.Page.context.getClientUrl();
var fetchContact = “<fetch version=’1.0′ output-format=’xml-platform’ mapping=’logical’ distinct=’false’>” +
” <entity name=’contact’>” +
” <attribute name=’fullname’ />” +
” <attribute name=’emailaddress1′ />” +
” <attribute name=’mc_mobilephone’ />” +
” <attribute name=’createdon’ />” +
” <attribute name=’ownerid’ />” +
” <order attribute=’fullname’ descending=’false’ />” +
” <filter type=’and’>” +
” <filter type=’or’>” +
” <condition attribute=’mc_mobilephone’ operator=’eq’ value='” + mobilePhone + “‘ />” +
” <condition attribute=’emailaddress1′ operator=’eq’ value='” + email + “‘ />” +
” </filter>” +
” <condition attribute=’statecode’ operator=’eq’ value=’0′ />” +
” </filter>” +
” </entity>” +
“</fetch>”;

var encodedFetchXml = encodeURI(fetchContact);
var reqURL = clientURL + “/api/data/v8.2/contacts?fetchXml=” + encodedFetchXml;
var req = new XMLHttpRequest();
req.open(“GET”, reqURL, false);
req.setRequestHeader(“Accept”, “application/json”);
req.setRequestHeader(“Content-Type”, “application/json; charset=utf-8”);
req.setRequestHeader(“OData-MaxVersion”, “4.0”);
req.setRequestHeader(“OData-Version”, “4.0”);
//req.setRequestHeader(“Prefer”, “odata.include-annotations=\”*\””);
req.setRequestHeader(“Prefer”, “odata.include-annotations=\”OData.Community.Display.V1.FormattedValue\””);
req.onreadystatechange = function () {
if (this.readyState == 4) {
req.onreadystatechange = null;
if (this.status == 200) {
var data = JSON.parse(this.response);
if (data.value.length == 0) {
alert(“There is no matching contact found in exiting contact list.”);
}
else if (data.value.length == 1) {
alert(“Duplicate Contact Record Found: Another Contact with the same detail already exists in the system which is created on: ” + data.value[0][‘createdon@OData.Community.Display.V1.FormattedValue’] + ” by ” + data.value[0][‘_ownerid_value@OData.Community.Display.V1.FormattedValue’] + “.”);
}
else {
alert(“There are more than one matching contact record found.”);
}
}
else {
var error = JSON.parse(this.response).error;
alert(error.message);
}
}
};
req.send();
}

To use FetchXml, we need to format FetchXml in usable Web API service endpoint format. We do this by storing the FetchXML in a variable and encoding the string with the encodeURI function native to JavaScrip as below.
var encodedFetchXml = encodeURI(fetchContact);

Another point, if you want formatted values to be included, then you need to set request header like below(This is mainly used to get Lookup, OptionSet, DateTime specific formatted value like lookup text value).
req.setRequestHeader(“Prefer”, “odata.include-annotations=\”*\””);
OR
req.setRequestHeader(“Prefer”, “odata.include-annotations=\”OData.Community.Display.V1.FormattedValue\””);

Please refer the below URL for details.
https://community.dynamics.com/crm/b/mscrmcustomization/archive/2016/10/18/ms-crm-2016-web-api-operations-retrieve-single-or-multiple-records

http://himbap.com/blog/?p=2012

Issue with Data Export Service Add-ons


In one of requirement, we wanted to use CRM Data for reporting purpose but due to some limitation with FetchXml (like N-N relationship) we were unable to get desire data so we decided to use Data Export Service Add-ons to push into Azure and make use of data for reporting purpose.

When we tried to install/Enable the Data Export Service from Dynamics Marketplace, the add-on installed successfully without any error but when we tried browse Data Export Service(Settings -> Data Export), it was showing blank page and Data Export Profile page was not displaying at all in IE 11 as below.

We raised ticket to Microsoft and they suggested to add below URL in IE trusted site list and once we added these sites in trusted site, we were able to see Data Export Profile page(Settings->Data Export) as shown below.
https://*.azure.net
https://*.crm.dynamics.com
https://*.microsoftonline.com
https://*.windows.net

Note: In Google Chrome , It was working fine even without adding above URLs in trusted sites.

Hope this will help someone.

Configure Dynamics 365 and Azure Service Bus Integration


As we know, we can connect Dynamics 365 with Azure platform by coupling Dynamics 365 event execution pipeline to the Microsoft Azure Service Bus. Once configured, this connection allows data that’s been processed as current Dynamics 365 operation to be posted to the service. Microsoft Azure Service Bus solutions that are “Dynamics-365 aware” can listen for and read the Dynamics 365 data from the service bus.There are many ways to establish a contract between Dynamics 365 and an Azure solution as below.

  • Queue
  • One-way
  • Two-way
  • Rest
  • Topic
  • Event Hub

In this post, I will use “Queue” contract where a listener doesn’t have to be actively listening for messages on the endpoint.
Let’s implement a basic scenario – When a record is created in CRM then pass the execution context to queue and then prepare a application to read and display the entity information.

Lets first configure Azure to create a Queue listener using Azure Portal(https://portal.azure.com).

  1. Create a Service Bus by click on “Add” button.

    2. Enter mandatory fields as below.

3. Create a Queue in newly created Service Bus(mcstaging in my case)

4. Create SAS Policy for Authorization and copy the Primary Connection String which is required during end point registration in CRM

Now let’s create service endpoint in CRM using plugin registration tool.
Login into plugin registration tool and click on Register ->Register New Service End Point as below


Copy the primary connection string form  step 4 and click on “Next”

All information will be populated automatically and then click on “Save”.
Now, need to register a step for this endpoint(In my case, custom entity Enquiry(mc_feedback)  on create message).


Now create a enquiry record in CRM, once you create a record in CRM a system job will be created and message will be passed to azure queue created earlier.

You can see one message in queue as below


Now, how to read the message from Queue. Lets create a console application to read the message from Queue.
Here the connection string will be the same which we had specified in the plugin registration tool. The message body is of type RemoteExecutionContext.


Output

 

Import attachment into MS CRM 2011 entity


Let’s say we want to attach some documents in Contact entity records. We can use out of box Import Data Tool to upload data/attachments into MS CRM 2011.To import attachment into particular record we need to identify a unique key for the record(let’s say for contact entity, it’s contact id i.e. GUID or any field which is unique).

Below is the step by step procedure to import attachment for contact’s record.
Steps:
Prepare Source Data:

Step #1 Go to Data Management ->Templates for Data Import -> Select Note and click on Download.
Step #2 Edit the downloaded template as shown below.
https://arvindcsit.files.wordpress.com/2012/07/sampledata.jpg

Description of Columns:
Title: This would be Title of attached file.
File Name: File name of the attachment.
Mime Type: Type of attachment like text/plain.
Regarding:  Reference of record’s field name. In my case I have selected Contact’s Full Name.
Document: Document Id of attached file.
Owner: Owner name must be the CRM user.
Description: Description of file.

Step #3 Build the folder structure for attachments and data file:
Below are some guidelines for file size and folder structure.

If you want to Import multiple files in one Import session, you can .zip them together. A .zip file can include files of .csv, .xml, or .txt file types. All files in a single compressed file must be of the same file type.

The .zip file must confirm to one of these folder structure:

  • .zip file having the files and optional attachment folder directly in it:-
    • Attachments (Folder)
    • <File1>
    • <File2>
    • <File3>

Note: By default, the maximum size of the files you can import is 8 megabytes (MB). This means:

  • Any .csv, .txt, or .xml file must not exceed 8 MB.
  • Any individual file inside the .zip file must not exceed 8 MB and the total size of the .zip file, including the Attachment folder, must not exceed 32 MB.

You can choose any of the above file formats and give it as an input to the Import Data wizard. The delimited .txt, .csv, or XML Spreadsheet 2003 format files can be easily created by using Microsoft Office Excel.

In my case, I followed this “ZIP folder that contain data files and one Attachment folder directly under root” i.e.

  • ContactImport(Zip file name)
    • Attachments(folder)
    • Note.xml

Step #4 Import the ContactImport.zip file using Import Data feature (Data Management ->Imports).
Step #5 Click Next
Step#6 Select “SampleDataMap” and Click Next.
Step#7 Select “Note” for Microsoft Dynamics CRM Record Types and Click Next.
Step#8 Select mapping fields from CRM Fields as shown below and Click Next.
https://arvindcsit.files.wordpress.com/2012/07/fieldsmapping.jpg
Step#8 Clicks on Next.
Step#9 Select Allow Duplicates “No”  and Click Submit.
You can write Data map Name for future import.
Step#9 Data has been submitted for import. It may take time based on size of data.

Step#10 Please check the contact record for attachment.

Moving CRM Servers(Application and Database) to New DataCentre


One of our client wanted to move CRM Server from One Data Centre to different data centre but due to some constraint we could not move it by creating New Organization or by new deployment. We had only left option was update IP address manually.

Recently we moved our CRM Server from one data centre to new data centre due to which IP Address of both Application and Database Server was changed. We had updated new IP Address in Registry(MSCRMconfigdb,database,LocalSdkHost,metabase,ServerUrl,SQLRSServerUrl), Application Pool, Web.Config and custom application. But even after updating new IP address on above section  we were getting the following error.

A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 172.23.201.162:80

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 172.23.201.162:80

Source Error:

An unhandled exception was generated during the execution   of the current web request. Information regarding the origin and location of   the exception can be identified using the exception stack trace below.

Stack Trace:

 
[SocketException (0x274c): A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 172.23.201.194:80]
   System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress) +239
   System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP) +35
   System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception) +224

[WebException: Unable to connect to the remote server]
   System.Net.HttpWebRequest.GetRequestStream(TransportContext& context) +1868309
   System.Net.HttpWebRequest.GetRequestStream() +13
   System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) +103
   Microsoft.Crm.Metadata.MetadataWebService.GetDataSet() +31
   Microsoft.Crm.Metadata.DynamicMetadataCacheLoader.LoadDataSetFromWebService(Guid orgId) +291
   Microsoft.Crm.Metadata.DynamicMetadataCacheLoader.LoadCacheFromWebService(LoadMasks masks, Guid organizationId) +42
   Microsoft.Crm.Metadata.DynamicMetadataCacheFactory.LoadMetadataCache(LoadMethod method, CacheType type, IOrganizationContext context) +408
   Microsoft.Crm.Metadata.MetadataCache.LoadCache(IOrganizationContext context) +339
   Microsoft.Crm.Metadata.MetadataCache.GetInstance(IOrganizationContext context) +388
   Microsoft.Crm.BusinessEntities.BusinessEntityMoniker..ctor(Guid id, String entityName, Guid organizationId) +116
   Microsoft.Crm.Caching.UserDataCacheLoader.LoadCacheData(Guid key, ExecutionContext context) +323
   Microsoft.Crm.Caching.ObjectModelCacheLoader`2.LoadCacheData(TKey key, IOrganizationContext context) +401
   Microsoft.Crm.Caching.BasicCrmCache`2.CreateEntry(TKey key, IOrganizationContext context) +84
   Microsoft.Crm.Caching.BasicCrmCache`2.LookupEntry(TKey key, IOrganizationContext context) +135
   Microsoft.Crm.BusinessEntities.SecurityLibrary.GetUserInfoInternal(WindowsIdentity identity, IOrganizationContext context, UserAuth& userInfo) +252
   Microsoft.Crm.BusinessEntities.SecurityLibrary.GetCallerAndBusinessGuidsFromThread(WindowsIdentity identity, Guid organizationId) +179
   Microsoft.Crm.Authentication.CrmWindowsIdentity..ctor(WindowsIdentity innerIdentity, Boolean publishCrmUser, Guid organizationId) +252
   Microsoft.Crm.Authentication.WindowAuthenticationProviderBase.Authenticate(HttpApplication application) +431
   Microsoft.Crm.Authentication.AuthenticationStep.Authenticate(HttpApplication application) +172
   Microsoft.Crm.Authentication.AuthenticationPipeline.Authenticate(HttpApplication application) +86
   Microsoft.Crm.Authentication.AuthenticationEngine.Execute(Object sender, EventArgs e) +525
   System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +68
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +75

 

Actually It was still referring Old IP Address at some places. To resolve the issue we enable the trace log and get CrmServerReportInformation with help of “Create File” option of  CrmDiagTool 4.0  and came to know that DeploymentProperty Table(Column Name- ADSdkRootDomain, ADWebApplicationRootDomain and AsyncSdkRootDomain) of MSCRM_Config was still referring old IP Address. We have updated this column with new IP Address as below and everything start working fine.

USE MSCRM_CONFIG

Update DeploymentProperties SET NVarCharColumn = ‘New IP Address’  WHERE ColumnName= ‘AsyncSdkRootDomain’

Update DeploymentProperties SET NvarCharColumn = ‘New IP Address’  WHERE ColumnName = ‘ADSdkRootDomain’

Update DeploymentProperties SET NvarCharColumn = ‘New IP Address’  WHERE ColumnName = ‘ADWebApplicationRootDomain’

 

Hope this help you 🙂

Differences between Data Migration and Data Import


As mentioned in CRM Sdk, below is the major differences between Data Migration and Data Import.

Data migration and data import use common entity model and messages. However, there are some important differences in the feature sets of the two operations as shown in the following table.

Feature Data Migration Data Import
Import data into Microsoft Dynamics CRM Yes (Only the system administrator can migrate data in the offline mode.) Yes (All users who have appropriate permissions can import data.)
Import data into custom entity types and attributes Yes Yes
Use multiple source files that contain related data Yes No (You can only import one file at a time.)
Assign entity instances to multiple users Yes No (All entity instances are assigned to one user.)
Update existing entity instances No Yes
Detect duplicates No (You can run duplicate detection after you migrate the data.) Yes
Delete all entity instances associated with one job Yes (Use bulk delete feature.) Yes (Use bulk delete feature.)
Automatically map data based on column headings in the source file No Yes
Set the value of the createdon attribute for the entity from source data Yes No
Customize Microsoft Dynamics CRM to match source data No (Custom entities and attributes can be created by Data Migration Manager.) No (Customization must be done before import.)
Apply complex data transformation mapping Yes No
Import state and status information Yes No