Executing SQL queries in CRM database on behalf of another user

If you are using filtered views in SSMS it is not possible to see what data will be returned for other users. But there is a way!

The GUID of the user is stored in the SQL CONTEXT_INFO. All you have to do is to run this piece of code:

DECLARE @binUserGuid VARBINARY(128)

DECLARE @userGuid UNIQUEIDENTIFIER

SET @userGuid = 'GUID of the user you want to test'

SET @binUserGuid = CAST(@userGuid AS VARBINARY(128))

SET CONTEXT_INFO @binUserGuid

From now on you will be able to query the data as if you would be logged in as another user.

To clear the context info simply execute

SET CONTEXT_INFO 0x

I hope this helps those who are testing SQL queries for the CRM reports.

Have some respect!

Over the past five years I have been more and more involved as a speaker at the conferences. I had my first session at the meeting of Slovenian Developer Group (sloDUG), then soon my first session in English language happened at SQLSaturday Slovenia.

For those who don't know SQLSaturday events, these are FREE 1-day SQL training events made possible first by the organizers, who devote their free time to organize the whole event from scratch. Then there are sponsors who provide necessary funds for the event (renting the venue,  coffee and lunch for attendees, evening meal for speakers). And the last, but not least, there are the speakers who arrive from all over the globe, live on their own expenses and speak for free. Yes that's right! Free of charge. Even some sponsors are doing this just because they like the idea. All just for you knowledge thirsty enthusiasts. It's called giving back to the community. I too was once at the other end of the room listening to the speakers and admiring their enthusiasm.

Yes I do it for free, but I get paid with more than money, and I am sure that I am speaking on behalf of vast majority speakers at SQLSaturday. First there is this great feeling of spreading the knowledge when I deliver a good session and receive good feedback. Even better are the emails or tweets I receive after where people are commenting on the session or asking questions. But the best part of this whole thing are amazing friendships I have made with amazing people. We live in different countries and this is one of the ways we are using to meet again and hang together for a few days. Personally I always try to make this also into a short tourist trip, therefore I arrive at least a day early and leave at least a day later so I can explore the city and the country where I am speaking at.

Why am I writing this you ask? It all started with this tweet:

The obligatory 'turn it off and back on again' moment strikes again... #sqlsatmanc pic.twitter.com/LrS5ooQIXN

— Terence Burridge (@CodebunTes) July 15, 2017

It happens and people tweet about it, no big deal. Some find it funny, some are feeling sorry for the speaker, and there are also some who take things like this personally.

Let's make it clear. Free event or not, speakers should prepare their sessions and themselves professionally. Go through the slides, the demos, see how much time you need and always think of what can you improve to make it better next time.

I'm not defending anyone here and I don't want to point any fingers, but then this tweet happened:

Wait what? Have some respect? How about you showing some respect? This "incompetent" speaker is one of the best professionals in his area of expertise and has taken his free time, paid for the trip and came to present to you for free so you could call him incompetent!

I just happened to be at this session. The speaker followed all the rules in the unwritten presenters rulebook. Two most important of them are: turn off all applications not needed for the session and prepare the scripts, do not type.

In the beginning everything went OK, but then scripts just failed to execute at one point. Then he started to type the commands 😐Of course this didn't work and at the end he decided to restart the computer. While restarting the computer he killed the time by explaining some things where demos were not need and had a short Q&A session. Basically he did everything he could do to make things better. Of course precious time was lost and he could not show everything he wanted to show in his session.

Boo-hoo, so what - it happens. Shit happens and it is called "The wrath of the demo gods". No matter how well you prepare yourself, there is always a chance that something will fail and eventually it will. The worst are online demos. The probability of connectivity problems in unimaginably high at the conferences. I myself have experienced "The wrath" twice. Both times there was something with SQL Management Studio. I restarted it and voila! Problem solved. In this case the restart of computer was needed, so it took a little longer to do so. Here the "incompetence" of the speaker is on display. Some would freeze and just wait in awkward silence for the computer to restart, some would try to kill the time with bad jokes 😬, but here the session actually continued.

I am sure that the author of the tweet above has seen better sessions but please don't judge people before you've walked a mile in their shoes.

So please have some respect for the speakers!

Error when opening an inactive entity

After migrating from CRM 2015 to CRM 2016 I experienced strange behavior at some inactive entities. Immediately after opening it I got an error:

The object cannot be updated because it is read-only

Wait what? I did not change any data! Why this error? Disabled all javascript, disabled all plugins, the error was still there.

We introduced business process flow some months ago in this entity. The old entities had no process and stage ID defined. CRM 2016 wants to fix this immediately when dealing with an entity that has no process or stage defined and sets the default values.

But there is a bug in CRM. The update is made on inactive entities as well. Thus the error.

The only solution is to update the values directly in the database: 

UPDATE ENTITY SET ProcessId = PROCESSGUID , StageId = STAGEGUID  WHERE (ProcessId is null or StageId IS NULL) AND StateCode = 1

Do not name the action parameter "Error"

If you create an output action parameter named "Error" and you assign a value to it using "Assign Value step" the action will be published normally, but when trying to execute it, you will get the message:

The following errors were encountered while processing the workflow tree:
'DynamicActivity': The private implementation of activity '1: DynamicActivity' has the following validation error:   Compiler error(s) encountered processing expression 

Just rename the parameter and the error will be gone.

CRM 2016 Update lookup field bug

I encountered a strange behavior after upgrading on premise CRM 2015 to 2016 version. 

In the account form after I set the value of the custom lookup field (lookup to custom Country entity) I got javascript error: 

SCRIPT5007: Unable to get property 'trim' of undefined or null reference

After some hours of debugging I removed all javascript code. Then I copied the example from SDK, the error was still there.

Investigating the setValue method I discovered, that something like "internalOnChange" was called. I suspect this method was called because there are some filtered lists in the page that depend on the value of this field.

Long story short, there are two ways to fix this:

Method 1:

Go to Settings-Administration-System settings. All the way down at the end of the page enable "Use legacy form rendering".

This will definitely slow down form loading.

Method 2:

The code deep down in the out-of-the-box CRM javascript libraries the script wants to parse the attribute type, but it is not there. The value should be "lookup" and you should pass it in the object when calling setValue.

//Create the lookup value object

var lookupReference = [];

lookupReference[0] = {};

lookupReference[0].id = "{a0c68c89-9752-e311-93f9-00155d78043f}";

lookupReference[0].entityType = "new_entityname";

lookupReference[0].name = "Display name";

//Magic happens here

lookupReference[0].type = "lookup";

    

//Update the lookup field   

Xrm.Page.getAttribute("new_entitynameid").setValue(lookupReference);

Sitemap error because of missing LCID

I accidentally discovered the following “feature”: All LCID Titles in SubArea must have matching LCID in Group Titles.

Add a group in sitemap, like this:

<Group Id="Demo">
  <Titles>
    <Title LCID="1033" Title="Demo"/>
  </Titles>
  <SubArea Id="DemoPage" Icon="iconUrl" Url="someUrl">
    <Titles>
      <Title LCID="1033" Title="Demo Page"/>
      <Title LCID="1060" Title="Demo Stran"/>
    </Titles>
  </SubArea>    
</Group>

The sitemap will upload normally and pass all validation, but after switching to language 1060, the error will be shown. The cause is missing LCID 1060 in Group Titles.

The correct XML for this example is:

<Group Id="Demo">
  <Titles>
    <Title LCID="1033" Title="Demo"/>
    <Title LCID="1060" Title="Demo"/>
  </Titles>
  <SubArea Id="DemoPage" Icon="iconUrl" Url="someUrl">
    <Titles>
      <Title LCID="1033" Title="Demo Page"/>
      <Title LCID="1060" Title="Demo Stran"/>
    </Titles>
  </SubArea>    
</Group>

Problem fixed: Installing Rollup 8 for Data Migration Manager

I installed a fresh instance of Microsoft Dynamics CRM Data Migration Manager. Then I wanted to apply Rollup 8 to it.

Error: DMClient KB971782 is not found

Resolution: First install  Rollup 7 update for DM Client (http://www.microsoft.com/downloads/details.aspx?FamilyID=A4893988-7804-4E23-AB58-740441CC696E&displaylang=en) and then you are able to install Rollup 8.

Error: The specified type was not recognized: name='usersettings'

Simple task: Try to retrieve user settings of a CRM user.

Simple solution:
RetrieveUserSettingsSystemUserRequest request = new RetrieveUserSettingsSystemUserRequest();
request.EntityId = systemUserId;
request.ColumnSet = new AllColumns();

RetrieveUserSettingsSystemUserResponse response= (RetrieveUserSettingsSystemUserResponse)crmService.Execute(request);

NOT!

The specified type was not recognized: name='usersettings', namespace='http://schemas.microsoft.com/crm/2007/WebServices', at <BusinessEntity xmlns='http://schemas.microsoft.com/crm/2006/WebServices'>.

 

Real solution:

Add the following line: request.ReturnDynamicEntities = true;

Microsoft CRM 4.0 – Applications certification and SQL 2008

On Microsoft learning page (http://www.microsoft.com/learning/en/us/certification/dynamics-crm-professional.aspx#tab2) it is said that exam 70-431 - Microsoft SQL Server 2005 – Implementation and Maintenance is elective for Microsoft Certified Business Management Solutions Professional – Applications for Microsoft Dynamics CRM 4.0 certification.

There is nothing said about SQL 2008 exams on this page.

So I sent an email to Microsoft and here is their response:

Dear Dejan,
Thank you for your email.
We are glad to inform you that the Exam 70-432: TS: Microsoft SQL Server 2008, Implementation and Maintenance can be counted as an elective for Microsoft Certified Business Management Solutions Professional – Applications for Microsoft Dynamics CRM 4.0 certification.
As that new exam has been released recently, it has not been added to the certification path yet.
Please note that the update of the Certification Tree has been planned for the end of September 2009.
Then, the exam 70-431 will be visible in the certification path for
Applications for Microsoft Dynamics CRM4.0 certification on the
Microsoft Learning website, as that certification will be uploaded to the Transcripts of all customers who have completed the certification requirements by passing exam 70-432.
We hope that provided information is of assistance.
If you have any further questions please get in touch with us and we will be happy to assist you in any way we can.

Link to custom entity – use of etn instead of etc argument

If you want to create a direct link to an out-of-a-box entity it is a very simple task: Right-click on one instance (let’s say account) and select “Copy Shortcut”.

Then just just change the GUID in the URL to open desired account. For the Account this URL looks like this: http://SERVER:PORT/OrganizationName/sfa/accts/edit.aspx?id={GUID}

Custom entities

All custom entities have the same URL structure: http://SERVER:PORT/OrganizationName/userdefined/edit.aspx?id={GUID}&etc=EntityTypeCode

Notice the entity type code? This code is created when creating the entity. But for some reason Entity Type Code can be (in most cases) different on every CRM instance. This means that if you are creating a plugin that needs to create a link to the entity, you will have to query metadata to get the code or enter that code in configuration part.

But there is a better way:

Instead of “etc” argument you can use “etn” (Entity Name) in the query string. Then use the logical name of the entity. Logical name is the same on all instances.

http://SERVER:PORT/OrganizationName/userdefined/edit.aspx?id={GUID}&etn=EntityName

http://testsrv:5555/Contoso/userdefined/edit.aspx?id={08B91EBF-525A-DE11-94A6-002354F5ABA2}&etn=new_testentity