SQL divide by zero "isZero" function

Recently come across a requirement on SQL to divide 2 columns and populate in a custom fields. Infact is just something simple like "SELECT column1/column2 AS result"


Issue raised because the "column2" might contains zero or null value, what we need to do is if column 2 is zero replace it with another fix non zero varible.

• Null issue can be easily solve by using isNull finction, but we don't have 'isZero' function

The solution is to use the nullif function with isnull function;
set column2 to null value if it is zero, outer level if is null replace with variable X.

ISNULL(NULLIF(column2, 0),variableX)

CRM 4.0 Update Rollup 11

Latest CRM 4.0 rollup 10 released on 3/June/2010.
Rollup 11 can be uninstall on server
Rollup 11 is cumulative.
Rollup 1,2,3,4,5,6,7,8,9,10 are not prerequisites for server portion.
Update Rollup 7 is a prerequisite for Microsoft Dynamics CRM for Microsoft Office Outlook and Microsoft Dynamics CRM Data Migration Manager.
Special Considerations:


KB: KB981328
Download HERE

CRM 4 Build Number - Version

Version	Build Number	Release on	Link ID(en)	Patch ID(en)
RTM	4.0.7333.3	12/19/2007
Rollup 1	4.0.7333.1113	11/24/2008
Rollup 2	4.0.7333.1312, 4.0.7333.1316	1/15/2009, 2/8/2009
Rollup 3	4.0.7333.1408	3/12/2009
Rollup 4	4.0.7333.1551	5/7/2009	150735&clcid=0x409	{004A7E60-5DB7-4F05-B7C1-1D2DD653A1A6}
Rollup 5	4.0.7333.1644, 4.0.7333.1645	7/2/2009	157191&clcid=0x409	{592DDF5E-D0D7-46FE-81C3-A3BCAC5E8F22}
Rollup 6	4.0.7333.1750	9/27/2009	162439&clcid=0x40	{7E83D1E5-7B7A-45D1-8930-4FEFB4123B57}
Rollup 7	4.0.7333.2138	10/22/2009	167830&clcid=0x409	{657DF4CA-4167-48C2-937E-1B4940C0F3C9}
Rollup 8	4.0.7333.2542	12/17/2009	179669&clcid=0x409	{7D1C2874-9093-4563-83C6-C2F82C8D82B8}
Rollup 9	4.0.7333.2644	02/11/2010	181054&clcid=0x409	{45859F4D-AC35-4FB3-A474-40492079C74A}
Rollup 10	4.00.7333.2741	4/8/2010	184388&clcid=0x409	{C853A15A-5546-4F07-BF41-FC1C64570AB3}
Rollup 11	4.0.7333.2861	6/3/2010	192046&clcid=0x409	{77F3EBA0-20E8-403E-B43B-E5AC633E139E}
Rollup 12	4.0.7333.2935	7/30/2010	194246&clcid=0x409	{99DA550E-457E-4339-A520-414AC14AE82C}
Rollup 13	4.0.7333.3018	9/23/2010	199444&clcid=0x409	{414017EA-201F-401D-BD4D-FDB8C2FD5AC8}
Rollup 14	4.00.7333.3135	11/18/2010	202910&clcid=0x409	{3A11783F-F1EF-431C-AB61-0B165502DBF0}
Rollup 15	4.00.7333.3231	1/10/2010	206903&clcid=0x409	{89833E9D-C22F-4EB9-A76F-A84623D67613}
Rollup 16	4.00.7333.3335	3/10/2011	209024&clcid=0x409	{6EA3A2FA-08DF-40B5-842B-1C6B6EC6B028}
Rollup 17	4.00.7333.3414	5/5/2011	214427&clcid=0x409 8	{87297B34-767B-4E3B-A51B-C89DB849AE82}
Rollup 18	4.00.7333.3531	6/30/2011	218855&clcid=0x409	{E054D596-F999-4BB2-9130-662E11CC26FF}
Rollup 19	4.00.7333.3628	8/25/2011	224158&clcid=0x409	{2DEE4EFD-B1CF-49CA-A37E-DD9BE6C8E2B7}
Rollup 20	4.00.7333.3732	11/10/2011	227975&clcid=0x409	{A4CEC8ED-18B4-4682-9D4D-1BD0C4FC85AC}

CRM 4.0 Update Rollup 10

Latest CRM 4.0 rollup 10 released on 8/April/2010.
Rollup 10 can be uninstall on server
Rollup 10 is cumulative.
Rollup 1,2,3,4,5,6,7,8,9 are not prerequisites for server portion.
Update Rollup 7 is a prerequisite for Microsoft Dynamics CRM for Microsoft Office Outlook and Microsoft Dynamics CRM Data Migration Manager.

Special Considerations:

1. 64-bit versions of Office 2010 will not be supported in Microsoft Dynamics CRM 4.0.
2. With Update Rollup 8 installed, Microsoft CRM E-mail Router Service supports Microsoft Exchange 2010. However, because of an issue with MAPI, the Rule Deployment Wizard does not support Microsoft Exchange 2010. This issue will be addressed in a future update of Microsoft Exchange 2010.

KB: KB979347
Download HERE

Stop previous workflow instance.

Recently come across a business scenario where a workflow needs to be run after record being assign to others. The problem is workflow configured as wait after 1 hour, then update a field.


It is possible that during the 1 hour waiting time, user might reassign again~~again, result of that is multiple same workflow actually is running and waiting at the same time...what we want is just the running the latest workflow instance only.


To solve this problem, create a CRM 4 plugin to query related workflow with specific workflow name, check its status then update it to cancelled.


Register as pre-stage; synchronous, so that it won't cancelled the latest workflow instance .

string error = string.Empty;

   ColumnSet colsWf = new ColumnSet(new string[] { "name", "statuscode", "asyncoperationid", "regardingobjectid" });

   ConditionExpression conditionName = new ConditionExpression("name", ConditionOperator.Equal, workflowName);
   ConditionExpression conditionRegardingObjectId = new ConditionExpression("regardingobjectid", ConditionOperator.Equal, recordId);

   FilterExpression filter = new FilterExpression();
   filter.FilterOperator = LogicalOperator.And;
   filter.AddCondition(conditionName);
   filter.AddCondition(conditionRegardingObjectId);

   QueryExpression query = new QueryExpression(EntityName.asyncoperation.ToString());
   query.Criteria = filter;
   query.ColumnSet = colsWf;

   BusinessEntityCollection results = service.RetrieveMultiple(query);
   if (results.BusinessEntities.Count > 0)
   {
    for (int i = 0; i < results.BusinessEntities.Count; i++)
    {
     asyncoperation singleWorkflowInstance = (asyncoperation)results.BusinessEntities[i];
     if (singleWorkflowInstance.statuscode.Value == AsyncOperationStatus.WaitingForResources || singleWorkflowInstance.statuscode.Value == AsyncOperationStatus.Waiting || singleWorkflowInstance.statuscode.Value == AsyncOperationStatus.InProgress || singleWorkflowInstance.statuscode.Value == AsyncOperationStatus.Pausing)
     {
      try
      {
       Status statusCanceled = new Status();
       statusCanceled.Value = AsyncOperationStatus.Canceled;

       AsyncOperationStateInfo state = new AsyncOperationStateInfo();
       state.Value = AsyncOperationState.Completed;

       SetStateWorkflowRequest request = new SetStateWorkflowRequest();
       singleWorkflowInstance.statuscode = statusCanceled;
       singleWorkflowInstance.statecode = state;

       TargetUpdateAsyncOperation operation = new TargetUpdateAsyncOperation();
       operation.AsyncOperation = singleWorkflowInstance;

       UpdateRequest update = new UpdateRequest();
       update.Target = operation;
       UpdateResponse updated = (UpdateResponse)service.Execute(update);
      }
      catch (System.Web.Services.Protocols.SoapException ex)
      {
       error = "KillWorkflow Error " + ex.Message + "" + ex.StackTrace;
      }
      catch (Exception ex)
      {
       error = "KillWorkflow Error " + ex.Message + "" + ex.StackTrace;
      }
     }
    }
    if (!string.IsNullOrEmpty(error))
    {
     throw new Exception(error);
    }
   }