Wednesday, 8 February 2017

CRM Microsoft License Types - CalType - CRM 2013-2016


Licensing... that thing that Microsoft ask us time to time...

This post is more a reminder to myself than actually a post for posterity in order to remember what are the codes of Microsoft CRM licensing types or "CalType" in the entity "systemuser". Since i run several reports on all the Organizations I need to automatize this kind of reports so we can charge the client accordingly.

Ex:
   if (((OptionSetValue)et["caltype"]).Value == 0) // 0 = "Professional"
                       {
                          LicenseTypeProfessional++;
                          CalType = "Professional";
                        }

CALTYPES CODES:

0 Professional
1 Administrative
2 Basic
3 Device Professional
4 Device Basic
5 Essential
6 Device Essencial
7 Enterprise
8 Device Enterprise

Wednesday, 19 October 2016

Importing Solution and fixing attribute maps failures On Premise


Since the "EASY TO USE" is not a feature of the Error Log in Microsoft CRM Dynamics. Today i`m explaining in terms of attribute maps failures how can we detect the guid that show on the error log.
In most cases this issue shows up while we import a solution like for example a new version. Caused by the fact that someone has created an Unmanaged Solution and on this unmanaged solution has created an relationship with one of the entities/fields that exist on the Managed solution. the one i'm trying to import. In my case this is the most common issue and we need to identify what mapping was created so we can delete it. Just one note please DO NOTE REMOVE THE UNMANAGED SOLUTION, that will not work.
Sorry, but sometimes i get a bit nervous :D.
Now, deleting the unmanaged solution will not delete the relationship or mapping, an unmanaged solution is like a pointer, deleting that will not delete what was created (entities, fields, mappings, etc) and you lose the track of it. So... before we delete the mapping or relationship do a note for yourself that maybe needed to be created later or has the best practice we should be putting that change in the managed solution, depending on the product and you will not be doing that on Field One, ClickDimensions‎ or Adxstudio, only on your own solutions.

Beside the below script in SQL can only run On-Premise, I know that is also possible to it in CRM Online version but i didn't spend to much time on  and maybe i will change this post later and put also the code here, but in order to do that you need to create a console application.

Is something of this kind (quick search on google):

<fetch version='1.0' mapping='logical' distinct='false'>
<entity name='entitymap'>
<attribute name='sourceentityname'/>
<attribute name='targetentityname'/>
<link-entity name='attributemap' alias='attributemap' to='entitymapid' from='entitymapid' link-type='inner'>
<attribute name='sourceattributename'/>
<attribute name='targetattributename'/>
</link-entity>
</entity>
</fetch>

So, here is the SQL query to use to find the mapping based on the GUID that the CRM has given.

select TargetAttributeName, SourceAttributeName, EntityMapID
from AttributeMapm
where AttributeMapId='AttributeMapIDGUIDGoesHere'
Select TargetEntityName
from EntityMapBase
where EntityMapId = 'EntityMapIDGUIDGoesHere'

Thanks and i hope this helps you.

Tuesday, 6 September 2016

Upgrading Microsoft CRM Dynamics from 2011 to 2016

Upgrading Microsoft CRM Dynamics from 2011 to 2016 for multiple Organizations


On today’s post I will go through the upgrade multiple organizations in just one go using PowerShell or Console Application from version 2011 to 2016. So this will be a long post...

Prepare the environments:
Since we are dealing with 3 differents environments counting the 4th the source, in this case the Test or Production Environment. We need to create new 3 new environments with everything inside, CRM Dynamics, SQL, Async etc. One for 2013, one for 2015 and one for 2016.

In my case i have more then 1 organization per server and this will be really painful do it all by hand. So the script will help to speed up the upgrades and allow me to do other stuff when they are upgrading. Since this is a process that will take at least 2 hours depending on the size of the database, in some cases one day.... yes one day :( ...you cant spend time do it manually. So..

  1. If you are using VM`s or Azure or Amazon Web Services make sure the machines can see each other and if you doing clones of the VM be sure you run the "Sysperp" or you going to face problems on the CRM side and SQL server and even running the VM`s.
  2. Build the different environment installing the CRM normally in each machine with each version. (No need to create one machine to Async, other for SQL, put everything in the same machine or you will have problems and will take you more time.)
  3. After all done and CRM running on each version on each machine we can now start to build our PorwerShell or Console application
The below script will help you to do all this process automatically.




Having in mind the following:
  • Creating a copy of your production CRM database (if needed) or just do a backup.
  • The below script don't do backups, therefore you will need to create the rest of the script, however it will not be needed because the code only copy`s the Database to a new destination server.
  • This script will not upgrade your custom code / solution. For more information about how to upgrade your code see my post regarding that: http://hugorsilva.blogspot.ie/2015/03/migration-to-crm-20111315-and.html 
  • If you having problems and errors importing the Organizations make sure you don't have other organization with the same Guid, open the Deployment Management and Delete the other Org with the same Guid, this could happen if you doing Test.
  • Make sure no one break your eggs :D

Let`s Start... Powershell
Note: Before you run the script please search on the code pieces for "CHANGE TEXT HERE" to setup and configure. This is needed for you to change the Server Names, Folders and Reporting Servers URL.

Prompts:
Prompt the user to request the values for server names, database names, etc.
$newOrgName = read-host "New Organization Name ex:ContosoNew"$Server1 = read-host "2011 Source Server"$Server2 = read-host "2013 Destination Server"$Server3 = read-host "2015 Destination Server"$Server4 = read-host "2016 Destination Server"$Db1 = read-host "Source Database Name ex:Contoso_MSCRM"$Db2 = read-host "Destination Database Name ex:ContosoNew_MSCRM"
$appSVR = read-host "Application Server"

SQL Function:
This will be used to copy the database from one server to other, like server1 to server2 and so on. Create the variables need on your code to do the connection and assign paths.

function SQLCopy {[cmdletBinding(DefaultParametersetName='WindowsAuthentication')]param(                [Parameter(Mandatory=$true, ParameterSetName = 'WindowsAuthentication')]                [Parameter(Mandatory=$true, ParameterSetName = 'SQLAuthentication')]                [ValidateNotNullOrEmpty()]                [alias('Source', 'Src')]                [System.String]                $SourceInstance                ,                [Parameter(Mandatory=$true)]                [ValidateNotNullOrEmpty()]                [alias('SourceDb', 'SrcDb', 'Database')]                [System.String]                $SourceDatabase                ,                [Parameter(Mandatory=$true)]                [ValidateNotNullOrEmpty()]                [alias('Target', 'Tgt')]                [System.String]                $TargetInstance                ,                [Parameter(Mandatory=$false)]                [ValidateNotNullOrEmpty()]                [alias('TargetDb', 'TgtDb', 'RenameAs')]                [System.String]                $TargetDatabase = $SourceDatabase                ,                [Parameter(Mandatory=$true)]                [ValidateNotNullOrEmpty()]                [alias('Path')]                [System.String]                $BackupDirectoryPath # <--<-- CHANGE TEXT HERE - Add folder name and create variable                ,                [Parameter(Mandatory=$true, ParameterSetName = 'SQLAuthentication')]                [ValidateNotNull()]                [System.String]                $Username                ,                [Parameter(Mandatory=$true, ParameterSetName = 'SQLAuthentication')]                [ValidateNotNull()]                [System.String]                $Password                ,                [Parameter(Mandatory=$false)]                [Switch]                $Force = $false)



Migration Function:
This will copy the database from Server1 (Source) to Server2 (Destination) by calling the  the function SQLCopy and will import the Organization into the CRM 2013 and will do upgrade after. To repeat the process for 2015 and 2016 you only need to duplicate the following code with the different name servers, folders and functions, changing also 2013 to 2015 and so on. Bear in mind if you change the variables names you need to change also on the prompt variables.

function 2013Merge {                New-Item "\\2013DestinationServer\migration\$Db1\2013$Db1" -type directory # <--<-- CHANGE TEXT HERE - Dont forget to create the folder
SQLCopy -SourceInstance $Server1 -SourceDatabase $Db1 -TargetInstance $Server2 -TargetDatabase $Db2 -BackupDirectoryPath \\2013DestinationServer\migration\$Db1\2013$Db1 # <--<-- CHANGE TEXT HERE - Change folder name                Invoke-Command -ComputerName $Server2 -ScriptBlock {
                }                Invoke-Command -ComputerName $Server2 -ScriptBlock {                                New-ItemProperty HKLM:\SOFTWARE\Microsoft\MSCRM -Name "MergeBaseAndExtensionTables" -Value "0" -PropertyType dword
                                Set-ItemProperty HKLM:\SOFTWARE\Microsoft\MSCRM -Name "MergeBaseAndExtensionTables" -Value "0"                      
                C:\Windows\Microsoft.NET\Framework64\v4.0.30319\installutil.exe "C:\Program Files\Microsoft Dynamics CRM\Tools\Microsoft.Crm.PowerShell.dll" # <--<-- CHANGE TEXT HERE - Change Files Origin                Add-PSSnapin Microsoft.Crm.PowerShell
                $opId = Import-CrmOrganization -SqlServerName $($args[0]) -DatabaseName $($args[1]) -SrsUrl "https://2013DestinationServer/ReportServer" -DisplayName $($args[2]) -Name $($args[2]) -UserMappingMethod KeepExisting # <--<-- CHANGE TEXT HERE - Change Reporting Server URL
                $opStatus = (Get-CrmOperationStatus -OperationId $opId).State                Write-Output "Starting 2013 Import"
                While ($opStatus -ne 'Completed')
                                {                                                Write-Output " 2013 In progress..."                                                Start-Sleep -Seconds 30                                                $opStatus = (Get-CrmOperationStatus -OperationId $opID).State                                }
                Write-Output "Import 2013 Completed!"
                Set-ItemProperty HKLM:\SOFTWARE\Microsoft\MSCRM -Name "MergeBaseAndExtensionTables" -Value "1"
                New-ItemProperty HKLM:\SOFTWARE\Microsoft\MSCRM -Name "EnableRecreateCustomIndexes" -Value "1" -PropertyType dword
       Set-ItemProperty HKLM:\SOFTWARE\Microsoft\MSCRM -Name "EnableRecreateCustomIndexes" -Value "1"
                Write-Output "Disabling Org and Starting Table Merge"
                Disable-CrmOrganization -Name $($args[2])
                              } -argumentlist $Server2, $Db2, $newOrgName                               Invoke-Command -ComputerName $Server2 -ScriptBlock {
                                New-Item "c:\CRMMigration" -type directory                                C:\"Program Files"\"Microsoft Dynamics CRM"\tools\CrmMergeBaseAndExtensionTableTool.exe /s:2013DestinationServer /o:$($args[0]) /log:c:\CRMMigration\mergelog.txt # <--<-- CHANGE TEXT HERE - Change Destination Files, folder and Server Name
                                C:\Windows\Microsoft.NET\Framework64\v4.0.30319\installutil.exe "C:\Program Files\Microsoft Dynamics CRM\Tools\Microsoft.Crm.PowerShell.dll"                                Add-PSSnapin Microsoft.Crm.PowerShell                                Enable-CrmOrganization -Name $($args[1])
                } -argumentlist $Db2, $newOrgName}



You can always check if the upgrading is running well by open the Deployment Manager and if the organization is on the Status "Pending" means that the Deployment Manager is doing the upgrade, this may take several hours... so sit back and relax :D

Common Issues:

  • If you get any error will show up on the Powershell and also on the Log located at c:\CRMMigration\mergelog.txt (Code removed for professional reasons)
  • You could also have the following issue in case you import the Organization manually, don't worry this is a normal alert.



Thank you and enjoy.

Upgrading Microsoft CRM Dynamics from 2011 to 2016

Upgrading Microsoft CRM Dynamics from 2011 to 2016 for multiple Organizations


On today’s post I will go through the upgrade multiple organizations in just one go using PowerShell or Console Application from version 2011 to 2016. So this will be a long post...

Prepare the environments:
Since we are dealing with 3 differents environments counting the 4th the source, in this case the Test or Production Environment. We need to create new 3 new environments with everything inside, CRM Dynamics, SQL, Async etc. One for 2013, one for 2015 and one for 2016.

In my case i have more then 1 organization per server and this will be really painful do it all by hand. So the script will help to speed up the upgrades and allow me to do other stuff when they are upgrading. Since this is a process that will take at least 2 hours depending on the size of the database, in some cases one day.... yes one day :( ...you cant spend time do it manually. So..

  1. If you are using VM`s or Azure or Amazon Web Services make sure the machines can see each other and if you doing clones of the VM be sure you run the "Sysperp" or you going to face problems on the CRM side and SQL server and even running the VM`s.
  2. Build the different environment installing the CRM normally in each machine with each version. (No need to create one machine to Async, other for SQL, put everything in the same machine or you will have problems and will take you more time.)
  3. After all done and CRM running on each version on each machine we can now start to build our PorwerShell or Console application
The below script will help you to do all this process automatically.




Having in mind the following:
  • Creating a copy of your production CRM database (if needed) or just do a backup.
  • The below script don't do backups, therefore you will need to create the rest of the script, however it will not be needed because the code only copy`s the Database to a new destination server.
  • This script will not upgrade your custom code / solution. For more information about how to upgrade your code see my post regarding that: http://hugorsilva.blogspot.ie/2015/03/migration-to-crm-20111315-and.html 
  • If you having problems and errors importing the Organizations make sure you don't have other organization with the same Guid, open the Deployment Management and Delete the other Org with the same Guid, this could happen if you doing Test.
  • Make sure no one break your eggs :D

Let`s Start... Powershell
Note: Before you run the script please search on the code pieces for "CHANGE TEXT HERE" to setup and configure. This is needed for you to change the Server Names, Folders and Reporting Servers URL.

Prompts:
Prompt the user to request the values for server names, database names, etc.
$newOrgName = read-host "New Organization Name ex:ContosoNew"$Server1 = read-host "2011 Source Server"$Server2 = read-host "2013 Destination Server"$Server3 = read-host "2015 Destination Server"$Server4 = read-host "2016 Destination Server"$Db1 = read-host "Source Database Name ex:Contoso_MSCRM"$Db2 = read-host "Destination Database Name ex:ContosoNew_MSCRM"
$appSVR = read-host "Application Server"

SQL Function:
This will be used to copy the database from one server to other, like server1 to server2 and so on. Create the variables need on your code to do the connection and assign paths.
function SQLCopy {Code removed for professional reasons
)
Migration Function:
This will copy the database from Server1 (Source) to Server2 (Destination) by calling the  the function SQLCopy and will import the Organization into the CRM 2013 and will do upgrade after. To repeat the process for 2015 and 2016 you only need to duplicate the following code with the different name servers, folders and functions, changing also 2013 to 2015 and so on. Bear in mind if you change the variables names you need to change also on the prompt variables.

function 2013Merge {
Code removed for professional reasons
}


You can always check if the upgrading is running well by open the Deployment Manager and if the organization is on the Status "Pending" means that the Deployment Manager is doing the upgrade, this may take several hours... so sit back and relax :D

Common Issues:

  • If you get any error will show up on the Powershell and also on the Log located at c:\CRMMigration\mergelog.txt (Code removed for professional reasons)
  • You could also have the following issue in case you import the Organization manually, don't worry this is a normal alert.



Thank you and enjoy.

Friday, 15 July 2016

How to Improve CPU rate on Microsoft Dynamics CRM on-premise


Machines are "intelligent" as well Microsoft CRM Dynamics and they manage the resources very well especially the last versions of  Microsoft CRM Dynamics like 2016, however we need sometimes to give a boost on our CRM on-premise.

So one of the things is related to the CPU utilization by a certain Organization.  



So we should look at some fragmented indexes and make sure we clean the house, therefore here follows a way to index that was used and drop the CPU rate for better values.

USE [Contoso_MSCRM] GO CREATE NONCLUSTERED INDEX [fndx_StateCode_OverwriteTime_ComponentState] ON [dbo].[SdkMessageProcessingStepBase](       [StateCode] ASC,       [OverwriteTime] ASC,       [ComponentState] ASC)INCLUDE (     [PluginTypeId],       [SdkMessageId],       [SdkMessageFilterId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) GO

Thursday, 21 April 2016

Using Selenium for Microsoft Dynamics CRM (C#, PhantomJS and Chrome)


Many times we need a tool that allow us to automate several processes in order to test the performance, monitoring or simply looking for bug that could be caused by new releases. So in order to accelerate high quality software delivery, we need to perform load testing, continuous testing, extensible automated test, etc.

The Selenium is a suite of tools to automate web browsers across many platforms and allow the use of several browser drivers. We can control the execution of tests and the comparison of actual outcomes with predicted outcomes. The test automation can automate some repetitive but necessary tasks in a formalized testing process already in place, or add additional testing that would be difficult to perform manually, for example trying to log in into CRM with different users to check if the Business Rule are correct for each user.

In this example I show how to use Selenium for Microsoft Dynamics CRM, using PhantomJS and Chrome.



Download Source Code

Thursday, 31 March 2016

Generic SQL Error – RetriveMultiple ConditionOperator Equal Like and Contains



Today someone "me" wanted to change the requirements of the wrap I have over the CRM web services so I could reuse the method more often, but to do so I need to transform the method more standard. So I changed the ConditionOperator.Equal to ConditionOperator.Contains. But was giving me an error "Generic SQL Error" while using in the ConditionExpression. The requirement was to get all the records from an entity that contains a specific field with a certain attribute value.

Before, using Equal:
Microsoft.Xrm.Sdk.Query.ConditionExpression condition = new Microsoft.Xrm.Sdk.Query.ConditionExpression(FieldName), Microsoft.Xrm.Sdk.Query.ConditionOperator.Equal, AttributeValue);

After, using Contains: You need to add % to the attributevalue but don’t work if the table or indexed view is not full-text indexed, therefore you need to use Like.
Microsoft.Xrm.Sdk.Query.ConditionExpression condition = new Microsoft.Xrm.Sdk.Query.ConditionExpression(FieldName), Microsoft.Xrm.Sdk.Query.ConditionOperator.Contains, “%”+AttributeValue+“%”);

After, using Like: You need to add % to the attributevalue
Microsoft.Xrm.Sdk.Query.ConditionExpression condition = new Microsoft.Xrm.Sdk.Query.ConditionExpression(FieldName), 
Microsoft.Xrm.Sdk.Query.ConditionOperator.Like, “%”+AttributeValue+“%”);