Ricky Safford Development Repository

Welcome to Development Repository for All

Dynamics CRM 365 Portal Extensibility: Leveraging Data Externally Using PowerBI IFRAME & Views — August 4, 2017

Dynamics CRM 365 Portal Extensibility: Leveraging Data Externally Using PowerBI IFRAME & Views

Dynamics CRM 365 Portal Extensibility: Leveraging Data Externally Using PowerBI IFRAME & Views  

 

Synopsis:

To show a report through, dashboard/ view and or report within Dynamics CRM the utilization of Microsoft Power BI to render the same report as in Dynamics CRM Native Reporting would be displayed through the use of Microsoft Power BI. I have written a Blog article on how to create Dynamics CRM 365 Dashboard leveraging Power BI “https://rickysafford.wordpress.com/2017/02/17/dynamics-365-adding-microsoft-power-bi-visualization-to-system-dashboard-through-iframe/”. I have also in this article focused on how to display a view externally through a custom portal within Dynamics CRM 365 seen below.

 

Portal Utilization:

1.       Displaying of System Dashboard via Dynamics CRM 365 Online using Power BI External Iframe
 Portal1_REF_PowerBI

 

2.       Dynamics CRM View Selected to be displayed in the Custom Portal External to Dynamics CRM
 Portal2_REF

 

3.       Setting Up Entity List to be used as view within Custom Portal
 Portal3_REF

 

4.       Setting Up New Entity List (Account View) to be used as view within Custom Portal
 Portal4_REF

 

 

Dynamics CRM Development! Using Set Submit Mode with JavaScript on Inactive Records (Read Only) to overwrite locked values — July 23, 2017

Dynamics CRM Development! Using Set Submit Mode with JavaScript on Inactive Records (Read Only) to overwrite locked values

Using SetSubmit Mode using JavaScript on Inactive Records (Read Only) to overwrite locked values

 

Overview: Using Set Submit Mode with JavaScript Form Library:  The current JavaScript will overwrite the locked value on the form. Both fields are custom fields to show proof of concept on how to utilize the set submit mode via On Save Event on the form to over write the current restricted field value.  This method will also include a on-demand workflow to make the update to the closed record.

 

Components

  1. 1 JavaScript Form Library
  2. 1 Event Handler
  3. 1 On Demand Workflow

 

1.       Identifying the Inactive Record Record
 Opp1_Com_Real.jpg

 

 

2.       After Identifying record, we want to update the Estimated Revenue Field and Actual Revenue Fields.  We now verify the field name through > Field Properties Identifying Estimated Revenue Value
 opp2_COM_Real

 

3.       Adding New JavaScript to Form Library from Form Properties
 opp2_COM.jpg

 

4.       JavaScript Used
//======================

function Form_OnSave()

 

{

 

var esttotal    =  Xrm.Page.getAttribute(“estimatedvalue”).getValue();

 

Xrm.Page.getAttribute(“estimatedvalue”).setSubmitMode(“always”);

Xrm.Page.getAttribute(“estimatedvalue”).setValue(esttotal);

 

 

 

 

}

 

5.       Event Handler to call the Form library
 opp9_Event_Handler

 

 

6.       Save, then Publish to commit the newly added JavaScript Form Library changes(Also Added Estimated Value and Actual Values to the Form)
 opp6_COM_Real.jpg

 

 

7.       Actual Revenue Value not updated due to set submit mode not being called within the Initial JavaScript. Using SetSubmit Mode on the Load of the Form will trigger the value to be saved regardless if the record is Inactive or Active. Take heed when using this functionality and make sure all steps are documented and fully explained just in case!
 opp6_Actual_Revenue_COM_COND.jpg

 

8.       Added JS Code for Actual Reveune
//======================

function Form_OnSave()

 

{

 

var esttotal    =  Xrm.Page.getAttribute(“estimatedvalue”).getValue();

 

Xrm.Page.getAttribute(“estimatedvalue”).setSubmitMode(“always”);

Xrm.Page.getAttribute(“estimatedvalue”).setValue(esttotal);

 Xrm.Page.getAttribute(“actualvalue”).setSubmitMode(“always”);

 

 

 

 

}

 

9.       Workflow Overview
 opp10_WorkFlow.jpg

 

10.       WorkFlow Process Field Updates
 opp11_WorkFlow.jpg

 

11.       Running Workflow
 opp12_WorkFlow.jpg

 

 

12.       Added JS Code View Form Update Success via Set Submit Mode
 opp7_Updated_COM.jpg

 

 

13.   List View of Updated Inactive Read Only Lost Opportunity Actual Revenue Field!
 opp8_Updated_COm.jpg

 

 

 

 

 

 

Record Cleansing in Dynamics CRM Using Scribe Insight — July 16, 2017

Record Cleansing in Dynamics CRM Using Scribe Insight

Overview:  This process is a cleanup of postal codes that were residing on the Contact Record in Dynamics CRM. The following will identify the records as proof of concept and transform the values in the Targeted Dynamics CRM System Using Scribe Insight.

 

 

 

1.      Identified Records for postal code Cleansing
 PostETL1R.jpg
2.      Run the SCRIBE DTS Contact_Zipcode_FIX.dts currently located in the C:\Users\Public\Documents\Scribe\Collaborations\ folder.

 

3.      Identifying Source Delta Query
       SELECT ca.CustomerAddressId

, c.fullname

, ca.PostalCode

, len(ca.PostalCode) as LENPC

, LEFT(ca.PostalCode, 5) AS New_Postal_Code

, ca.AddressNumber

FROM CustomerAddressBase ca (nolock) left join Contact c (nolock)

on c.Address1_AddressId = ca.CustomerAddressId

WHERE LEN(PostalCode) >= 15

AND ISNUMERIC (PostalCode) = 1

union

SELECT ca.CustomerAddressId

, c.fullname

, ca.PostalCode

, len(ca.PostalCode) as LENPC

, LEFT(ca.PostalCode, 5) AS New_Postal_Code

, ca.AddressNumber

FROM CustomerAddressBase ca (nolock) left join Contact c (nolock)

on c.Address1_AddressId = ca.CustomerAddressId

WHERE LEN(PostalCode) >= 15

AND ISNUMERIC (PostalCode) <> 1

AND PostalCode NOT IN (

‘United States of Ame’

, ‘CH-1228 Plan-les-Oua’

)

 PostETL2.jpg

 

4.      Declaring Target Delta
 PostETL3.jpg
 PostETL4.jpg

 

5.      Scribe Testing data conversation
 PostETL5.jpg

 

6.      SQL Record Source Delta Verification
SELECT ca.CustomerAddressId

, c.fullname

, ca.PostalCode

, len(ca.PostalCode) as LENPC

, LEFT(ca.PostalCode, 5) AS New_Postal_Code

, ca.AddressNumber

FROM CustomerAddressBase ca (nolock) left join Contact c (nolock)

on c.Address1_AddressId = ca.CustomerAddressId

WHERE LEN(PostalCode) >= 15

AND ISNUMERIC (PostalCode) = 1

AND CustomerAddressId = ”

union

SELECT ca.CustomerAddressId

, c.fullname

, ca.PostalCode

, len(ca.PostalCode) as LENPC

, LEFT(ca.PostalCode, 5) AS New_Postal_Code

, ca.AddressNumber

FROM CustomerAddressBase ca (nolock) left join Contact c (nolock)

on c.Address1_AddressId = ca.CustomerAddressId

WHERE LEN(PostalCode) >= 15

AND ISNUMERIC (PostalCode) <> 1

AND CustomerAddressId = ”

AND PostalCode NOT IN (

‘United States of Ame’

, ‘CH-1228 Plan-les-Oua’

)

 

 

 

7.      SQL Record Verification
 PostETL6.jpg

 

8.      CRM Interface Record Verification (Isolation)
 PostETL7.jpg

 

9.      CRM Form Field Definition
 PostETL9_FieldPropr.jpg

 

 

10.  Scribe Test Verifies Change
 PostETL8.jpg

 

11.  Run DTS package for Scribe Successful Update
 PostETL9_ScribeUpdate.jpg

 

12.  Successful Update of Record from CRM Interface
 PostETL9.jpg

 

 

Create Team Foundation Server Build Automation for Dynamics CRM 365 Solution Import using xRM CI Framework for your DevOps Pipeline and Move to Production Process — July 14, 2017

Create Team Foundation Server Build Automation for Dynamics CRM 365 Solution Import using xRM CI Framework for your DevOps Pipeline and Move to Production Process

Overview:  Using Team Foundation Server Online with Dynamics CRM 365 Online the following Build Definition will import a managed solution to the target Dynamics CRM Organization

Prerequisites: First Install Dynamics CRM Build Tools to Team Foundation Server Instance @ https://marketplace.visualstudio.com/items?itemName=WaelHamze.xrm-ci-framework-build-tasks

1.       Create Build Definition
 TFSAutomation2_MSCRMPing_BuildDefination1

 

2.       Select Empty Definition
 TFSAutomation2_MSCRMPing_BuildDefination2

 

3.       Add Task to Build Event
 TFSAutomation2_MSCRMPing_BuildDefination3

 

4.       After Installing “xRM CI Framework” to your Team Foundation Server Instance; Select MSCRM Import Solution to Add as Task to Build Definition
 TFSAutomation2_MSCRMPing_BuildDefination4_Use.jpg

 

5.       Verify Connection String is Correct.
Connection String to use Via Office 365 Portal Authentication:

AuthType=Office365; Url=https://org.crm.dynamics.com; UserName=CRMService@CRMDEV.onmicrosoft.com; Password=password

 

 TFSAutomation2_MSCRMPing_BuildDefination5_Queued_ConnectionString.jpg

 

6.       Upload Solution to File Directory of TFS then Select the Path of Solution File to be imported into the targeted Dynamics CRM Organization.
 TFSAutomation2_SelectPath

 

 

7.       Queue Build Event using Hosted Agent
 TFSAutomation2_MSCRMPing_BuildDefination6_Queued_Use

 

 

8.       Build Definition in Progress
 TFSAutomation2_MSCRMPing_BuildDefination7_Queued_Use.jpg

 

 

9.       Successful Build using MSCRM Import Solution
 TFSAutomation2_MSCRMPing_BuildDefination8_Successful_Use.jpg

 

 

10.   Successful Import of Solution Using TFS Build Confirmed in Dynamics CRM Organization
 TFSAutomation2_MSCRMPing_BuildDefination9_SuccessfulCRM

 

Dynamics CRM BLOB Data Extract — June 27, 2017

Dynamics CRM BLOB Data Extract

Dynamics CRM BLOB Data Extract from attachments

Tutorial Extracting Blob out of Mimeattachment

BLOB EXTRACTION OF CRM ATTACHMENTS TO ORIGINAL DATA TYPE AND PROPER FILE EXTENSION Proof of Concept

URL for Tool: http://sqlblobextractor.codeplex.com/

Background if done Manually without Blob Extraction Tool :

The DocumentBody is a base64 encoded string, so you’ll need to convert back to binary and save to a file.

For an online converter see – www.motobit.com/…/base64-decoder-encoder.asp

For doing this in c# use File.WriteAllBytes(@”c:\yourfile”, Convert.FromBase64String(yourBase64String));

To do this in SQL is a bit tricky – see blogs.msdn.com/…/converting-from-base64-to-varbinary-and-vice-versa.aspx

 

  1. IDENTIFYING DATASOURCE WITH BLOB File Extractor.

Blob1

2) CONFIGURATION OF BLOB STORAGE EXTRACTOR

Blob2

3) TEST CONNECTION

Blob3.jpg

4) Select statement to obtain records from datasource

Blob4.jpg

Blob5

5) IDENTIFYING THE BLOB FILE IN SQL MANAGEMENT STUDIO

Blob7

6) INSERT BLOB DETAILS INTO BLOB FILE EXTRACTOR

Blob8

7) COMPLEATION OF EXTRACTION

Blob9.jpg

8) Execution of Blob extraction from sql database instance

Blob10.jpg

9) Below is the opened PDF file from the extraction process.

 

 

 

Blob11

Dynamics CRM On Premise 2013 + : Solution to Clean Referential Attribute Metadata for Continued Solution Development and Production Instance Sustainability — June 17, 2017

Dynamics CRM On Premise 2013 + : Solution to Clean Referential Attribute Metadata for Continued Solution Development and Production Instance Sustainability

Dynamics CRM On Premise 2013 + : Solution to Clean Referential Attribute Metadata for Continued Solution Development and Production Instance Sustainability

Overview: If any custom entity is deleted or created and or modified within the Dynamics CRM organization without fully committing the new entity value to the following Tables/Views:

 

  1. Base Table, IE = “OpportunityProduct”
  2. View [dbo].[FilteredEntity, IE = “FilteredOpportunityProduct”
  3. [MetadataSchema].[Attribute], IE = “WHERE Name in (‘new_unit’,’new_sfdcopportunityid’)”

 

These will either need to manually be removed using SQL and or the XRMTOOLBOX component “DLB Attribute Manager”. The following SQL Server Error will be experienced throughout the entire Dynamics CRM organization potentially stopping all development making this issue of High Importance and Production Critical issue if experienced as described within the following use case scenario

1.0.1 SQL Error Generated on loading of Opportunity Form
 SQL_AttributeMettaData1
1.0.2 Advance Find Price Per Unit
1.0.2 Advance Find Price Per Unit
SQL_AttributeMettaData1_2

 

XML :

 

<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”false”>

<entity name=”opportunityproduct”>

<attribute name=”productid” />

<attribute name=”productdescription” />

<attribute name=”priceperunit” />

<attribute name=”quantity” />

<attribute name=”extendedamount” />

<attribute name=”opportunityproductid” />

<order attribute=”productid” descending=”false” />

<filter type=”and”>

<condition attribute=”priceperunit” operator=”not-null” />

</filter>

</entity>

</fetch>

1.0.3 Advance Find Price Per Unit Error
1.0.3 Advance Find Price Per Unit Error generated due to having broken reference from OpportunityProductBase to OportunityProduct View, FilteredOpportunityProduct View and MetadataSchema.Attribute table.
SQL_AttributeMettaData2
1.1.0 Root Cause Analysis of SQL Generic Error
1.1.0 Root Cause Analysis of SQL Generic Error
Due to the constraints nested within the Opportunity Form the Opportunity Product View needed to be updated within the SQL database. The metadata of the OpportunityProduct table needed to be updated along with entity and attribute mappings against views.  The alternation of the view of OpportunityProduct, FilteredOpportunityProduct and MetadataSchema.Attribute table solved the overall SQL Generic error issue.

 

On Import of the Opportunity Products the values had gotten mapped to new entities which created a new reference within the metadata within Opportunity Product. After the import processed The bulk delate feature had failed due to the deletion of the associated entities to this import. After altering the SQL table the

 

1.1.1 The Following SQL Query Updates the Opportunity Product View which is the first factor with SQL Generic error to reference the proper subgrid nested within the Opportunity Form.
USE [DEV_MSCRM]

GO

 

/****** Object:  View [dbo].[OpportunityProduct]  ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

 

 

 

— base view for OpportunityProduct

ALTER view [dbo].[OpportunityProduct]

(

— logical attributes

[ModifiedOnBehalfByName],

[OwnerId],

[OwnerIdType],

[EntityImage_URL],

[CreatedByName],

[OwningBusinessUnit],

[TransactionCurrencyIdName],

[OpportunityStateCode],

[ModifiedOnBehalfByYomiName],

[EntityImage],

[OpportunityIdName],

[CreatedByYomiName],

[dev_CSLName],

[dev_CSLYomiName],

[OwningUser],

[ModifiedByYomiName],

[CreatedOnBehalfByYomiName],

[CreatedOnBehalfByName],

[EntityImage_Timestamp],

[ModifiedByName],

[ProductIdName],

[UoMIdName],

 

— physical attributes

[ProductId],

[OpportunityProductId],

[PricingErrorCode],

[IsProductOverridden],

[IsPriceOverridden],

[PricePerUnit],

[OpportunityId],

[BaseAmount],

[ExtendedAmount],

[UoMId],

[ManualDiscountAmount],

[Quantity],

[CreatedOn],

[VolumeDiscountAmount],

[CreatedBy],

[Tax],

[ModifiedBy],

[ProductDescription],

[ModifiedOn],

[Description],

[VersionNumber],

[OverriddenCreatedOn],

[UTCConversionTimeZoneCode],

[TimeZoneRuleVersionNumber],

[ImportSequenceNumber],

[ExchangeRate],

[TransactionCurrencyId],

[BaseAmount_Base],

[ManualDiscountAmount_Base],

[VolumeDiscountAmount_Base],

[PricePerUnit_Base],

[Tax_Base],

[ExtendedAmount_Base],

[LineItemNumber],

[CreatedOnBehalfBy],

[ModifiedOnBehalfBy],

[SequenceNumber],

[EntityImageId],

[dev_Annuity],

[dev_AnnuityNew],

[dev_CSL],

[dev_AnnuityTotal],

[dev_annuitytotal_Base],

[dev_AnnuityValue],

[dev_annuityvalue_Base],

[dev_Delete]

) with view_metadata as

select

— logical attributes

[lk_opportunityproductbase_modifiedonbehalfby].[FullName],

[product_opportunities].[OwnerId],

[product_opportunities].[OwnerIdType],

[lk_opportunityproduct_entityimage].[ImageURL],

[lk_opportunityproductbase_createdby].[FullName],

[product_opportunities].[OwningBusinessUnit],

[transactioncurrency_opportunityproduct].[CurrencyName],

[product_opportunities].[StateCode],

[lk_opportunityproductbase_modifiedonbehalfby].[YomiFullName],

[lk_opportunityproduct_entityimage].[ImageData],

[product_opportunities].[Name],

[lk_opportunityproductbase_createdby].[YomiFullName],

[dev_systemuser_opportunityproduct_CSL].[FullName],

[dev_systemuser_opportunityproduct_CSL].[YomiFullName],

case when [product_opportunities].OwnerIdType = 8

then [product_opportunities].OwnerId

else null

end,

[lk_opportunityproductbase_modifiedby].[YomiFullName],

[lk_opportunityproductbase_createdonbehalfby].[YomiFullName],

[lk_opportunityproductbase_createdonbehalfby].[FullName],

[lk_opportunityproduct_entityimage].[ImageTimestamp],

[lk_opportunityproductbase_modifiedby].[FullName],

[opportunity_products].[Name],

[unit_of_measurement_opportunity_products].[Name],

 

— physical attribute

[OpportunityProductBase].[ProductId],

[OpportunityProductBase].[OpportunityProductId],

[OpportunityProductBase].[PricingErrorCode],

[OpportunityProductBase].[IsProductOverridden],

[OpportunityProductBase].[IsPriceOverridden],

[OpportunityProductBase].[PricePerUnit],

[OpportunityProductBase].[OpportunityId],

[OpportunityProductBase].[BaseAmount],

[OpportunityProductBase].[ExtendedAmount],

[OpportunityProductBase].[UoMId],

[OpportunityProductBase].[ManualDiscountAmount],

[OpportunityProductBase].[Quantity],

[OpportunityProductBase].[CreatedOn],

[OpportunityProductBase].[VolumeDiscountAmount],

[OpportunityProductBase].[CreatedBy],

[OpportunityProductBase].[Tax],

[OpportunityProductBase].[ModifiedBy],

[OpportunityProductBase].[ProductDescription],

[OpportunityProductBase].[ModifiedOn],

[OpportunityProductBase].[Description],

[OpportunityProductBase].[VersionNumber],

[OpportunityProductBase].[OverriddenCreatedOn],

[OpportunityProductBase].[UTCConversionTimeZoneCode],

[OpportunityProductBase].[TimeZoneRuleVersionNumber],

[OpportunityProductBase].[ImportSequenceNumber],

[OpportunityProductBase].[ExchangeRate],

[OpportunityProductBase].[TransactionCurrencyId],

[OpportunityProductBase].[BaseAmount_Base],

[OpportunityProductBase].[ManualDiscountAmount_Base],

[OpportunityProductBase].[VolumeDiscountAmount_Base],

[OpportunityProductBase].[PricePerUnit_Base],

[OpportunityProductBase].[Tax_Base],

[OpportunityProductBase].[ExtendedAmount_Base],

[OpportunityProductBase].[LineItemNumber],

[OpportunityProductBase].[CreatedOnBehalfBy],

[OpportunityProductBase].[ModifiedOnBehalfBy],

[OpportunityProductBase].[SequenceNumber],

[OpportunityProductBase].[EntityImageId],

[OpportunityProductBase].[dev_Annuity],

[OpportunityProductBase].[dev_AnnuityNew],

[OpportunityProductBase].[dev_CSL],

[OpportunityProductBase].[dev_AnnuityTotal],

[OpportunityProductBase].[dev_annuitytotal_Base],

[OpportunityProductBase].[dev_AnnuityValue],

[OpportunityProductBase].[dev_annuityvalue_Base],

[OpportunityProductBase].[dev_Delete]

from [OpportunityProductBase]

left join [ImageDescriptor] [lk_opportunityproduct_entityimage] on ([OpportunityProductBase].[EntityImageId] = [lk_opportunityproduct_entityimage].[ImageDescriptorId])

left join [SystemUserBase] [lk_opportunityproductbase_createdby] with(nolock) on ([OpportunityProductBase].[CreatedBy] = [lk_opportunityproductbase_createdby].[SystemUserId])

left join [SystemUserBase] [lk_opportunityproductbase_createdonbehalfby] with(nolock) on ([OpportunityProductBase].[CreatedOnBehalfBy] = [lk_opportunityproductbase_createdonbehalfby].[SystemUserId])

left join [SystemUserBase] [lk_opportunityproductbase_modifiedby] with(nolock) on ([OpportunityProductBase].[ModifiedBy] = [lk_opportunityproductbase_modifiedby].[SystemUserId])

left join [SystemUserBase] [lk_opportunityproductbase_modifiedonbehalfby] with(nolock) on ([OpportunityProductBase].[ModifiedOnBehalfBy] = [lk_opportunityproductbase_modifiedonbehalfby].[SystemUserId])

left join [SystemUserBase] [dev_systemuser_opportunityproduct_CSL] with(nolock) on ([OpportunityProductBase].[dev_CSL] = [dev_systemuser_opportunityproduct_CSL].[SystemUserId])

left join [ProductBase] [opportunity_products] on ([OpportunityProductBase].[ProductId] = [opportunity_products].[ProductId])

left join [OpportunityBase] [product_opportunities] on ([OpportunityProductBase].[OpportunityId] = [product_opportunities].[OpportunityId])

left join [TransactionCurrencyBase] [transactioncurrency_opportunityproduct] on ([OpportunityProductBase].[TransactionCurrencyId] = [transactioncurrency_opportunityproduct].[TransactionCurrencyId])

left join [UoMBase] [unit_of_measurement_opportunity_products] on ([OpportunityProductBase].[UoMId] = [unit_of_measurement_opportunity_products].[UoMId])

 

GO

 

 

 

 

1.1.2 The Following SQL Query adjusted for View [dbo].[FilteredOpportunityProduct the SQL Generic error to reference the proper subgrid nested within the Opportunity Form.
1.1.2 The Following SQL Query adjusted for View [dbo].[FilteredOpportunityProduct the SQL Generic error to reference the proper subgrid nested within the Opportunity Form.
USE [DEV_MSCRM]

 

GO

 

/****** Object: View [dbo].[FilteredOpportunityProduct] Script Date: 7/18/2016 1:24:37 AM ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

 

 

— report view for opportunityproduct

ALTER view [dbo].[FilteredOpportunityProduct] (

[baseamount],

[baseamount_base],

[createdby],

[createdbydsc],

[createdbyname],

[createdbyyominame],

[createdon],

[createdonutc],

[createdonbehalfby],

[createdonbehalfbydsc],

[createdonbehalfbyname],

[createdonbehalfbyyominame],

[description],

[entityimage],

[entityimageid],

[entityimage_timestamp],

[entityimage_url],

[exchangerate],

[extendedamount],

[extendedamount_base],

[importsequencenumber],

[ispriceoverridden],

[ispriceoverriddenname],

[isproductoverridden],

[isproductoverriddenname],

[lineitemnumber],

[manualdiscountamount],

[manualdiscountamount_base],

[modifiedby],

[modifiedbydsc],

[modifiedbyname],

[modifiedbyyominame],

[modifiedon],

[modifiedonutc],

[modifiedonbehalfby],

[modifiedonbehalfbydsc],

[modifiedonbehalfbyname],

[modifiedonbehalfbyyominame],

[opportunityid],

[opportunityiddsc],

[opportunityidname],

[opportunityproductid],

[opportunitystatecode],

[opportunitystatecodename],

[overriddencreatedon],

[overriddencreatedonutc],

[ownerid],

[owneridtype],

[owningbusinessunit],

[owninguser],

[priceperunit],

[priceperunit_base],

[pricingerrorcode],

[pricingerrorcodename],

[productdescription],

[productid],

[productiddsc],

[productidname],

[quantity],

[sequencenumber],

[tax],

[tax_base],

[timezoneruleversionnumber],

[transactioncurrencyid],

[transactioncurrencyiddsc],

[transactioncurrencyidname],

[uomid],

[uomiddsc],

[uomidname],

[utcconversiontimezonecode],

[versionnumber],

[volumediscountamount],

[volumediscountamount_base],

crm_moneyformatstring,

crm_priceformatstring

) with view_metadata as

select

[OpportunityProduct].[BaseAmount],

[OpportunityProduct].[BaseAmount_Base],

[OpportunityProduct].[CreatedBy],

–[OpportunityProduct].[CreatedByDsc]

0,

[OpportunityProduct].[CreatedByName],

[OpportunityProduct].[CreatedByYomiName],

dbo.fn_UTCToTzSpecificLocalTime([OpportunityProduct].[CreatedOn],

us.TimeZoneBias,

us.TimeZoneDaylightBias,

us.TimeZoneDaylightYear,

us.TimeZoneDaylightMonth,

us.TimeZoneDaylightDay,

us.TimeZoneDaylightHour,

us.TimeZoneDaylightMinute,

us.TimeZoneDaylightSecond,

0,

us.TimeZoneDaylightDayOfWeek,

us.TimeZoneStandardBias,

us.TimeZoneStandardYear,

us.TimeZoneStandardMonth,

us.TimeZoneStandardDay,

us.TimeZoneStandardHour,

us.TimeZoneStandardMinute,

us.TimeZoneStandardSecond,

0,

us.TimeZoneStandardDayOfWeek),

[OpportunityProduct].[CreatedOn],

[OpportunityProduct].[CreatedOnBehalfBy],

–[OpportunityProduct].[CreatedOnBehalfByDsc]

0,

[OpportunityProduct].[CreatedOnBehalfByName],

[OpportunityProduct].[CreatedOnBehalfByYomiName],

[OpportunityProduct].[Description],

[OpportunityProduct].[EntityImage],

[OpportunityProduct].[EntityImageId],

[OpportunityProduct].[EntityImage_Timestamp],

[OpportunityProduct].[EntityImage_URL],

[OpportunityProduct].[ExchangeRate],

[OpportunityProduct].[ExtendedAmount],

[OpportunityProduct].[ExtendedAmount_Base],

[OpportunityProduct].[ImportSequenceNumber],

[OpportunityProduct].[IsPriceOverridden],

IsPriceOverriddenPLTable.Value,

[OpportunityProduct].[IsProductOverridden],

IsProductOverriddenPLTable.Value,

[OpportunityProduct].[LineItemNumber],

[OpportunityProduct].[ManualDiscountAmount],

[OpportunityProduct].[ManualDiscountAmount_Base],

[OpportunityProduct].[ModifiedBy],

–[OpportunityProduct].[ModifiedByDsc]

0,

[OpportunityProduct].[ModifiedByName],

[OpportunityProduct].[ModifiedByYomiName],

dbo.fn_UTCToTzSpecificLocalTime([OpportunityProduct].[ModifiedOn],

us.TimeZoneBias,

us.TimeZoneDaylightBias,

us.TimeZoneDaylightYear,

us.TimeZoneDaylightMonth,

us.TimeZoneDaylightDay,

us.TimeZoneDaylightHour,

us.TimeZoneDaylightMinute,

us.TimeZoneDaylightSecond,

0,

us.TimeZoneDaylightDayOfWeek,

us.TimeZoneStandardBias,

us.TimeZoneStandardYear,

us.TimeZoneStandardMonth,

us.TimeZoneStandardDay,

us.TimeZoneStandardHour,

us.TimeZoneStandardMinute,

us.TimeZoneStandardSecond,

0,

us.TimeZoneStandardDayOfWeek),

[OpportunityProduct].[ModifiedOn],

[OpportunityProduct].[ModifiedOnBehalfBy],

–[OpportunityProduct].[ModifiedOnBehalfByDsc]

0,

[OpportunityProduct].[ModifiedOnBehalfByName],

[OpportunityProduct].[ModifiedOnBehalfByYomiName],

[OpportunityProduct].[OpportunityId],

–[OpportunityProduct].[OpportunityIdDsc]

0,

[OpportunityProduct].[OpportunityIdName],

[OpportunityProduct].[OpportunityProductId],

[OpportunityProduct].[OpportunityStateCode],

OpportunityStateCodePLTable.Value,

dbo.fn_UTCToTzSpecificLocalTime([OpportunityProduct].[OverriddenCreatedOn],

us.TimeZoneBias,

us.TimeZoneDaylightBias,

us.TimeZoneDaylightYear,

us.TimeZoneDaylightMonth,

us.TimeZoneDaylightDay,

us.TimeZoneDaylightHour,

us.TimeZoneDaylightMinute,

us.TimeZoneDaylightSecond,

0,

us.TimeZoneDaylightDayOfWeek,

us.TimeZoneStandardBias,

us.TimeZoneStandardYear,

us.TimeZoneStandardMonth,

us.TimeZoneStandardDay,

us.TimeZoneStandardHour,

us.TimeZoneStandardMinute,

us.TimeZoneStandardSecond,

0,

us.TimeZoneStandardDayOfWeek),

[OpportunityProduct].[OverriddenCreatedOn],

[OpportunityProduct].[OwnerId],

[OpportunityProduct].[OwnerIdType],

[OpportunityProduct].[OwningBusinessUnit],

[OpportunityProduct].[OwningUser],

[OpportunityProduct].[PricePerUnit],

[OpportunityProduct].[PricePerUnit_Base],

[OpportunityProduct].[PricingErrorCode],

PricingErrorCodePLTable.Value,

[OpportunityProduct].[ProductDescription],

[OpportunityProduct].[ProductId],

–[OpportunityProduct].[ProductIdDsc]

0,

[OpportunityProduct].[ProductIdName],

[OpportunityProduct].[Quantity],

[OpportunityProduct].[SequenceNumber],

[OpportunityProduct].[Tax],

[OpportunityProduct].[Tax_Base],

[OpportunityProduct].[TimeZoneRuleVersionNumber],

[OpportunityProduct].[TransactionCurrencyId],

–[OpportunityProduct].[TransactionCurrencyIdDsc]

0,

[OpportunityProduct].[TransactionCurrencyIdName],

[OpportunityProduct].[UoMId],

–[OpportunityProduct].[UoMIdDsc]

0,

[OpportunityProduct].[UoMIdName],

[OpportunityProduct].[UTCConversionTimeZoneCode],

[OpportunityProduct].[VersionNumber],

[OpportunityProduct].[VolumeDiscountAmount],

[OpportunityProduct].[VolumeDiscountAmount_Base],

dbo.fn_GetNumberFormatString(t.CurrencyPrecision, us.NumberGroupFormat, us.NegativeCurrencyFormatCode, 1, case o.CurrencyDisplayOption when 0 then t.CurrencySymbol when 1 then t.ISOCurrencyCode end, us.CurrencyFormatCode),

dbo.fn_GetNumberFormatString(o.PricingDecimalPrecision, us.NumberGroupFormat, us.NegativeCurrencyFormatCode, 1, case o.CurrencyDisplayOption when 0 then t.CurrencySymbol when 1 then t.ISOCurrencyCode end, us.CurrencyFormatCode)

from OpportunityProduct

join SystemUserBase u on (u.SystemUserId = dbo.fn_FindUserGuid()and u.IsDisabled = 0)

leftjoin UserSettingsBase us on us.SystemUserId = u.SystemUserId

leftjoin OrganizationBase o on u.OrganizationId = o.OrganizationId

leftjoin TransactionCurrencyBase t on t.TransactionCurrencyId = [OpportunityProduct].TransactionCurrencyId

leftouterjoin StringMap [IsPriceOverriddenPLTable] on

([IsPriceOverriddenPLTable].AttributeName =’ispriceoverridden’

and [IsPriceOverriddenPLTable].ObjectTypeCode = 1083

and [IsPriceOverriddenPLTable].AttributeValue = [OpportunityProduct].[IsPriceOverridden]

and [IsPriceOverriddenPLTable].LangId=

case us.UILanguageId

when 0 then o.LanguageCode

else us.UILanguageId

end)

leftouterjoin StringMap [IsProductOverriddenPLTable] on

([IsProductOverriddenPLTable].AttributeName =’isproductoverridden’

and [IsProductOverriddenPLTable].ObjectTypeCode = 1083

and [IsProductOverriddenPLTable].AttributeValue = [OpportunityProduct].[IsProductOverridden]

and [IsProductOverriddenPLTable].LangId=

case us.UILanguageId

when 0 then o.LanguageCode

else us.UILanguageId

end)

leftouterjoin StringMap [OpportunityStateCodePLTable] on

([OpportunityStateCodePLTable].AttributeName =’opportunitystatecode’

and [OpportunityStateCodePLTable].ObjectTypeCode = 1083

and [OpportunityStateCodePLTable].AttributeValue = [OpportunityProduct].[OpportunityStateCode]

and [OpportunityStateCodePLTable].LangId=

case us.UILanguageId

when 0 then o.LanguageCode

else us.UILanguageId

end)

leftouterjoin StringMap [PricingErrorCodePLTable] on

([PricingErrorCodePLTable].AttributeName =’pricingerrorcode’

and [PricingErrorCodePLTable].ObjectTypeCode = 1083

and [PricingErrorCodePLTable].AttributeValue = [OpportunityProduct].[PricingErrorCode]

and [PricingErrorCodePLTable].LangId=

case us.UILanguageId

when 0 then o.LanguageCode

else us.UILanguageId

end)

crossjoin dbo.fn_GetMaxPrivilegeDepthMask(3) pdm

where

(

— privilege check

pdm.PrivilegeDepthMask is not null and

(

— Owner check

[OpportunityProduct].OwnerId in

(– returns only principals with Basic Read privilege for entity

select pem.PrincipalId from PrincipalEntityMap pem(NOLOCK)

join SystemUserPrincipals sup(NOLOCK)on pem.PrincipalId = sup.PrincipalId

where sup.SystemUserId = u.SystemUserId

and pem.ObjectTypeCode = 3

)

 

— role based access

or

exists

(

select

1

where

(

— deep/local security

(((pdm.PrivilegeDepthMask & 0x4)!= 0)or((pdm.PrivilegeDepthMask & 0x2)!= 0))and

[OpportunityProduct].[OwningBusinessUnit] in (select BusinessUnitId from SystemUserBusinessUnitEntityMap (NOLOCK) where SystemUserId = u.SystemUserId and ObjectTypeCode = 3)

)

or

(

— global security

((pdm.PrivilegeDepthMask & 0x8)!= 0)and

[OpportunityProduct].[OwningBusinessUnit] isnotnull

)

)

 

— object shared to the user

or

[OpportunityProduct].[OpportunityId] in

(

select POA.ObjectId from PrincipalObjectAccess POA

join SystemUserPrincipals sup(NOLOCK)on POA.PrincipalId = sup.PrincipalId

where sup.SystemUserId = u.SystemUserId and

POA.ObjectTypeCode = 3 and

((POA.AccessRightsMask | POA.InheritedAccessRightsMask)& 1)=1

)

)

)

 

GO

 

 

 

 

1.1.3 Updating the the MetadataSchema.Attribute table to delete references to the metadata data to alow for continued development within unmanaged solution.
USE [DEV_MSCRM]

GO

 

DELETE FROM [MetadataSchema].[Attribute]

WHERE Name in(‘new_unit’,’new_sfdcopportunityid’)

GO

 

 

1.1.4 After Process is completed Advance Find will Function Successful
1.1.4 After Process is completed Advance Find will Function Successful
 SQL_AttributeMettaData3
2.0 Delete the Attribute using DLBAttributeManager for successful truncation of the entity and metadata associated to the attribute
Finding that to delete the orphaned attribute after not successfully committing the attribute to the metadata the Entity has to be recreated from the base table on OpportunityProduct for the DLBAttribute Manager within the XRMToolBox 2016.
2.1Recreate the Entity on the Base table
2.1Recreate the Entity on the Base table
 SQL_AttributeMettaData4
2.2 Using DLBAttributeManager

 

2.2 Using DLBAttributeManager
 SQL_AttributeMettaData5.jpg
2.3 Before the Attribute is Deleted with the DLB Attribute Manager (Unmanaged Solution)

 

2.3 Before the Attribute is Deleted with the DLB Attribute Manager (Unmanaged Solution)
 SQL_AttributeMettaData6.jpg
2.4 After the Attribute is Deleted with the DLB Attribute Manager (Unmanaged Solution)
2.4 After the Attribute is Deleted with the DLB Attribute Manager (Unmanaged Solution)
 SQL_AttributeMettaData7.jpg

 

Dynamics CRM On Premise Maintenance Jobs! — June 15, 2017

Dynamics CRM On Premise Maintenance Jobs!

Please remember to reschedule these maintenance jobs after the installation of CRM is completed.

Maintenance Job Name Purpose Default Frequency/ Recommendation
Deletion Service The deletion service maintenance operation now cleans up subscription tracking records for deleted metadata objects as they expire.

If Outlook does not sync for 90 days, the job will remove the subscription with CRM.

Cleanup POA records. Un-sharing of record(s) do not remove the records in the POA table, it will stay there for 90 days and it will clean up by the deletion job.

Cleanup failed/stuck workflows for the record(s) synchronize with Outlook.

By default, the job executes every 24 hours, reschedule the job to run at non-peak hours. E.g. 11:00PM at night
Indexing Management Validates that system-managed indexes exist for all entities and recreates any missing indexes.

This is more for CRM Online and only impacts us during configuration of the solution. It automatically creates index for each column used in search for Quick Find if the index is not already created in the CRM database

By default, the job executes every 24 hours, reschedule the job to run at non-peak hours. E.g. 11:00PM at night
Reindex All Reorganizes/rebuilds fragmented indexes depending on the amount of fragmentation.

It execute the p_ReindexAll stored procedure, it selects all indexes in the database with greater than 30% fragmentation and runs ReIndex on each while in online mode. Run while online mode means indexes are not taken offline while reindex is happening they still are active and are functioning and can be used.

Large enterprise CRM implementation with big data volume should disable this CRM reindex job if a custom job is created for recreating the indexes and with run update stats.

By default, the job executes every 24 hours, disable the job by setting the Job Next Run value to the future. E.g. 12/31/2999
Cleanup Inactive Workflow Assemblies Seeks custom workflow assemblies that are no longer referenced in workflow rules or in-process jobs. Those unreferenced assemblies are then deleted. Consider the scenario where you register version 2.0 of a custom workflow assembly.  You may update your rules to reference the new version, but some in-progress jobs may still be referencing version 1.0.  Once those jobs have completed, this maintenance job will clean up the version 1.0 assembly that is no longer referenced by rules/jobs.

It executes the p_CleanupInactiveWorkflowAssemblies stored procedure.

By default, the job executes every 24 hours, reschedule the job to run at non-peak hours. E.g. 11:00PM at night
Create Audit Partition Alters the partitioning scheme for the auditbase table (SQL Enterprise only). By default, the job executes every month, reschedule the job to run at non-peak hours. E.g. 11:00PM at night
Check for MUI Updates Detects upgrades to language (MUI) packs and schedules additional async operations to perform individual language provisioning. By default, the job executes every 24 hours, if you don’t have language pack installed, you can disable the job by setting the Job Next Run value to the future. E.g. 12/31/2999
Refresh Entity Row Count Refreshes the Record Count snapshot statistics leveraged enhanced query plans.

This job is important because it define how Quick Find works, it uses the counts to come up with the correct query plan.

By default, the job executes every 24 hours, reschedule the job to run at non-peak hours. E.g. 11:00PM at night
Refresh Sharing Count Refreshes the POA read snapshot statistics leveraged in enhanced query plans.

This job is important because it define how Quick Find works, it uses the counts to come up with the correct query plan.

By default, the job executes every 24 hours, reschedule the job to run at non-peak hours. E.g. 11:00PM at night

The Deletion Service executes the following SQL queries.

exec sp_executesql N’select SubscriptionId, SystemUserId

from Subscription (nolock)

where datediff(dd, LastSyncStartedOn, getutcdate()) >= @expireSubscriptionInDays’,N’@expireSubscriptionInDays int’,@expireSubscriptionInDays=90

exec sp_executesql N’delete from PrincipalObjectAccess where (AccessRightsMask = 0 or AccessRightsMask is null) and (InheritedAccessRightsMask = 0 or InheritedAccessRightsMask is null) and VersionNumber <= @versionNumberExpired’,N’@versionNumberExpired bigint’,@versionNumberExpired=404676
exec sp_executesql N’delete from SubscriptionTrackingDeletedObject

where TimeStamp <= convert(timestamp, @versionNumberExpired)

SELECT @@ROWCOUNT’,N’@versionNumberExpired bigint’,@versionNumberExpired=404676

exec sp_executesql N’

declare @now DateTime = getutcdate();

DELETE FROM [MetadataSyncTrackingDeletedObject]

WHERE datediff(dd, CreatedOn, @now) >= @expireSubscriptionInDays;

declare @rowCount int;

SELECT @rowCount = @@ROWCOUNT;

declare @columnExists bit = 0;

IF (EXISTS (SELECT * FROM sys.columns

WHERE object_id = OBJECT_ID(N”OrganizationBase”)

AND name = N”MetadataSyncLastTimeOfNeverExpiredDeletedObjects”))

BEGIN

set @columnExists = 1;

END

SELECT @rowCount as [RowCount], @now as [Now], @columnExists as [ColumnExists];

‘,N’@expireSubscriptionInDays int’,@expireSubscriptionInDays=90

exec sp_executesql N’

UPDATE

OrganizationBase

SET

MetadataSyncLastTimeOfNeverExpiredDeletedObjects = null

WHERE

MetadataSyncLastTimeOfNeverExpiredDeletedObjects is not null

AND MetadataSyncLastTimeOfNeverExpiredDeletedObjects <= DATEADD(dd, -@expireSubscriptionInDays, @now)

‘,N’@now datetime,@expireSubscriptionInDays int’,@now=’2014-04-02 16:13:42.993,@expireSubscriptionInDays=90

exec sp_executesql N’

if exists (select * from WorkflowWaitSubscriptionBase (nolock) where IsModified = 1)

begin

update AsyncOperationBase

set

StateCode = @readyState,

StatusCode = @waitingStatus,

ModifiedOn = @modifiedOn,

ModifiedBy = CreatedBy

where

StateCode = @suspendedState

and RetryCount < @maxRetries

and AsyncOperationId in (select AsyncOperationId from WorkflowWaitSubscriptionBase where IsModified = 1)

end’,N’@readyState int,@suspendedState int,@waitingStatus int,@maxRetries int,@modifiedOn datetime’,@readyState=0,@suspendedState=1,@waitingStatus=0,@maxRetries=10,@modifiedOn=’2014-04-02 16:13:43

Reindex All executes the p_ReindexAll stored procedure below.

/***********************************************************************************************/

/* RETURN CODES: */

/* 0 – Success */

/* 1 – Partial success – some indexes could not be rebuilt */

/* 5 – Invalid input parameter(s) */

/***********************************************************************************************/

/*

Returns

a) Always first recordset – one row with 4 integer columns:

ResultCode – see RETURN CODES

TotalIndexesToRebuild – total count of indexes detected to be rebuild

RebuiltWithOnlineON – count of indexes rebuilt with option ONLINE = ON

RebuiltWithOnlineOFF – count of indexes rebuilt with option ONLINE = OFF (can’t be rebuilt with ONLINE = ON)

b) Always second recordset – see @errors table

c) Only when @Verbose=1, then the second recordset with detailed info about all indexes

*/

BEGIN

SET NOCOUNT ON

DECLARE @TotalIndexesToRebuild int = 0,

@RebuiltWithOnlineON int = 0,

@RebuiltWithOnlineOFF int = 0

–Get start time for max run time tracking

DECLARE @MaxTime datetime

SELECT @MaxTime = dateadd(ss,ISNULL(@MaxRunTime,0), GetUTCDate())

–Account for nulls in parameters, set to default values

SET @FragRebuildPct = ISNULL(@FragRebuildPct, 30)

SET @AllIndexTypes = ISNULL(@AllIndexTypes, 0)

SET @Verbose = ISNULL(@Verbose, 0)

SET @MinPages = ISNULL(@MinPages, 25)

SET @Online = ISNULL(@Online, 1)

–Validate parameters

IF ((@MaxRunTime <= 0) OR

(@AllIndexTypes not in (0,1)) OR

(@Verbose not in (0,1)) OR

(@Online not in (0,1)) OR

(@MinPages < 1) OR

(@FragRebuildPct > 100) OR (@FragRebuildPct < 0))

BEGIN

PRINT ‘Invalid Parameter value. Valid values are:’

PRINT ‘MaxRunTime > 0,’

PRINT ‘MinPages > 0

PRINT ‘FragRebuildPct in {NULL,0..100}’

PRINT ‘AllIndexTypes in {0,1}’

PRINT ‘Verbose in {0,1}’

PRINT ‘Online in {0,1}’

SELECT 5 as ResultCode, @TotalIndexesToRebuild as TotalIndexesToRebuild, @RebuiltWithOnlineON as RebuiltWithOnlineON, @RebuiltWithOnlineOFF as RebuiltWithOnlineOFF

RETURN 5

END

DECLARE @indexes table

(

SchemaName sysname,

TableName sysname,

IndexName sysname,

OldFrag int,

NewFrag int null,

processed bit

)

DECLARE @errors table

(

Number int,

Severity int,

State int,

–Message nvarchar(4000), — can be found by select * from sys.messages m where message_id = Number and m.language_id = 1033

OnlineOn bit,

Statement NVarchar(2048)

)

INSERT INTO @indexes

SELECT schema_name(o.schema_id), object_name(s.object_id), i.name, s.avg_fragmentation_in_percent, null, 0

FROM sys.dm_db_index_physical_stats (db_id(),NULL,NULL,NULL,NULL) s

JOIN sys.objects o on (s.object_id = o.object_id)

JOIN sys.indexes i on (s.object_id = i.object_id and s.index_id = i.index_id)

WHERE

s.avg_fragmentation_in_percent > @FragRebuildPct — defrag only if more than x% fragmented

and i.type in (1, @AllIndexTypes + 1) — (1,2) — cannot defrag non-indexes(0-heap, 1- clustered, 2-nonclustered, 3-xml)

and s.page_count >= @MinPages — select only if the index spans multiple pages

ORDER BY s.avg_fragmentation_in_percent desc

select @TotalIndexesToRebuild = @@rowcount

DECLARE @SchemaName sysname,

@TableName sysname,

@IndexName sysname,

@sqlTemplate NVarchar(2048),

@sql NVarchar(2048)

DECLARE @retry bit

,@onlineON bit

DECLARE IndexCursor CURSOR LOCAL FOR

SELECT SchemaName, TableName, IndexName from @indexes order by OldFrag desc

OPEN IndexCursor

FETCH NEXT FROM IndexCursor INTO @SchemaName, @TableName, @IndexName

WHILE ((@@FETCH_STATUS = 0) AND ((GetUTCDate() < @MaxTime) OR (@MaxRunTime IS NULL)) )

BEGIN

select @sqlTemplate = ‘ALTER INDEX [‘+ @IndexName +’] ‘+

‘ON [‘+@SchemaName+’].[‘+@TableName+’] REBUILD WITH ‘+

‘( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = ‘

IF (@Online=1)

select @sql = @sqlTemplate + ‘ON )’

ELSE

select @sql = @sqlTemplate + ‘OFF )’

select @retry = 1, @onlineON = @Online

while (@retry = 1)

BEGIN

BEGIN TRY

IF (@Verbose=1) PRINT @sql

EXEC (@sql)

select @retry = 0

IF (@onlineON=1)

SELECT @RebuiltWithOnlineON = @RebuiltWithOnlineON +1

ELSE

SELECT @RebuiltWithOnlineOFF = @RebuiltWithOnlineOFF +1

END TRY

BEGIN CATCH

insert into @errors

select ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), @onlineON, @sql

IF (@onlineON=1 and ERROR_NUMBER() = 2725)

BEGIN

– Handle the possible exception below: rebuild index offline. Only SQL2012 has THROW

–ErrorNumber ErrorMessage

–2725 An online operation cannot be performed for index ‘?’ because the index contains column ‘?’ of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type. For a non-clustered index, the column could be an include column of the index. For a clustered index, the column could be any column of the table. If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.

select @sql = @sqlTemplate + ‘OFF )’

select @onlineON = 0

END

ELSE

select @retry = 0

END CATCH

END

UPDATE @indexes SET processed=1 WHERE SchemaName=@SchemaName and TableName=@TableName and IndexName=@IndexName

FETCH NEXT FROM IndexCursor INTO @SchemaName, @TableName, @IndexName

END

CLOSE IndexCursor

DEALLOCATE IndexCursor

IF (@Verbose=1)

BEGIN

UPDATE @indexes

SET NewFrag = avg_fragmentation_in_percent

FROM

sys.dm_db_index_physical_stats (db_id(),NULL,NULL,NULL,NULL) s

JOIN sys.objects o on (s.object_id = o.object_id)

JOIN sys.indexes i on (s.object_id = i.object_id and s.index_id = i.index_id)

WHERE SchemaName=schema_name(o.schema_id)

and TableName = object_name(s.object_id)

and IndexName = i.name

END

DECLARE @ResultCode int

IF Exists(select * from @indexes where processed = 0)

BEGIN

PRINT ‘Did not process all indexes due to @MaxRunTime constraint’

SELECT @ResultCode = 1

END

ELSE

BEGIN

SELECT @ResultCode = 0

END

– Return results

SELECT @ResultCode as ResultCode, @TotalIndexesToRebuild as TotalIndexesToRebuild, @RebuiltWithOnlineON as RebuiltWithOnlineON, @RebuiltWithOnlineOFF as RebuiltWithOnlineOFF

SELECT * from @errors

IF (@Verbose=1) SELECT * FROM @indexes order by OldFrag desc

RETURN @ResultCode

Clean Up Inactive Workflow Assemblies executes the p_CleanupInactiveWorkflowAssemblies stored procedures below:

DECLARE @ActivationsToDelete TABLE

(

WorkflowId UNIQUEIDENTIFIER

)

INSERT @ActivationsToDelete

SELECT WorkflowId

FROM WorkflowBase

WHERE

Type = 2 — it’s a workflow activation

AND StateCode = 0 — not active

AND WorkflowId NOT IN

(SELECT OwningExtensionId FROM AsyncOperationBase

WHERE OperationType = 10

AND OwningExtensionId IS NOT NULL)

DELETE FROM WorkflowDependencyBase WHERE WorkflowId IN

(SELECT WorkflowId from @ActivationsToDelete)

DELETE FROM DependencyNodeBase WHERE

ComponentType = 29 AND

ObjectId IN

(SELECT WorkflowId from @ActivationsToDelete)

DELETE FROM WorkflowBase WHERE WorkflowId IN

(SELECT WorkflowId from @ActivationsToDelete)

 

 

Upgrading from Dynamics 2016 (8.1) to Dynamics 365 (8.2) Update 2.0 — June 9, 2017

Upgrading from Dynamics 2016 (8.1) to Dynamics 365 (8.2) Update 2.0

Upgrading from Dynamics 2016 (8.1) to Dynamics 365 (8.2) Update 2.0

  1. Dynamics CRM Install Wizard for Update 2.0 for Dynamics CRM 365 Server

 

1

 

2. License Agreement & Privacy statement

2

3. Ready to Install Update 2.0 for Dynamics CRM 365 Server

3.jpg

4. Installing Update 2.0 for Dynamics CRM 365 Server

4.jpg

5. Installing Update 2.0 Progress for Dynamics CRM 365 Server

5

6. Update Completed for Dynamics CRM 365 Server

6

Upgrading from Dynamics 2015 to Dynamics 2016 Both Server and Organization with Error Resolution —

Upgrading from Dynamics 2015 to Dynamics 2016 Both Server and Organization with Error Resolution

 

1.0 Backup MSCRM Database

2

1.1 Backup MSCRM_Config Database

1.jpg

 

2.1 Upgrade from Dynamics CRM Server 2015 to 2016

 

3.jpg

 

 

2.2 Verify Domain Service Accounts

4.jpg

 

2.3 System Check Verification

5.jpg

 

2.4 Upgrading Dynamics CRM Server to 2016

6.jpg

 

 

2.5 Errors occurring during installation of Dynamics CRM 2016 Server:

This error is fairly normal when the IIS Service and or another component within the Windows services has to be restarted and is not either within the time limit window and or the service needs to be restarted

7.jpg

2.5.1 Verify Windows Activation Service is running and hit retry

8.jpg

 

2.6 Successful installation of Dynamics CRM 2016 Server!

9.jpg

 

3.1 Upgrading Dynamics CRM 2015 Organization to Dynamics CRM 2016 Organization:

Open Deployment Manager and now Update the Organization from 2015 (7.0) to 2016 (8.0)

10.jpg

3.2 If System check Fails because SSRS instance is not installed install and or the SSRS instance is blank when trying to upgrade organization from Dynamics CRM 2015. (CRM 2015/2016 Cannot find SSRS Instance when install Reporting Extension)

11

3.2.1 When trying to install the SRSDataconnector the SSRS Instance Label is blank. The work around is to manually install from the install-config.xml

3.2.2 Find the File location of the SRS Data Connector within New Dynamics CRM Server 2016 File Folder:

3.2.3 Open the install-config.xml within the SRS Data Connector Folder and add the following to the config file if you have an SSRS reporting Server setup but do not see the Report Server instance when trying to Upgrade.

12

3.2.4 Folder Path Identification for running CMD Prompt for Manuel Installation of SRS Data Connector.

Once saved exit out of the install-config.xml and run the following Script in CMD prompt. Please modify code to specific the specific file folder path to initialize the install-config.xml

C:\\SrsDataConnector>SetupSrsDataConnector /Q /C ONFIG install-config.xml /L  C:\\SrsDataConnecto r\log.txt

3.2.5 The Log file now should exist within the SRSDataConnector directory for further analysis of issues

 

Empty path name is not legal error

13.jpg

This means that when installing the SRSDataConnector manually this value was left null in the registry when it should not have been left blank

14.jpg

 

Change this registry value to default Report Instance Name:

For Example “MSSQLSERVER”

 

15

3.2.6 Once Report Server is installed against the registry after the Manuel installation of SSRS Registry Values will now be existent

 

3.3 After SSRS Errors Resolved, the Upgrade of the Organization now Successfully Installs!

16.jpg

17

 

18

3.4 Successful Upgrade of Organization from Dynamics 2015 to Dynamics 2016

 

19

 

URL:

http://sanderstechnology.com/2016/crm-reporting-extensions-ssrs-not-listed-4/14569/#.WTp4w-nruM8

https://blogs.msdn.microsoft.com/johnsullivan/2010/02/01/the-path-is-not-of-a-legal-form/

https://usingdynamicscrm.wordpress.com/2009/01/17/the-path-is-not-of-a-legal-form-while-installing-the-dynamics-crm-outlook-client/

Microsoft Dynamics CRM reporting requirements – https://technet.microsoft.com/en-us/library/hh699754.aspx

How to determine the version, edition and update level of SQL Server and its components – https://support.microsoft.com/en-au/kb/321185

Install Microsoft Dynamics CRM Reporting Extensions using a command prompt – https://technet.microsoft.com/en-us/library/hh699725.aspx

Dynamics 365 Editable Grids: Change Record Status — May 26, 2017

Dynamics 365 Editable Grids: Change Record Status

Editable Grids: Change Record Status

Overview: User will be able to change Optionset custom field to set the status of the Open opportunity to Won and or Lost from Editable List View using Editable Grid View. There is a Workflow that is checking for the custom fields’ status which then converts the locked read only record of the status of the open opportunities record to either won and or lost based on its Optionset value and friendly name.

Configuration Components:

1 Custom System View

1 Workflow

1 Custom Field

Enabling Editable Grids on Entity(Opportunity)

·         Editable Grid Proof of Concept
 EGT1

 

·         Changing Custom Field Optionset
 EGT2

 

·         Changing Optionset Status in Custom Field
 EGT3

 

·         Editing Multiple Opportunities from the List View
 EGT4.jpg

 

·         Work Flow Used:
 EGT5_WF
 EGT6_WF