Advertisements

Dynamics 365 – New Admin Center (Preview)

October 22, 2018 Leave a comment

Have you noticed the Try the new Admin center link when you connected to your Dynamics 365 Admin Center?

Admin Center_2

When you click on the link, you will be redirected to ‘Admin Center (Preview)’ built on Power platform.

Admin Center_1

Click on the Instance name (i.e.,MS) in above screen, will take you Instance Details page.

Admin Center_5

Click on ‘See all‘ to get the ‘Details’ pane.

Admin Center_6

Click on ‘Edit‘ to get ‘Settings’ edit pane where you can change the Instance Type (i.e., Production/Sandbox)

Admin Center_7

I started exploring and one standout feature so far is ‘Settings’ page where you can manage all your Admin related configurations (i.e., Short cuts) in one page.

Admin Center_3

To go to ‘Settings’ page, you first need to select the ‘Instance’ and click on ‘Settings’ button.

Admin Center_4

🙂

Advertisements

SQL Server- Troubleshoot ‘String or binary data would be truncated’ error

October 20, 2018 Leave a comment

In one of my Data Migration requirement, I had to join data from multiple tables and  move to a single table. Requirement is complex as data is around 20 GB and table has around 300 columns.

So, I built a Stored Procedure to join tables and move data to single table, the query ran for an hour and finally failed with below error:

String or binary data would be truncated.

Reason:

  • Length of the data I was trying to insert to is more than the defined column size in target table.

Fix:

  • Identifying the problematic column was difficult as my source tables had 300 columns.
  • To trouble the issue, I ran a query on my source tables to get max data length of all columns, which helped me to identify the mismatched column.
  • Below query helped me to identify the maximum data length of source table columns

DECLARE @SQLStatement VARCHAR(MAX);
DECLARE @TableName sysname;
–Set your table name here
SET @TableName = ‘Product‘;
SET @SQLStatement = ”;

SELECT @SQLStatement = @SQLStatement + ‘SELECT ‘ + QUOTENAME(cols.name, ””) + ‘ AS ”Column Name”, ‘ + QUOTENAME(types.name, ””) + ‘ AS ”Data Type”, ‘ +
QUOTENAME(cols.max_length, ””) + ‘ AS ”Defined Length”, MAX(DATALENGTH(‘ + QUOTENAME(cols.name) + ‘)) AS ”Max Data Length” FROM ‘+@TableName+ char(10) +’ UNION ‘
FROM
SYS.COLUMNS cols
JOIN
SYS.TYPES types
ON
types.system_type_id = cols.system_type_id and types.name != ‘sysname’
WHERE
cols.OBJECT_ID = OBJECT_ID(@TableName);

SET @SQLStatement = LEFT(@SQLStatement, LEN(@SQLStatement)-6)
— Execute SQL statement
EXEC(@SQLStatement)

  • To simplify the understanding I created a ‘Product’ table with 4 columns and added few rows

Product Table_1

  • Execute the query and it lists out ‘Column Name’,’Data Type’, ‘Defined Column Length’ and ‘Max Data Length’.
    • In the below result, my ‘Description’ column’s max data length is 142

Product Table_2

🙂

 

SQL Server- Drop If Exists (DIE) Objects

September 28, 2018 Leave a comment

Other day, I generated my Database SQL Script including script to create Tables, Procedures, Types, Views etc., using Tasks -> Generate Scripts… option which I had to deploy on my test instance.

DIE_1

‘Generate Scripts’ generate the SQL script with ‘CREATE’ statement (i.e., It will have ‘CREATE Table’, ‘CREATE PROCEDURE’ etc…)

Issue is, what if I already had Database script ran previously and want to only update the Database objects, you would get script error if you run the generated SQL Script.

Solution is, we need to check existence of the object (i.e.,Table, Procedure, View etc.) and Drop if exists the object and run the CREATE statement generated using ‘Generate Scripts…’ option.

Drop If Exists (DIE) statement in SQL Server 2016:

From SQL Server 2016 CTP3 we can use DIE  statements to check if the Object exists and Drop before run the CREATE statements.

  • Dropping ‘Procedure’ if exists:

DROP PROCEDURE IF EXISTS [Stored_Procedure_Name]

  • Dropping ‘Table’ if exists:

DROP TABLE IF EXISTS [Table_Name]

  • Dropping ‘Type’ (i.e.,User Defined Table Type/User Defined Data Types) if exists:

DIE_2

DROP TYPE IF EXISTS [Type_Name]

  • Delete Table’s COLUMN if Exists.

ALTER TABLE DROP COLUMN IF EXISTS

  • Delete Table’s CONSTRAINT if exists

ALTER TABLE DROP CONSTRAINT IF EXISTS

Below are the list of Objects that can be included in DIE statements.

DIE_3

Note:

  • If your SQL version is before 2016 below is the syntax

IF OBJECT_ID(‘Table_Name‘, ‘U’) IS NOT NULL
DROP TABLE Table_Name;

  • You can also use ‘Check for object existence‘ and ‘Script DROP and CREATE‘ options while ‘Generating Script’ which delete and recreate objects.

DIE_4.PNG

🙂

Categories: SQL Tags: ,

Web API Helper Code Compilation Error

September 18, 2018 Leave a comment

I was creating a console application to connect to Dynamics 365 Web API, and downloaded “Microsoft.CrmSdk.WebApi.Samples.HelperCode” NuGet package.

Compilation Error_1

I got “AcquireToken method is no longer available” compilation error, when I build the project

Compilation Error

Reason & Fix:

  • We have to use UserPasswordCredential class in ADAL v3.
  • Below is the code snippet

var credentials = new UserPasswordCredential(userName, password);
var context = new AuthenticationContext(authorityUri);
authResult = context.AcquireTokenAsync(serviceUrl, applicationId, credentials).Result;

Refer my previous article for step by step to connect to Dynamics 365 Web API.

🙂

[Fix] AADSTS65001: The user or administrator has not consented to use the application

September 18, 2018 Leave a comment

I was getting below exception when I was trying to connect to D365 Web API from console.

AAD_Access_2

Reason:

  • Issue was the Application registered on ‘Azure Active Directory’ was not granted the Permission

Fix:

  • Connect to the Azure Active Directory admin center,  using O365 credentials.
  • Select the ‘Application’ from the ‘Azure Active Directory -> App registrations’
  • Click on ‘Settings’ -> Required Permissions -> Click on ‘Grant permissions‘ button

AAD_Access_1

Refer my previous article on how to register application and connect to D365 Web API

🙂

[Code Snippet] Authenticate and Perform Operations using D365 Web API and C#

September 18, 2018 3 comments

As a continuation to my last article Different ways to connect authenticate dynamics 365 , lets see how to Authenticate Dynamics Web API using C#.

Note: Be mindful that this approach is different than connecting to Dynamics 365 using Microsoft.XRM.Tooling.Connector dll approach. This article explains how to connect to D365 using Web API which is no SDK .dll approach.

Pre-requisites:

  • Dynamics 365 subscription. Go for 30 days trail if not already have one.
  • Register an App in “Azure Active Directory” and get ‘Application ID’ and other parameters.
  • Visual Studio Console application

Steps to Register App in “Azure Active Directory”:

We need to register an application with Microsoft Azure Active Directory so that it can connect to the Microsoft Dynamics 365 server, authenticate using OAuth, and access the web services.

WebAPI_Snippet2

  • From the “Azure Active Directory admin center’, select ‘App registrations’ -> New application registration

WebAPI_Snippet3

  • Provide below details
    • Name – Provide name of the App. Can be any name minimum of 4 characters.
    • Application Type – Choose ‘Native’ as we are going to call Web API from Console application
    • Sign-on URL – Can be a valid URL. This you need to pass in the Console application.

WebAPI_Snippet4

  • Click ‘Create’ to complete the App creation
  • Post creation, open the App and copy the ‘Application ID’ which you need in Console application.

WebAPI_Snippet5

  • Click on ‘Settings’ -> Required Permissions -> Add ‘Dynamics CRM Online’ -> Enable the permission as below

WebAPI_Snippet6

  • Finally, select the App, click on ‘Endpoints’ and copy ‘OAuth 2.0 Authorization Endpoint‘ which you would need in Console Application.

WebAPI_Snippet7

Steps to connect to D365 WebAPI from Console Application:

After registering App in ‘Azure Active Directory’ now its time to connect to D365 Web API from Console Application.

  • Create a new C# Console Application project
  • Add below 2 Nuget packages to the project
    • Newtonsoft.Json
    • Microsoft.IdentityModel.Clients.ActiveDirectory

WebAPI_Snippet8

Code Snippet:

In the ‘Program.cs’ file add below

  • Add Using Namespaces:

using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.Net.Http;
using System.Net.Http.Headers;
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using System.Net;

  • Add Global Variables:

// O365 User Name and Password
private const string userName = “rajeevpentyala@exploreSept18.onmicrosoft.com”;
private const string password = “YourPasswordHere”;
// D365 Application Url
private const string serviceUrl = “https://exploresept18.crm.dynamics.com”;
// Azure APP Application Id
private const string applicationId = “1549b5b3-XXXX-XXXX-94be-7a8eeaf3e081”;
// Redirct Uri specified during registration of application
private const string RedirectUri = “https://localhost”;
// OAuth 2.0 Authorization Endpoint copied from Azure APP
private const string authorityUri = “https://login.microsoftonline.com/9e3039aa-XXXX-XXXX-80e1-f67d40bd01cf/oauth2/authorize”;

private static AuthenticationResult authResult = null;

  • Main Method:

private static void Main(string[] args){

// Code to connect to D365
var credentials = new UserPasswordCredential(userName, password);
var context = new AuthenticationContext(authorityUri);
authResult = context.AcquireTokenAsync(serviceUrl, applicationId, credentials).Result;

// Call CRUD operations

// Task.WaitAll(Task.Run(async () => await ExecuteWhoAmI()));
// Task.WaitAll(Task.Run(async () => await CreateRecord()));
// Task.WaitAll(Task.Run(async () => await RetrieveContacts()));

}

  • Code to call WhoAmIRequest:

private static async Task ExecuteWhoAmI(){
var httpClient = new HttpClient{
BaseAddress = new Uri(serviceUrl),
Timeout = new TimeSpan(0, 2, 0)
};
httpClient.DefaultRequestHeaders.Add(“OData-MaxVersion”, “4.0”);
httpClient.DefaultRequestHeaders.Add(“OData-Version”, “4.0”);
httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue(“application/json”));
httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue(“Bearer”, authResult.AccessToken);

// Add this line for TLS complaience
ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12;

// Call WhoAmI
var retrieveResponse = await httpClient.GetAsync(“api/data/v9.0/WhoAmI”);
if (retrieveResponse.IsSuccessStatusCode){
var jRetrieveResponse = JObject.Parse(retrieveResponse.Content.ReadAsStringAsync().Result);

var currUserId = (Guid)jRetrieveResponse[“UserId”];
var businessId = (Guid)jRetrieveResponse[“BusinessUnitId”];

Console.WriteLine(“My User Id – ” + currUserId);
Console.WriteLine(“My User Id – ” + businessId);
Console.ReadLine();
}
}

  • Code to Retrieve Records:

private static async Task RetrieveContacts(){
var httpClient = new HttpClient{
BaseAddress = new Uri(serviceUrl),
Timeout = new TimeSpan(0, 2, 0)
};
httpClient.DefaultRequestHeaders.Add(“OData-MaxVersion”, “4.0”);
httpClient.DefaultRequestHeaders.Add(“OData-Version”, “4.0”);
httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue(“application/json”));
httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue(“Bearer”, authResult.AccessToken);

// Add this line for TLS complaience
ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12;

// Retrieve Contacts
var retrieveResponse = await httpClient.GetAsync(“api/data/v9.0/contacts”);
if (retrieveResponse.IsSuccessStatusCode){
var jRetrieveResponse = JObject.Parse(retrieveResponse.Content.ReadAsStringAsync().Result);

dynamic collContacts = JsonConvert.DeserializeObject(jRetrieveResponse.ToString());

foreach (var data in collContacts.value){
Console.WriteLine(“Contact Name – ” + data.fullname.Value);
}

Console.ReadLine();
}
}

WebAPI_Snippet1

  • Code to Create Record:

private static async Task CreateRecord(){
JObject contact1 = new JObject{
{ “firstname”, “Peter” },
{ “lastname”, “Cambel” },
{ “annualincome”, 80000 }
};

contact1[“jobtitle”] = “Junior Developer”;

var httpClient = new HttpClient{
BaseAddress = new Uri(serviceUrl + “/api/data/v9.0/”),
Timeout = new TimeSpan(0, 2, 0)
};
httpClient.DefaultRequestHeaders.Add(“OData-MaxVersion”, “4.0”);
httpClient.DefaultRequestHeaders.Add(“OData-Version”, “4.0”);
httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue(“application/json”));
httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue(“Bearer”, authResult.AccessToken);

// Add this line for TLS complaience
ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12;
HttpRequestMessage request = new HttpRequestMessage(HttpMethod.Post, “contacts”){
Content = new StringContent(contact1.ToString(), Encoding.UTF8, “application/json”)
};

HttpResponseMessage response = await httpClient.SendAsync(request);if (response.StatusCode == HttpStatusCode.NoContent) //204 {
Console.WriteLine(“POST succeeded, entity created!”);
//optionally process response message headers or body here, for example:
var entityUri = response.Headers.GetValues(“OData-EntityId”).FirstOrDefault();

// Update the Contact record
Task.WaitAll(Task.Run(async () => await UpdateRecord(entityUri)));

// Delete the contact record
Task.WaitAll(Task.Run(async () => await DeleteRecord(entityUri)));
}
else{
Console.WriteLine(“Operation failed: {0}”, response.ReasonPhrase);
throw new CrmHttpResponseException(response.Content);
}
}

  • Code to Update Record:

private static async Task UpdateRecord(string contactUri){
JObject contact1Add = new JObject{
{ “annualincome”, 80000 },
{ “jobtitle”, “Junior Developer” }
};

var httpClient = new HttpClient{
BaseAddress = new Uri(serviceUrl + “/api/data/v9.0/”),
Timeout = new TimeSpan(0, 2, 0)
};

httpClient.DefaultRequestHeaders.Add(“OData-MaxVersion”, “4.0”);
httpClient.DefaultRequestHeaders.Add(“OData-Version”, “4.0”);
httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue(“application/json”));
httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue(“Bearer”, authResult.AccessToken);

HttpRequestMessage updateRequest1 = new HttpRequestMessage(new HttpMethod(“PATCH“), contactUri){
Content = new StringContent(contact1Add.ToString(), Encoding.UTF8, “application/json”)
};
HttpResponseMessage updateResponse1 = await httpClient.SendAsync(updateRequest1);

if (updateResponse1.StatusCode == HttpStatusCode.NoContent) //204 {
//Console.WriteLine(“Contact ‘{0} {1}’ updated with job title” +
// ” and annual income.”, contactUri.GetValue(“firstname”),
// contactUri.GetValue(“lastname”));
}
else{
Console.WriteLine(“Failed to update contact for reason: {0}”, updateResponse1.ReasonPhrase);
throw new CrmHttpResponseException(updateResponse1.Content);
}

}

  • Code to Delete Record:

private static async Task DeleteRecord(string contactUri){
var httpClient = new HttpClient{
BaseAddress = new Uri(serviceUrl + “/api/data/v9.0/”),
Timeout = new TimeSpan(0, 2, 0)
};

httpClient.DefaultRequestHeaders.Add(“OData-MaxVersion”, “4.0”);
httpClient.DefaultRequestHeaders.Add(“OData-Version”, “4.0”);
httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue(“application/json”));
httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue(“Bearer”, authResult.AccessToken);

var response = httpClient.DeleteAsync(contactUri).Result;
if (response.IsSuccessStatusCode) //200-299 {
Console.WriteLine(“Contact has been deleted!!!”);
}
else if (response.StatusCode == HttpStatusCode.NotFound) //404 {
//Entity may have been deleted by another user or via cascade delete.
}
else //Failed to delete {
Console.WriteLine(“Error while deletion; Message: ” + response.Content);
}
}

🙂

D 365 Development – Different ways to connect/authenticate Dynamics 365

September 17, 2018 1 comment

There was a question posted on my blog by a Dynamics 365 newbie developer on how to authenticate to Dynamics 365 online from his console application.

In this article I am going to detail various ways to connect to Dynamics 365.

Option 1 – Connect using Dynamics 365 SDK assemblies:

  • You need Dynamics 365 SDK assemblies, If you are creating plug-ins, custom workflow activities, or custom XAML workflows and performing operations (i.e., Create/Update/Execute/Retrieve)
  • Refer Steps to create a basic plug-in
  • Download the latest SDK assemblies from nuget.

Option 2 – Connect using XRM Tooling assemblies:

  • If you are building .Net applications (i.e., Console/Web/Windows) use the XRM Tooling assemblies to connect to the Dynamics Application.
  • XRM tooling enables you to connect to your Dynamics 365 instance by using connection strings.
  • Refer article for different types of Connection strings based on your Dynamics deployment (i.e., One-prem/IFD/Office 365 etc..)
  • Below is the sample code to connect to your instance from Console:

Prerequisites:

  • Download the latest SDK assemblies from nuget in your console application.
  • Make sure you refer below .dlls in your console class file.
    • using Microsoft.Xrm.Sdk;
    • using Microsoft.Xrm.Tooling.Connector;

App.Config:

// Add below Connection string to your console’s App.config file

<connectionStrings>

<add name=”Xrm” connectionString=”Url=https://{orgname}.crm.dynamics.com; Username=rajeevpentyala@yourdomain.onmicrosoft.com; Password=XXXXXXX;authtype=Office365;Timeout=20″ />

</connectionStrings>

Code:

// Declare the Service Variables

private static OrganizationServiceProxy _serviceProxy;

private static IOrganizationService _service;

// Read the connection string configured in App.config file

var connectionString = ConfigurationManager.ConnectionStrings[“Xrm”].ConnectionString;

var crmConn = new CrmServiceClient(connectionString);

using (_serviceProxy = crmConn.OrganizationServiceProxy) {

_service = _serviceProxy;

var reqWhoAmI = new WhoAmIRequest();

var resp = (WhoAmIResponse)_service.Execute(reqWhoAmI);

var buID = resp.OrganizationId.ToString();

var userID = resp.UserId.ToString();

}

  • Refer my post on steps to connect to D 365 using Xrm.Tooling.Connector

Option 3 – Connect using Dynamics 365 Web API:

  • What if you want to connect to Dynamics from a Non- .NET applications (i.e., Java/PHP applications), the solution is Web API.
  • Web API provides development experience that can be used across a wide variety of programming languages, platforms, and devices.
  • Web API uses no DLL approach; Unlike above 2 approaches (i.e., XRM Tooling/SDK Assemblies), you don’t need to refer assemblies to connect to Web API.
  • There are 3 different ways to connect to Web API
    1. Using JavaScript in from Dynamics web resources (i.e., Jscript files, HTML, Ribbon). We don’t need to include any authentication code as the logged-in user is already authenticated by the application.
    2. If your Dynamics 365 is On-premise, you can authenticate Web API by passing User’s network credentials.
    3. If your Dynamics 365 is Online or IFD, you must use OAuth to connect.
  • Refer this article on steps to connect to Web API.
  • Web API is very convenient to use and test.
  • With Postman tool you can connect to Web API and perform operations. Refer article

Below is the Flow diagram gives idea on when to use which option among the 3 options:

WebAPI_Connect.PNG

🙂

Categories: CRM, Dynamics 365 Tags: , ,