Recently I encountered an error message when trying to test the send email functionality after setting up the email parameters and using SMTP to send out test email “D365 F&O Server smtp.office365.com:587 using SSL/TLS supports the following authentication options: LOGIN, XOAUTH2.“
After some researching, I found the below setup was missing in Microsoft 365 Admin Center for the user. To fix this :
Navigate to Office 365 Admin Center >>Open list of users >> Select the user and click on the name
Click on Mail >> Go to Manage Email Apps
Make sure the check box is Turned ON. By default it is turned OFF.
After this come back to Dynamics 365 F&O and send the test email
I recently faced the below error when trying to import a BAI2 format bank statement file in Microsoft Dynamics 365 Finance (Service update 10.0.28) version.
Error upon importing bank statement
The bank statement file had correct bank account. The issue was due to another bank setup in a different legal entity with the same account number.
After correcting the bank account the import process worked fine.
If you are facing the same error when worth checking for the duplicate bank account number in another legal entity.
In Dynamics 365 F&O service update 10.0.26, a new personalization feature is released which allows users to choose and select desired tile size for the workspaces. The feature can be enabled from Feature management workspace
ERP’s have evolved over time and the people who are in this domain have witnessed how the usage of ERP system has changed – from being just an in-house system to manage operational activities to being modern and intelligent systems which can talk to external systems, provide access to external parties via portals with rich analytics baked into the product to provide actionable insights to improve operational efficiencies and decision making.
Last few years have taken a big leap in adding Data Analytics, Machine learning and Artificial intelligence capabilities in order to make the systems more smart .
In this post I’ll highlight AI/ML based capabilities available out of the box in Microsoft Dynamics 365 Finance , Supply chain and commerce applications. There are links to explore more about these features:
Microsoft Dynamics 365 for Finance:
Finance insights combines functionality from Microsoft Dynamics 365 Finance with Dataverse, Azure, and AI Builder to provide powerful forecasting tools . Announcement of these capabilities were made in Feb 2020 and these features are now generally available.
Budget Proposal : By using Finance Insights, intelligent budget proposals can quickly consolidate and analyze years of historical data to predict your budget forecast. By letting the system perform these budgeting tasks for you, you’ll save time and be able to focus on meaningful discussions about how the budget will support your business. https://docs.microsoft.com/en-us/dynamics365/finance/finance-insights/budget-proposals
Cash Position : Cash position is the projection of cash flow that is forecast for the near term. It’s based on the projection of cash receipts from customers that pay outstanding invoices and orders, and also on the projection cash disbursements that are paid to vendors for purchase invoices and orders. More details can be found at https://docs.microsoft.com/en-us/dynamics365/finance/finance-insights/cash-position
Customer payment predictions : The Manage customer credit and collections workspace includes two new tiles: Transaction payment predictions and Customer payment predictions. On the Transaction payment predictions list page, you can view the probability of payment for open transactions in the On time, Late, and Very late buckets
The Customer payment predictions list page shows the total open balance, and the amount that is predicted to be paid in the On time, Late, and Very late buckets.
Expense receipt processing : Expense entry has been enhanced through the introduction of optical character recognition (OCR) processing for receipts. We need to install expense management service add in from LCS and then enable the below features
Expense reports re-imagined
Auto-match and create expense from receipt
This feature uses Azure cognitive services Form Recognizer to extract the field information from the the uploaded expense receipts
Demand Forecasting : Demand forecasting is used to predict independent demand from sales orders and dependent demand at any decoupling point for customer orders. To generate the baseline forecast, a summary of historical transactions is passed to Microsoft Azure Machine Learning hosted on Azure. Basic flow is as below. Wecan use Supply Chain Management to visualize the forecast, adjust the forecast, and view key performance indicators (KPIs) about forecast accuracy.
A great demo on this feature was given by Andrew Lencsak in DynamicsCon 2020. Below is the link
Ratings and Review recommendations : The ratings and reviews solution lets retailers capture product reviews and ratings from customers. Retailers can then show average ratings and review information across their e-Commerce website. Details can be found at https://docs.microsoft.com/en-us/dynamics365/commerce/ratings-reviews-overview
Below is high level architecture of the service
Commerce analytics (Preview) : This feature is in preview and utilises all modern capabilities of data lake, azure synapse analytics and power BI semantic models to provide the rich analytics from commerce data ganarated from multiple sources. A live demo if this dashboard can be access from https://aka.ms/CommerceAnalyticsDemo
Microsoft clarity in Dynamics 365 commerce : Microsoft Clarity is a user behavior analytics tool that can help site owners understand user interactions with their e-commerce sites. Clarity’s analysis tools enable visibility using session recordings, heatmaps, and machine learning insights to review and study user interactions. Clarity can be integrated into Dynamics 365 commerce sites. More details can be found at https://docs.microsoft.com/en-us/dynamics365/commerce/set-up-clarity
Fraud Protection :Fraud Protection is a service that offers fraud protection solutions to help retailers prevent fraudulent activity and identify places where fraud might be unnoticed. This has to be purchased in addition to Commerce license altough the integration capabilities exists out of the box . Visit https://dynamics.microsoft.com/en-us/ai/fraud-protection/ to know more about the offering.
Product recommendations : The product recommendations service utilizes artificial intelligence and machine learning (AI-ML) technologies in the following way:
Data in the format that the Recommendation service requires is extracted from the Commerce operational database and sent to Azure Data Lake Storage or Entity store.
The recommendations service uses the stored data to train recommendation models for the People also like, Frequently bought together, New, Best selling, and Trending lists.
Personalised product recommendations : In Dynamics 365 Commerce, retailers can make personalized product recommendations (also known as personalization) available. In this way, personalized recommendations can be incorporated into the customer experience online and at the point of sale (POS). When the personalization functionality is turned on, the system can associate a user’s purchase and product information to generate individualized product recommendations.
Shop similar looks recommendations : The “shop similar looks” recommendations feature in Dynamics 365 Commerce uses the power of artificial intelligence and machine learning (AI-ML) to deliver recommendations for visually similar products to customers.
Shop similar description recommendations : The functionality for “shop similar description” recommendations uses the product name and description of seed products to find and recommend similar products in a retailer’s product catalog. “Shop similar description” recommendations are available in both the point of sale (POS) and e-commerce experiences.
Clienteling with customer insights : In Microsoft Dynamics 365 Commerce, retailers can use the client book functionality to help store associates form long-term relationships with key customers. By using the Dynamics 365 Customer Insights application, retailers can aggregate data from the various systems that customers use to interact with the retailer’s brand. They can then use this data to generate a single view of the customer and derive insights. The integration of Customer Insights with Commerce lets retailers select one or more measures that should be shown on the customer card in the client book. For example, retailers can use the data in Customer Insights to calculate the “churn probability” for a customer and define the “next best action.” If these values are defined as measures, they can be shown on the customer card and can provide crucial information to sales associates
The below AI powered capabilities are available in Dynamics 365 ecosystem which can be added to your existing Dynamics 365 applications to enhance the experience:
Dynamics 365 Connected space ( In preview) : With Dynamics 365 Connected Spaces, your organization can harness observational data with ease, use AI-powered models to unlock insights about your environment, and respond in real-time to trends and patterns.
Dynamics 365 customer insights : Bring together transactional, behavioral, and demographic data in real time to create a 360-degree view of your customers. . Optimize the customer journey with out-of-the-box and custom interactive reports.
Understand customer behavior on your website and mobile app for cross-channel journey optimization
Optimize the customer journey with out-of-the-box and custom interactive reports.
Predict customer needs by unlocking powerful insights using prebuilt AI models, or use custom models for deeper insights.
Improve decision making with a digital-supply-chain twin by performing what-if simulations, advanced analytics powered by AI, and enhancing multi-tier supplier visibility.
Seamlessly work with existing planning and execution systems. Invite suppliers and partners to securely receive and share data on one platform.
Improve collaboration with suppliers, partners, and team members to identify risks early and plan for supply and resource constraints.
It does not stops here, power platform provides rich capabilities to build our own AI and ML models and curate your own solutions for Dynamics 365 applications. For more details visit https://docs.microsoft.com/en-us/ai-builder/model-types
During Microsoft Ignite 2021 held from 3-5 Nov 2021, Microsoft announced about emerging technology trends and innovations happening across the Microsoft Cloud covering Azure, Business Applications, Industry cloud, Microsoft 365 & Windows, Microsoft Edge, Power platform, Security & Identity management.
In this post I’ll highlight 6 key announcements related to Dynamics 365 business applications space and provide references so that you can dive deeper into your area of interest.
Collaborative apps released across Microsoft Dynamics 365, Power Platform, Office and Teams
Microsoft Dynamics 365 Supply Chain Insights now in preview
Microsoft Dynamics 365 Customer Service voice channel now available
Microsoft Dynamics 365 Connected Spaces coming to preview soon
Microsoft Dynamics 365 adds key capabilities for Supply Chain Management
Microsoft Customer Experience Platform puts organizations in control of customer data
Collaborative apps released across Microsoft Dynamics 365, Power Platform, Office and Teams
To allow organizations to to operate as one business, everywhere. New capabilities and features in preview have been release released across Microsoft Dynamics 365, Power Platform, Office 365 and Teams include:
Accessible business data: A. Dynamics 365 and Teams are getting closer to each other to allow Organizations quickly find results from business data across Microsoft Search, unfurl that data into actionable cards (Loops) across Outlook and Teams and search valuable business data inline while writing. This new capability is called a Loop component, an element of Microsoft Loop, introduced today at Microsoft Ignite. Learn more about Microsoft Loop on the Microsoft 365 blog
Increased collaboration: Teams can invite anyone across the company to collaborate on Dynamics 365 sales records within the flow of a Teams chat or channel. Other collaborative updates include the ability to have a “chat now” pop out from anywhere in Dynamics 365 and inbound/outbound digital voice with Teams, featuring conversational intelligence.
Real-time engagement: Organizations can see who is working with them in the flow of work with live co-presence throughout Dynamics 365 apps. In addition, users can have all the information available on people they are collaborating with and how to engage with them via calls, chats or meetings.
Microsoft Dynamics 365 Supply Chain Insights now in preview
Supply Chain Insights enables organizations to predict and mitigate disruptions and ensure positive customer experiences through fulfilling demand. With this solution, organizations can:
Collaborate and unify data in near real-time from third-party data providers, logistics partners, customers and multi-tier suppliers.
Enhance visibility by creating a digital twin of the supply chain and then generate actionable insights powered by AI.
Further predict and mitigate risk by enriching supply chain signals with external constraints like environmental disasters or geopolitical events that could affect the supply chain.
Microsoft Dynamics 365 Customer Service voice channel now available
Organizations can transform their contact center operations with the new Microsoft Dynamics 365 Customer Service voice channel, now generally available. This addition to the existing omnichannel capabilities leverages the power of Microsoft Teams to bring traditional Contact Center as a Service (CCaaS), Unified Communications as a Service (UCaaS) and Customer Engagement Center (CEC) under a single, integrated customer service Software as a Service (SaaS) solution with Dynamics 365 Customer Service.
Now, the automation, intelligence and data insights that were previously only available for digital channels are available in a voice option. Organizations can get up and running quickly, deliver an all-in-one contact center, improve knowledge authoring and management, and transform contract center routing to deliver improved service experiences for customers in both digital and voice channels.
Microsoft Dynamics 365 Connected Spaces coming to preview soon
In today’s environment of both digital and physical spaces, organizations have less visibility into their physical footprint than their digital channels, limiting their potential to optimize operations and improve employee and customer experiences. Microsoft Dynamics 365 Connected Spaces lets organizations harness observational data, use low-code capabilities to control observations and then produce real-time, predictive insights. Connected Spaces, coming to preview next month, is built to be extensible and works with existing hardware.
Connected Spaces lets organizations gain a new perspective in the way people move and interact in nearly any space, from the retail store to the
factory floor. Maximize safety in high-risk areas, observe queue management and seamlessly manage the connected factory, all in the name of understanding spaces in new and intelligent ways.
Microsoft Dynamics 365 adds key capabilities for Supply Chain Management
As manufacturers seek to create an agile and connected factory, Microsoft Dynamics 365 Supply Chain Management can now help customers predict demand shifts, supply constraints and equipment downtime by eliminating siloed Manufacturing Execution Systems (MES) to improve visibility of the production floor. Manufacturers can now work seamlessly with any MES system to unify data and generate predictive insights that can improve quality and optimize production processes.
Additionally, a priority-based enhancement to the planning optimization engine in Dynamics 365 Supply Chain Management helps eliminate zero-stock events. The solution automatically prioritizes replenishment of high-demand items in near real-time based on order priorities, current stock levels and projected inventory, leading to improved supply chain management. These updates are in preview.
Microsoft Customer Experience Platform puts organizations in control of customer data
The Microsoft Customer Experience Platform is a complete marketing solution that puts organizations in control of their customer data to personalize, automate and orchestrate customer journeys. It’s designed to deliver personalized and connected experiences from awareness to purchase. With a deep understanding of customers and rich, out-of-the-box insights, organizations can now understand and predict intent to deliver the right content on the right channel and in the right moment.
Microsoft Customer Experience Platform utilizes assets from across the company to create a cohesive Service as a Solution (SaaS) product while also delivering capabilities from Microsoft Customer Insights and Microsoft Dynamics 365 Marketing. Features in this new solution include:
Consent-enabled Consumer Data Platform (CDP): This feature, in preview, enables the chief data officer (CDO) to use consent data directly in the CDP as an integrated, first-class capability. Organizations can utilize consent enablement to build complete customer profiles, manage known and pseudonymous aliases, ensure consumer data practices are compliant and protect that data with robust privacy and security controls, all in real-time.
Expanded business-to-business (B2B) tools: A business-to-business CDP combines customer data from all sources – CRM, email, websites, point-of-sales, partner systems, social networks and more – and performs identity resolution at the contract and account level to generate unified profiles for individuals and accounts. This feature is now generally available.
Capabilities included as part of customer experience platform are:
Artificial Intelligence is impacting every aspect of our life’s. Not only it uses it’s models to create outputs used by businesses it also has also been leveraged to make life of developers easy and enable then to quickly build the apps they need and more easily learn advanced concepts in the process.
Microsoft has fine-tuned one of the most advanced natural language AI models on the planet, a model known as OpenAI GPT-3, to automatically generate Power Fx formulas based on natural language input.
Trained with 175 billion parameters, GPT-3 is an advanced natural language AI model that implements deep learning to be able to both understand and produce human-like text based on a prompt in natural language. Microsoft has a strategic collaboration with OpenAI, the developers of GPT-3, to be able to apply the model in products like Power Apps.
Below is an example of the generated Power FX formula based on the text inputted.
Refer to below links for details on the announcements and the technology under the hood.
With the new SAAS based Intelligent Order Management apps , business professionals gain real-time cross-channel inventory visibility and advanced analytical capabilities for better decision-making processes.
Using AI-powered order insights, they can re-model and orchestrate the order fulfillment journey with easy-to-use drag and drop tools, and proactively overcome bottlenecks— all while driving efficiencies across the supply chain to ensure that their customers’ needs are met on time and cost-effectively.
What I like most about this app is that it is built on top of latest technology stack and does not require rip and replace of existing applications. New connectors can be developed by partners and this is great opportunity for players in the market in this space.
The below image shows the architecture of the application :
Recently I faced an issue related to crypto encryption on a DEV BOX (Tier 1) Cloud hosted machine.
Below is the full error message
Encryption error occured with exception: Microsoft.Dynamics.Ax.Xpp.Security.CryptoEncryptionException: Encryption error occured with exception: Microsoft.Dynamics.AX.Configuration.CertificateHandler.NoCertificateFoundException: No certificate found for id 'EE19588CB6569148D75AB3096213505AEE312E85'. at Microsoft.Dynamics.AX.Configuration.CertificateHandler.CertificateHandlerBase.GetCertificateFromLocalStore(X509FindType findType, String findValue) at Microsoft.Dynamics.AX.Configuration.CertificateHandler.CertificateHandlerBase.GetFirstCertificateForId(String id) at Microsoft.Dynamics.Ax.Xpp.Security.CryptoEncryptionEngine.GetCryptoServiceProviderByThumbprintBuffer(String idList, Byte[] thumbprint) ---> Microsoft.Dynamics.AX.Configuration.CertificateHandler.NoCertificateFoundException: No certificate found for id 'EE19588CB6569148D75AB3096213505AEE312E85'. at Microsoft.Dynamics.AX.Configuration.CertificateHandler.CertificateHandlerBase.GetCertificateFromLocalStore(X509FindType findType, String findValue) at Microsoft.Dynamics.AX.Configuration.CertificateHandler.CertificateHandlerBase.GetFirstCertificateForId(String id) at Microsoft.Dynamics.Ax.Xpp.Security.CryptoEncryptionEngine.GetCryptoServiceProviderByThumbprintBuffer(String idList, Byte[] thumbprint) --- End of inner exception stack trace --- at Microsoft.Dynamics.Ax.Xpp.Security.CryptoEncryptionEngine.GetCryptoServiceProviderByThumbprintBuffer(String idList, Byte[] thumbprint) at Microsoft.Dynamics.Ax.Xpp.Security.CryptoEncryptionEngine.DecryptionInternal(Byte[] cipher, Boolean validateSignature, String purpose)
The error used to come on several forms like when editing customer address and customer groups. Upon initial research on internet, it looked like the certificates on the VM have expired and I did the rotate certificates from LCS but that did not helped.
Then we raise a case with Microsoft and we started tracing back recent changes done to environment and we encountered that we did a Database restore from another Tier 1 VM which had dual write mappings enabled on it.
Microsoft support team shared the below SQL script with us to clear the environment specific data so we ran the below script and then did IIS reset and restarted the VM. This solved the problem. Sharing it here in case it helps you. P.S –> Take your DB backups before running this script.
--Tidy up the batch server config from the previous environment
DELETE FROM SYSSERVERCONFIG
--Tidy up server sessions from the previous environment.
DELETE FROM SYSSERVERSESSIONS
--Tidy up printers from the previous environment
DELETE FROM SYSCORPNETPRINTERS
--Tidy up client sessions from the previous environment.
DELETE FROM SYSCLIENTSESSIONS
--Tidy up batch sessions from the previous environment.
DELETE FROM BATCHSERVERCONFIG
--Tidy up batch server to batch group relation table
DELETE FROM BATCHSERVERGROUP
--Set any waiting, executing, ready or canceling batches to withhold
UPDATE BatchJob
SET STATUS = 0
WHERE STATUS IN (1,2,5,7)
GO
--Disable Change Data Capture / CDC from Microservices team as it isn't compatible on another server
IF(1=(SELECT is_cdc_enabled FROM sys.databases WHERE name = DB_NAME()))
BEGIN
EXEC sys.sp_cdc_disable_db
END
IF '$(EnvSku)' = 'prod'
BEGIN
-- Batchjob History Cleanup from source environment
BEGIN TRAN
DELETE BATCHJOBHISTORY WHERE CREATEDDATETIME < GETDATE()
COMMIT TRAN
BEGIN TRAN
DELETE BATCHHISTORY WHERE NOT EXISTS (SELECT RECID FROM BATCHJOBHISTORY JOB WHERE JOB.RECID = BATCHJOBHISTORYID)
COMMIT TRAN
BEGIN TRAN
DELETE BATCHCONSTRAINTSHISTORY WHERE NOT EXISTS (SELECT RECID FROM BATCHHISTORY WHERE BATCHHISTORY.RECID = BATCHCONSTRAINTSHISTORY.BATCHID)
COMMIT TRAN
--Update SYSCONFIGURATION table
UPDATE SYSGLOBALCONFIGURATION SET [VALUE] = 1 WHERE NAME IN ( 'DATAAREAIDLITERAL', 'PARTITIONLITERAL ')
--Tidy up printers from the previous environment
UPDATE SYSGLOBALCONFIGURATION SET VALUE = 'SQLAZURE' WHERE NAME = 'BACKENDDB'
--Tidy up printers from the previous environment
UPDATE SYSGLOBALCONFIGURATION SET VALUE = 1 WHERE NAME = 'TEMPTABLEINAXDB'
END
IF '$(EnvSku)' = 'sandbox'
BEGIN
--disable all users except Admin, Microsoft account and Dynamics-alias users (they should go and re-enable as they see fit),
DECLARE @sqlDisableUserCmd nvarchar(256) = 'UPDATE UserInfo
SET ENABLE = 0
WHERE ID <> ''Admin'' AND NETWORKALIAS not like ''%dynamics.com%'''
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'UserInfo'
AND COLUMN_NAME = 'IsMicrosoftAccount')
BEGIN
SET @sqlDisableUserCmd = @sqlDisableUserCmd + ' AND IsMicrosoftAccount <> 1'
END
exec sp_executesql @sqlDisableUserCmd
--Proactively enable API Service Account. This is optional account customer can create, and will not be enabled in the source.
--Microsoft does not create this app user, but customers optionally can and they can choose which security role to give it.
--Enabling it during database refresh ensures that their app user is able to run X++ cleanup scripts immediately after
--the refresh is completed via Database Movement API from LCS.
UPDATE UserInfo
SET ENABLE = 1
WHERE ID = 'DBMovementAPI'
--Remove the SMTP server configuration to prevent sandbox from sending mails
UPDATE SysEmailParameters
SET SMTPRELAYSERVERNAME = '', MAILERNONINTERACTIVE = 'SMTP'
--Blank out all email addresses - in the case someone adds back SMTP config this will prevent accidentally sending mails
UPDATE LogisticsElectronicAddress
SET LOCATOR = ''
WHERE Locator LIKE '%@%'
--Remove all print management settings - there is email addresses stored in a container field here - again we want to prevent accidentally sending a vendor/customer a PO/SO email.
DELETE FROM PrintMgmtSettings
DELETE FROM PrintMgmtDocInstance
END
-- Change Maintenance Mode setting to disabled, in case it was enabled on the source environment
IF EXISTS (SELECT * FROM Information_Schema.Tables WHERE Table_Name = 'SQLSYSTEMVARIABLES')
BEGIN
IF EXISTS (SELECT * FROM SQLSYSTEMVARIABLES WHERE PARM = 'CONFIGURATIONMODE' AND VALUE = 1)
BEGIN
UPDATE SQLSYSTEMVARIABLES
SET VALUE = 0
WHERE PARM = 'CONFIGURATIONMODE'
END
END
-- Clean up tables where data is encrypted.
IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'SysEMailSMTPPassword')
BEGIN
TRUNCATE TABLE SysEMailSMTPPassword
END
IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'SYSOAUTHUSERTOKENS')
BEGIN
TRUNCATE TABLE SYSOAUTHUSERTOKENS
END
IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'B2BInvitationConfig')
BEGIN
TRUNCATE TABLE B2BInvitationConfig
END
IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'PersonnelIntegrationConfiguration')
BEGIN
TRUNCATE TABLE PersonnelIntegrationConfiguration
END
IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'CatExternalCatalogProperties')
BEGIN
TRUNCATE TABLE CatExternalCatalogProperties
END
--CDS Unlink Various Tables BEGIN
IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'DualWriteProjectConfiguration')
BEGIN
TRUNCATE TABLE DualWriteProjectConfiguration;
END
IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'DualWriteProjectFieldConfiguration')
BEGIN
TRUNCATE TABLE DualWriteProjectFieldConfiguration;
END
IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'DualWriteErrorLog')
BEGIN
TRUNCATE TABLE DualWriteErrorLog;
END
IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'DualWriteProjectConfigurationStaging')
BEGIN
TRUNCATE TABLE DualWriteProjectConfigurationStaging;
END
IF EXISTS (select * FROM Information_Schema.Tables WHERE Table_Name = 'BusinessEventsDefinition')
BEGIN
TRUNCATE TABLE BusinessEventsDefinition;
END
--CDS Unlink Various Tables END
--Remove all attachment references as storage account is not copied. Approved by Tariq Bell.
UPDATE t1
SET t1.storageproviderid = 0
, t1.accessinformation = ''
, t1.modifiedby = 'NonProdRestore'
, t1.modifieddatetime = getdate()
FROM docuvalue t1
WHERE t1.storageproviderid = 1
-- RETAILTRANSACTIONSERVICEPROFILE
DECLARE @MIGRATIONVALUE NVARCHAR(50)
SET @MIGRATIONVALUE = N'https://MIGRATION_VALUE'
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'RETAILTRANSACTIONSERVICEPROFILE'
AND COLUMN_NAME = 'AzureResource')
BEGIN
EXECUTE ('UPDATE dbo.[RETAILTRANSACTIONSERVICEPROFILE] SET ServiceHostUrl = ''' + @MIGRATIONVALUE + ''' , AzureResource = ''' + @MIGRATIONVALUE + ''' ')
END
ELSE
BEGIN
EXECUTE ('UPDATE dbo.[RETAILTRANSACTIONSERVICEPROFILE] SET ServiceHostUrl = ''' + @MIGRATIONVALUE + ''' ')
END
-- RETAILCHANNELPROFILEPROPERTY
UPDATE
dbo.[RETAILCHANNELPROFILEPROPERTY]
SET
[VALUE] = N'https://MIGRATION_VALUE'
WHERE
[VALUE] LIKE '%dynamics.com'
UPDATE
dbo.[RETAILCHANNELPROFILEPROPERTY]
SET
[VALUE] = N'https://MIGRATION_VALUE/Commerce'
WHERE
[VALUE] LIKE '%dynamics.com/Commerce'
UPDATE
dbo.[RETAILCHANNELPROFILEPROPERTY]
SET
[VALUE] = N'https://MIGRATION_VALUE/MediaServer'
WHERE
[VALUE] LIKE '%dynamics.com/MediaServer'
-- RETAILCONNDATABASEPROFILE (all rows)
UPDATE
dbo.[RETAILCONNDATABASEPROFILE]
SET
[CONNECTIONSTRING] = NULL
-- RETAILIDENTITYPROVIDER
UPDATE
dbo.[RETAILIDENTITYPROVIDER]
SET
[ISSUER] = N'https://sts.windows-ppe.net/MIGRATION_VALUE_' + SUBSTRING(CONVERT(nvarchar(50), NEWID()), 1, 8) + '/'
WHERE
[NAME] = N'Azure AD'
UPDATE
dbo.[RETAILIDENTITYPROVIDER]
SET
[ISSUER] = N'https://MIGRATION_VALUE_' + SUBSTRING(CONVERT(nvarchar(50), NEWID()), 1, 8) + '/auth'
WHERE
[NAME] = N'Commerce Identity Provider'
UPDATE
dbo.[RETAILHARDWAREPROFILE]
SET
[SECUREMERCHANTPROPERTIES] = NULL
UPDATE
dbo.[CREDITCARDACCOUNTSETUP]
SET
[SECUREMERCHANTPROPERTIES] = NULL
UPDATE
dbo.[RETAILCHANNELPAYMENTCONNECTORLINE]
SET
[SECUREMERCHANTPROPERTIES] = NULL
--FULLTEXT STOP LIST REMOVAL
-------------------------------------------------------------------------------------
-- ALTER FULLTEXT INDEX ON [TableName] SET STOPLIST = SYSTEM'
DECLARE @_SQLFullTextStopList NVARCHAR(4000)
IF object_id('tempdb..#tmpsetstoplist') IS NOT NULL
DROP TABLE #tmpsetstoplist;
CREATE TABLE #tmpsetstoplist (
TableName [nvarchar] (250)
);
DECLARE cur CURSOR
FOR select object_NAME(sys.fulltext_indexes.object_id) as TableName from sys.fulltext_indexes where stoplist_id != 0
OPEN cur;
DECLARE @TableName [nvarchar](250);
FETCH NEXT FROM cur INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #tmpsetstoplist (TableName)
VALUES (@TableName);
FETCH NEXT
FROM cur
INTO @TableName;
END;
CLOSE cur;
DEALLOCATE cur;
DECLARE cur CURSOR
FOR SELECT TableName FROM #tmpsetstoplist;
OPEN cur;
FETCH NEXT FROM cur INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @_SQLFullTextStopList = N'ALTER FULLTEXT INDEX ON ' + QUOTENAME(@TableName) + ' SET STOPLIST = SYSTEM'
PRINT (@_SQLFullTextStopList)
EXEC SP_EXECUTESQL @_SQLFullTextStopList
FETCH NEXT
FROM cur
INTO @TableName;
END;
CLOSE cur;
DEALLOCATE cur;
-------------------------------------------------------------------------------------
-- DROP FULLTEXT STOPLIST [FullTextStopListName];
IF object_id('tempdb..#dropfulltextstoplist') IS NOT NULL
DROP TABLE #dropfulltextstoplist;
CREATE TABLE #dropfulltextstoplist (
StopListName [nvarchar] (250)
);
DECLARE cur CURSOR
FOR select name from sys.fulltext_stoplists
OPEN cur;
DECLARE @StopListName [nvarchar](250);
FETCH NEXT FROM cur INTO @StopListName;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #dropfulltextstoplist (StopListName)
VALUES (@StopListName);
FETCH NEXT
FROM cur
INTO @StopListName;
END;
CLOSE cur;
DEALLOCATE cur;
DECLARE cur CURSOR
FOR SELECT StopListName FROM #dropfulltextstoplist;
OPEN cur;
FETCH NEXT FROM cur INTO @StopListName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @_SQLFullTextStopList = N'DROP FULLTEXT STOPLIST ' + QUOTENAME(@StopListName) + ';'
PRINT (@_SQLFullTextStopList)
EXEC SP_EXECUTESQL @_SQLFullTextStopList
FETCH NEXT
FROM cur
INTO @StopListName;
END;
CLOSE cur;
DEALLOCATE cur;
--END FULLTEXT STOPLIST REMOVAL
-- Start script: Turn off DMF change tracking for all entities
BEGIN TRY
-- 1. Find and drop triggers
DECLARE TriggerCursor CURSOR
FOR SELECT TriggerName FROM AifSqlCtTriggers
OPEN TriggerCursor
DECLARE @TriggerName NVARCHAR(200)
DECLARE @SqlStmt NVARCHAR(1000)
FETCH NEXT FROM TriggerCursor INTO @TriggerName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlStmt = 'DROP TRIGGER ' + @TriggerName
PRINT @SqlStmt
EXECUTE sp_executesql @SqlStmt
FETCH NEXT FROM TriggerCursor INTO @TriggerName
END
CLOSE TriggerCursor
DEALLOCATE TriggerCursor
-- 2. Delete rows from AifSqlCtTriggers
DELETE AifSqlCtTriggers WHERE Scope LIKE '%Export'
-- 3. Turn off table level change tracking
DECLARE TableCursor CURSOR
FOR SELECT TableName FROM AifSqlChangeTrackingEnabledTables
OPEN TableCursor
DECLARE @TableName1 NVARCHAR(162)
FETCH NEXT FROM TableCursor INTO @TableName1
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlStmt = 'ALTER TABLE ' + @TableName1 + ' DISABLE CHANGE_TRACKING'
PRINT @SqlStmt
EXECUTE sp_executesql @SqlStmt
FETCH NEXT FROM TableCursor INTO @TableName1
END
CLOSE TableCursor
DEALLOCATE TableCursor
-- 4. Clean up tables
DELETE FROM AIFSqlChangeTrackingEnabledTables
DELETE FROM AIFSqlCdcEnabledTables WHERE Scope LIKE '%Export'
UPDATE DMFEntity SET DMFChangeTrackingType = 0
END TRY
BEGIN CATCH
PRINT 'exception happened'
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
-- End script: Turn off DMF change tracking for all entities
It is important to do IIS reset and restart the virtual machine after running the script.
Brewing thought 