CRM 2011 user security roles listing

This SQL script will give you an result of all active users within the CRM deployment and their security roles

select

u.fullname, u.internalemailaddress, r.name as 'Security Role' from FilteredSystemUserRoles AS sr
inner

join FilteredRole as r on sr.roleid = r.roleid
inner

join FilteredSystemUser as u on u.systemuserid = sr.systemuserid
where

u.isdisabled = 0
order

by u.fullname

Enjoy!

Using Entity Framework to create or update database(Migration)

Once you define your object model in C# or VB, you can push the object to your database. Most of the case entity framework will handle the update automatically. And you dont even need to write a SQL code.
This development method also commonly know as "Code First" in Entity Framework.

However I searched numbers of blog posts and found not a lot of them mention the command to generate and update database. And here are the procedures to create or update your databases:

1. Develop your object in visual studio
2. Make sure your connection is correct and the account have permission to crate and update
3. Open package manager console (view > other windows >  package manager console)
4. Enable automatic migration if you haven't by "PM> Enable-Migrations -EnableAutomaticMigrations"
5. Update database command "PM> Update-Database"

Thanks Travis in providing these procedure.

Update status in CRM 2011

Here is the code to update a recode via CRM context object:

CRMContext.Execute(new SetStateRequest
            {
                EntityMoniker = new EntityReference
                {
                    Id = new Guid(""),
                    LogicalName = "" //Entity Name
                },
                State = new OptionSetValue(1), //get the value
                Status = new OptionSetValue(1)
            }
            );

Select top N from group

This is a simple SQL select query to illustrate how to select top (or bottom) N from each group.

Here is the data structure

Table 1: product

Prodcut_ID	Product_Name
1	Apple
2	Orange
3	Banana
4	Mango

Table 2: Review

Review_ID	Product_FK	Score
1	1	9
2	4	3
3	3	8
4	2	9
5	2	7
6	3	6
7	2	8
8	1	6
9	2	6
10	3	7
11	4	8
12	4	5

SQL query to select top review score from each product

SELECT P.Product_Name, R.Score AS 'Top Score' FROM Product AS P

LEFT JOIN Review AS R ON R.Product_FK = P.Prodcut_ID

AND R.Review_ID =

      (SELECT TOP 1 R2.Review_ID FROM Review AS R2

      WHERE R2.Product_FK = P.Prodcut_ID

      ORDER BY R2.Score DESC)

Here is the select result:

Product_Name	Top Score
Apple	9
Orange	9
Banana	8
Mango	8

You can also use the same query to get average or do other calculations

The context is not currently tracking the entity

I encountered this error when I deploy a plug-in to CRM online environment. I tested the same plug-in in on premises environment and it works without any error.

The plugin read and write to entity A and entity B (they have no relationships). The plug-in is triggered when entity A records are created and it retrieve all records from entity B and process them.

The error was thrown when it tries to save changes to entity B, with this error message: The context is not currently tracking the <<entity B>> entity

It suggested the services context does not track <<entity B>> in the scope. I researched this topic and found out I need to use OrganizationServiceContext.Attach Method. However when I use this method it returns with this error: The context is already tracking the <<entity B>> entity. :(

And did more research and found that it actually need to detach the object ( OrganizationServiceContext.Detach Method) before it can be attached.

This makes no sense to me but this is the work around.

CRM 2011 Plug-in error: Object reference not set to an instance of an object.

Problem/Error:

When I create an instance of object from the sdk generated code by using the default constructor like:

email.regardingobjectid = new CrmEntityReference();

I will find it return a null object:

This is not the result I wanted because the object does not have correct structure and when I try to assign a value to the object (in this case is regardingobjectid) it will throw a NullReferenceException error like this:

Solution

The solution is relatively simple by using a different constructor and it will give a correct object:

email.regardingobjectid = new CrmEntityReference("contact", contact.contactid);

CRM Email router time out

This summary is not available. Please click here to view the post.

CRM Online can't find Microsoft.Xrm.Client.dll

Hi all,

Today I was writing (and I am still) a Plug-in for CRM online 2011 and counter a error as:

Could not load file or assembly 'Microsoft.Xrm.Client, Version=5.0.9688.1154, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.

It happens because in my project I reference to Microsoft.Xrm.Client.dll and in the CRM online server this DLL is not installed.

I think this client dll is not intended to be used in server environment and therefore is not installed in CRM server.

I founded the generated code from crmsvcutil actually required the client dll if you include following parameter in your generation command .

/codeCustomization:"Microsoft.Xrm.Client.CodeGeneration.CodeCustomization, Microsoft.Xrm.Client.CodeGeneration"

I re-generated the code file from crmsvcutil without the parameter and reload the project with an updated code file (Xrm.cs), recompile the project and it works :)

By the way, Happy 2012 :)

Unable to connnect to CRM server (email router or Outlook client)

Today I experienced an error when connecting a outlook client to CRM server. The setting are excellently same as other computers. It turns out that the clock (on client PC) is out of sync and it could not establish a secured connect.

This is fixed by sync the time and re-connect :) not too hard after spending lot of time to find the bug.

CRM4 primary character limit

Issues:
This morning I received an error from generic SQL error when a process tried to update an account record. The details error log looks like this:

>Exception when executing non-query: update ActivityPointerBase set RegardingObjectIdName='<<A very long account name>>' where (RegardingObjectId = '5cc47836-4867-e011-8520-005056860020' and RegardingObjectTypeCode = 1) Exception: System.Data.SqlClient.SqlException: String or binary data would be truncated.

 When I look in the error, it suggests the name is too long to be put in the reference object table. I checked the name attribute in account; it is set to 450 characters. However when I look at the database table RegardingObjectName is set to 400. When it try to inset the name it errors out.

Conclusion and lesion learn:
The character limits of primary attribute in CRM is 400. This apply to both CRM 4 and CRM 2011. To fix this simply truncate the name to 400 characters and update the attribute in account to 400 characters.