Microsoft Dynamics CRM 201X: 2011

Monday, August 22, 2011

Get Database - tables size

How to get database size:

run this script:

SELECT
so.object_id AS ObjectID,
so.name AS ObjectName,
(CONVERT(decimal(20,4),(SUM (ps.reserved_page_count) * 8))) / 1024 As Reserved_MB,
(CONVERT(decimal(20,4),SUM (
            CASE
                  WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
                  ELSE ps.lob_used_page_count + ps.row_overflow_used_page_count
            END
            ) * 8)) / 1024 As Data_MB,
(CONVERT(decimal(20,4),(CASE WHEN (SUM(used_page_count)) >
            (SUM(CASE
                  WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
                  ELSE ps.lob_used_page_count + ps.row_overflow_used_page_count
            END
            )) THEN (SUM(used_page_count) -
            (SUM(CASE
                  WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
                  ELSE ps.lob_used_page_count + ps.row_overflow_used_page_count
            END
            ))) ELSE 0 END) * 8)) / 1024 As Index_MB,
(SUM (
            CASE
                  WHEN (ps.index_id < 2) THEN ps.row_count
                  ELSE 0
            END
            )) AS [RowCount]
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.objects AS so ON so.object_id = ps.object_id
WHERE so.object_id > 100
GROUP BY so.object_id, so.name
ORDER BY [Reserved_MB]Desc

Thanks,
Rami Heleg.

Friday, July 1, 2011

Server Side Example - Run query expression

Hi,
Here is an example of run query QueryExpression in CRM 2011.
Example project for Console application.

Before run the application musat add reference to dlls:
1. Microsoft.Xrm.Sdk.dll ( get this file from SDK 2011)
2. Microsoft.Crm.Sdk.Proxy.dll ( get this file from SDK 2011)

Before run the console application add Service References to:

http://ServerName:5555/XRMServices/2011/Organization.svc

Console Example:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ServiceModel.Description;
using System.Net;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk;
namespace crm2011Test1
{
    class query2
    {
        static void Main(string[] args)
        {
            Button1_Click();
        }
        static void Button1_Click()
        {
            //Authenticate using credentials of the logged in user;
            ClientCredentials Credentials = new ClientCredentials();
            Credentials.Windows.ClientCredential = CredentialCache.DefaultNetworkCredentials;
            //This URL needs to be updated to match the servername and Organization for the environment.
            Uri OrganizationUri = new Uri("http://crmserver:5555/crm2011dev/XRMServices/2011/Organization.svc");
            Uri HomeRealmUri = null;
            using (Microsoft.Xrm.Sdk.Client.OrganizationServiceProxy serviceProxy = new Microsoft.Xrm.Sdk.Client.OrganizationServiceProxy(OrganizationUri, HomeRealmUri, Credentials, null))
            {
                serviceProxy.ServiceConfiguration.CurrentServiceEndpoint.Behaviors.Add(new ProxyTypesBehavior());
                IOrganizationService service = (IOrganizationService)serviceProxy;
                // Create query in contact entity                Microsoft.Xrm.Sdk.Query.QueryExpression qe = new Microsoft.Xrm.Sdk.Query.QueryExpression();
                qe.EntityName = "contact";
                Microsoft.Xrm.Sdk.Query.FilterExpression filter1 = new Microsoft.Xrm.Sdk.Query.FilterExpression();
                filter1.FilterOperator = Microsoft.Xrm.Sdk.Query.LogicalOperator.And;
                filter1.Conditions.Add(new Microsoft.Xrm.Sdk.Query.ConditionExpression("firstname", Microsoft.Xrm.Sdk.Query.ConditionOperator.Equal, "amos"));
                filter1.FilterOperator = Microsoft.Xrm.Sdk.Query.LogicalOperator.Or;
                filter1.Conditions.Add(new Microsoft.Xrm.Sdk.Query.ConditionExpression("createdon", Microsoft.Xrm.Sdk.Query.ConditionOperator.NextXDays, 7));
                filter1.Conditions.Add(new Microsoft.Xrm.Sdk.Query.ConditionExpression("createdon", Microsoft.Xrm.Sdk.Query.ConditionOperator.NextXDays, 30));
                qe.Criteria = filter1;
                //first retrive
                EntityCollection ec = service.RetrieveMultiple(qe);
                //create loog on result
                foreach (Entity act in ec.Entities){}
                Console.ReadKey();            }
        }
    }
}

Server Side Example - Fetch

Hi,
Here is an example of update entity in CRM 2011.
Example project for Console application.

Before run the application musat add reference to dlls:
1. Microsoft.Xrm.Sdk.dll ( get this file from SDK 2011)
2. Microsoft.Crm.Sdk.Proxy.dll ( get this file from SDK 2011)

Before run the console application add Service References to:

http://ServerName:5555/XRMServices/2011/Organization.svc

Console Example:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ServiceModel.Description;
using System.Net;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk;
namespace crm2011Test1
{
    class Fetch
    {
        static void Main(string[] args)
        {
            Button1_Click();
        }
        static void Button1_Click()
        {
            ClientCredentials Credentials = new ClientCredentials();
            Credentials.Windows.ClientCredential = CredentialCache.DefaultNetworkCredentials;
            //This URL needs to be updated to match the servername and Organization for the environment.
            Uri OrganizationUri = new Uri("http://crmserver:5555/crm2011dev/XRMServices/2011/Organization.svc");
            Uri HomeRealmUri = null;
            using (Microsoft.Xrm.Sdk.Client.OrganizationServiceProxy serviceProxy = new Microsoft.Xrm.Sdk.Client.OrganizationServiceProxy(OrganizationUri, HomeRealmUri, Credentials, null))
            {
                serviceProxy.ServiceConfiguration.CurrentServiceEndpoint.Behaviors.Add(new ProxyTypesBehavior());
                IOrganizationService service = (IOrganizationService)serviceProxy;
                //fetch example
                string opportunity_count = @"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
                                              <entity name='contact'>
                                                <attribute name='fullname' />
                                                <attribute name='new_customernumber' />
                                                <attribute name='donotemail' />
                                                <attribute name='birthdate' />
                                                <order attribute='new_customernumber' descending='true' />
                                                <filter type='and'>
                                                  <filter type='or'>
                                                    <condition attribute='birthdate' operator='next-seven-days' />
                                                    <condition attribute='birthdate' operator='last-x-days' value='30' />
                                                  </filter>
                                                </filter>
                                              </entity>
                                            </fetch>";
                EntityCollection contact_count_result = service.RetrieveMultiple(new Microsoft.Xrm.Sdk.Query.FetchExpression(opportunity_count));
                //Create loop of the result                 foreach (var c in contact_count_result.Entities){}
                //end fetch example
                Console.ReadKey();
                //This code will clear the textboxes after the contact is created.
            }
        }
    }
}
Thanks,
Rami Heleg

Server Side Example - Update Entity

Server Side example – SetState

Hi,
Here is an example of setstate entity in CRM 2011.
Example project for Console application.

Before run the application musat add reference to dlls:
1. Microsoft.Xrm.Client.dll ( get this file from SDK 2011)
2. Microsoft.Xrm.Sdk.dll ( get this file from SDK 2011)
3. Microsoft.Crm.Sdk.Proxy.dll ( get this file from SDK 2011)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ServiceModel.Description;
using System.Net;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk;

namespace crm2011Test1
{
    class SetState
    {
        //static void Main(string[] args)
        //{
        //    Button1_Click();
        //}
        static void Button1_Click()
        {
            //Authenticate using credentials of the logged in user;
            ClientCredentials Credentials = new ClientCredentials();
            Credentials.Windows.ClientCredential = CredentialCache.DefaultNetworkCredentials;
            //This URL needs to be updated to match the servername and Organization for the environment.
            Uri OrganizationUri = new Uri("http://crmserver:5555/crm2011dev/XRMServices/2011/Organization.svc");
            Uri HomeRealmUri = null;
            using (Microsoft.Xrm.Sdk.Client.OrganizationServiceProxy serviceProxy = new Microsoft.Xrm.Sdk.Client.OrganizationServiceProxy(OrganizationUri, HomeRealmUri, Credentials, null))
            {
                serviceProxy.ServiceConfiguration.CurrentServiceEndpoint.Behaviors.Add(new ProxyTypesBehavior());
                IOrganizationService service = (IOrganizationService)serviceProxy;
                //set state of contact to disable (add microsoft.xrm.client.dll)
                OrganizationServiceContext osc = new OrganizationServiceContext(service);
                Microsoft.Xrm.Client.Messages.OrganizationServiceContextExtensions.SetState(osc, new EntityReference("contact", new Guid("{contactGuid}")), new OptionSetValue(1), new OptionSetValue(2));
                Console.ReadKey();
            }
        }
    }
}

Thanks,
Rami Heleg

Saturday, June 25, 2011

Web Resource Utility

Hi,
To update Javascript files in CRM 2011 two option available.
1. CRM solution, add remove, update web resource
2. Web resource Utility

Web Resource Utility is the faster way to update JS in CRM 2011.

After connect to crm need
1. Create a new Package. each package contain root folder
2. Define root Path : JS files location
3. The first grid view the list with Js files. select 1-2 files and press "Add Files to web resource"
4. Upload All button upload all the files from the second grid.
5. Upload Selected upload only the selected files

After this step user doesn't need to publish the files because it's done.

Enjoy,
Rami

Tuesday, June 21, 2011

Failed to delete or copy DLL from GAC- Global Assembly Cache

Many times I try to delete file from GAC

Make the next instructions to solve this action:

1. Open Regedit,

2. Browse to HKLM\Software\Microsoft\Fusion.

3. Add a REG_DWORD value named 'DisableCacheViewer'; set it to 1.

4. Open an Explorer window and type in C:\WINDOWS\assembly\GAC_MSIL\

Open GAC (c:\windows\assembly\msil and copy the relevant file and remove
after finish change 'DisableCacheViewer' to 0

Good luck
Rami Heleg.

Friday, June 17, 2011

Server throw exception: SecLib::CrmCheckPrivilege failed. Returned hr = -2147220960 on

Hi,
Sometimes i get error messages on server side:

<error>
   <code>0x80040220</code<
   <description>
    SecLib::CrmCheckPrivilege failed. Returned hr = -2147220960 on UserId: 59d6a36f-641d-e011-a5f3-00215aec973e and PrivilegeId: 4533fc7e-76d3-44cf-8f6e-74e1efe84c51
   </description<
   <type>Platform</type>
</error>

The reason of this message is missing Privilege to make the action.
The solution is:

1. Run sql query:
Select domainname from systemuser where systemuserid ='{UserId}'
this sql return for which user missing role for CRUD entity

2.
SELECT     * FROM         PrivilegeBase WHERE    PrincipalId = '{GuidFromErrorMessage}'
return the action that the user failed to do.
now the next step is to change role permission/privilege.

open CRM 4/2011 select user - > role -> search the entity and define the missing permission

Thanks,
Rami Heleg.
aa

Microsoft Dynamics CRM 201X