Search Results

Most deployment of Dynamics 365 Finance and Supply Chain are SaaS in the Microsoft cloud. With that said, there is an on-premises, self-hosted deployment option. Below outlines some tips for those on-premises to make the deployment and on-going support successful. 1. Requirement Changes. Monitor software and hardware requirement changes. Regular monitoring of the software and hardware requirements pages will help prevent surprises. For example, SQL Server 2019 became a requirement in Service Update 10.0.21. If this was not known, it all of the sudden becomes an unexpected project. References: Microsoft Dynamics 365 Finance + Operations (on-premises) supported software Troubleshoot on-premises deployments - Finance & Operations 2. Service Updates. Keep up to date on service updates. When issues due arise, Microsoft support will more times than not say the issue or bug is already fixed and you should upgrade to the latest service update. Do this often, our suggestion is once a quarter. While these are forced in the cloud, it is easy to get behind on-premises if not playing for regular updates. 3. Monitoring. Implement your own monitoring tools. For SaaS in the cloud, Lifecycle Services offers plenty of monitoring tools. On-premises gets none of those. With that said, it is paramount to deploy the correct SQL, infrastructure, and application monitoring tools to ensure uptime and performance. 4. Direct Database Access. Limit direct database use. As the saying goes, just because you can doesn’t mean you should. On-premises has the benefit of direct access to the SQL database. But this doesn’t mean you should use that. Not only does it lock the deployment into on-premises, but it could also corrupt the application. Only modify data directly if directed by Microsoft Support with a support case. Backup before you do! 5. Number of Environments. Determine correct number of environments. On-premises deployments require more planning for long-term requirements than a cloud deployment. In the cloud, adding or removing an entire environment is a series of clicks. Not so much on-premises. Ensure to consider the now and the future needs of the business to select the right number of environments. Consider how many ISVs could be implemented that may need a separate environment for testing. Typically, Production and UAT are the minimum number of environments. In our experience, this is too scant and at least three environments should exist, Production, UAT, and QA Testing. The more you have, the more you have to manage but the more flexibility you have for troubleshooting. Summary These summarize some of the tips and considerations when choosing to go on-premises vs SaaS in the cloud. Considering on-premises? Contact me for a consultation.

Recently, I experienced an issue where a custom destination component in SQL Server Integration Services (SSIS) was failing in Visual Studio. The component worked fine when the SSIS package was deployed to the server and worked at design time in designing the package. Very strange to say the least. Why would the package success at design time in Visual Studio, execute successfully when deployed to the target SQL Server, but fail at runtime in Visual Studio when the developer was testing? After much troubleshooting, we finally found an answer. Details below on the error and solution for anyone who encounters this! Version Info Visual Studio 2017 (15.9) SQL Server 2019 Note: this issue likely could occur in Visual Studio 2019 as well. We originally started to use Visual Studio 2019 but abandoned it was that version as SSIS as an extension, which we thought could be a factor in our original error. Error [SSIS.Pipeline] Error: Cannot find the connection manager with ID "{13E04AC8-6375-4328-A1CC-71AAB3AAAF21}" in the connection manager collection due to error code 0xC0010009. That connection manager is needed by "MyCustomComponent.Connections[CustomConnectionManager]" in the connection manager collection of "MyCustomComponent". Verify that a connection manager in the connection manager collection, Connections, has been created with that ID. Solution Ultimately, the solution required installing SQL Server 2019 and Integration Services into the same VM where the developer was working in Visual Studio. It appears that the Visual Studio installer was missing required libraries and executables to pick up the custom connection manager that the custom destination component was using. This explains why the package would succeed on the server as the full SQL Server installation contained all the necessary references.

Microsoft recently released the first version of the 2022 Release Wave 2 for Power Platform and Dynamics 365. Here is a quick article of what I am most looking forward to being released. Detailed plans below Microsoft Dynamics 365 2022 release wave 2 plan overview | Microsoft Docs Microsoft Power Platform 2022 release wave 2 plan overview | Microsoft Docs Features I am looking forward to:

Quick post about a simple, browser agnostic tool for opening the Table Browser more easily in Dynamics 365 Finance and Operations. Ever want to look at data in the table browser but you cannot/will not install a browser extension? Create a bookmarklet! What is a bookmarklet? A bookmarklet is a browser bookmark that contains JavaScript code, rather than a URL. This code when executed can automate actions on a page, prompt for information, and more. The below bookmarklet will allow a user to store the root URL to a Dynamics 365 Finance and Operations environment. It will then prompt for a short name and a table name to take the user to the table browser! How to use the "Go To Table Browser" bookmarklet Click on the bookmarklet JavaScript prompts for the short name of the environments you defined Next it prompts for the table name A new tab will open (if it does not, check your popup blocker!) taking you to the table browser in the environment and table specified. How to create I would love to give this bookmark out but...it's unique to each situation. Thus, you will have to create this yourself. The following will walk you through how to do this. Start by copying the code below into a code or text editor. Replace the URL in each CASE of the switch statement Rename the CASE for the short name of your environment Remove any CASE that are not needed. Next, minify the code by pasting into an online JavaScript minifier (such as https://jsminify.org/) to make the code into a single line. 🔑 This is key to making the code "paste-able" into the bookmark URL. Once the code is minified, create a new bookmark, and paste the code into the URL attribute Your bookmarklet should be ready to go! Try it out! Note: This bookmarklet assumes using the users default Company. It could be extended to add another prompt for Company. Code javascript: var rootURL, name, environment = prompt("Environment? (Dev1, Dev2, Build, UAT, Prod)", ""), errorMessage = "No Error"; switch (environment) { case "Dev1": rootURL = "https://{replaceme}.cloudax.dynamics.com"; break; case "Dev2": rootURL = "https://{replaceme}.cloudax.dynamics.com"; break; case "Build": rootURL = "https://{replaceme}.cloudax.dynamics.com"; break; case "UAT": rootURL = "https://{replaceme}.cloudax.dynamics.com"; break; case "Prod": rootURL = "https://{replaceme}.cloudax.dynamics.com"; break; default: rootURL = "ERROR", errorMessage = "Invalid Environment Reference" } "ERROR" == rootURL ? alert(errorMessage) : (name = prompt("Table Name?", ""), window.open(rootURL + "?mi=SysTableBrowser&prt=initial&limitednav=false&tablename=" + name.toLowerCase()));

What are Choices in Dataverse? Also, and formerly, known as Option Sets and Picklists, Choices are effectively a key-value pair object type in Dataverse that allow for a drop-down list of static options. The end user sees a label and the database stores its corresponding integer value. This is wonderful for application performance but poses a problem when reporting. The Problem While integers are great for performance in the application, they are not so helpful for reporting. Depending on how the data is extracted from the application, one may encounter only having the value of the integer with no label. Users of reports will not want to see the integer value, but the label that they are familiar with in the application. Thus, it is regularly desired to show the label. But how does one retrieve the label in a repeatable way? To address this problem, a SQL function can be used. The Function The function below is an in-line table valued function. This allows for a repeatable, straightforward way to retrieve a label. Not only is it repeatable but using an in-line table valued function instead of a scalar function will yield performant queries over any size of data. Some other solutions using scalar functions in SQL do not scale as well over large data sets. The function assumes using a single language is used. If needing to use multiple languages, need to pass in the language into the function as well. Otherwise leave it hardcoded as 1033 (English) CREATE FUNCTION [dbo].[GetChoiceLabel] ( @EntityName nvarchar(64), @ChoiceName nvarchar(64), @ChoiceValue int ) RETURNS TABLE AS RETURN ( SELECT StringMap.Value AS Label FROM dbo.StringMap AS StringMap WHERE StringMap.AttributeName=@ChoiceName AND StringMap.ObjectTypeCode=( SELECT TOP 1 Entity.ObjectTypeCode FROM dbo.Entity AS Entity WHERE Entity.LogicalName=@EntityName ) AND StringMap.AttributeValue = @ChoiceValue AND StringMap.langid=1033 ) Notes The above function may need to be updated for your exact situation. Using the Dataverse connector in Azure Data Factory does not require the "SELECT TOP 1" to translate the entity name to an objecttypecode. The Implementation To implement the above solution, you use the APPLY operator (similar to a join). APPLY dbo.GetChoiceLabel('Entity', 'Choice Name', Value Field) AS ChoiceLabel Here is another example using a table called item SELECT I.ItemType, ItemType.Label AS ItemTypeLabel FROM dbo.item as I APPLY dbo.GetChoiceLabel('item','itemType',Item.ItemType) AS ItemType Alternatives There are some alternatives to using an inline function. See a quick overview below.

Looping over a set of records is a very common operation conducted in applications, integrations, data warehouse, reporting, and more. It is generally advised to avoid looping in SQL if possible, as other programming languages can be much more efficient. Alas, there are times looping in SQL either unavoidable or the best option given a myriad of factors. There are various options for looping such as...... WHILE loop Recursive CTE CURSOR Nested stored procedures GoTo looping ......to name a few. This article will focus on the last bullet above that will be referred to as "GoTo Looping". GoTo, What is it? GoTo in Microsoft SQL Server is a keyword in the control flow of a SQL stored procedure. It allows logic to "go to" another point in the code. This effectively allows for jumping around the code and skipping/exiting conditions in an easy way. Example: DECLARE @condition1 bit='true'; @condition2 bit='true'; IF @condition1='true' BEGIN GOTO LabelB; END LabelA: GOTO LabelEnd; LabelB: IF @condition2='true' BEGIN GOTO LabelA; END LabelEnd: return; -- do nothing and exit procedure In the above example, the code would first pass through checking condition 1. It finds that this condition is true, thus it goes to LabelB. It completely skips LabelA code. Once the code in LabelB executes, it would then jump back up to LabelA due to the GOTO in LabelB after it's work is done. Finally, LabelA will go to LabelEnd and return out of the procedure. This is powerful to limit repetition (i.e. have D.R.Y. code). Some criticisms state that this makes the code harder to read, which is a valid point, but situationally dependent. Read more in the official Microsoft doc here. Another use of this technique is for looping. Read on to find out how GOTO can be used to loop over logic GoTo Looping Situation is as follows. A stored procedure is required to be executed for as many legal entities as business has, which will be referred to here after as "companies". It must be run one at a time. The benefit to doing this in SQL is the ease of debugging, logging each execution to a table, and rendering each result set. The solution below could be refactored to use a WHILE loop. When choosing between techniques, it is important to consider all the factors that contribute to SQL performance, code maintainability, and ultimately meeting the requirement. As a general statement, WHILE loops and GOTO loops have negligible performance differences. Example: The example below first sets variables to be used in the loop. Second it sets the company and inserts all companies into a table variable (this assumed a smaller number of companies, else a temporary table should be used). Finally, it loops through each company executing a stored procedure named procedure1 and logging to a table named ExecutionLog /* ========================================== Variable Declaration and Startup logic ========================================== */ --Declare variables for looping DECLARE @continue bit='true', @companycount int=0, @company nvarchar(8)=@company, @executionid uniqueidentifier=NewID(), @message nvarchar(4000) DECLARE @companylist TABLE(CompanyID nvarchar(8)); /* List of companies */ --Log execution EXECUTE dbo.usp_writeLog @ExecutionID=@executionid, @Company=@company, @StepName='Begin', @ExecutionStatus='Success', @ExecutionDescription='Process Begin' /* ========================================== Loop through company ========================================== */ INSERT INTO @companylist(CompanyID) SELECT Company=CL.CompanyID FROM dbo.CompanyList AS CL SetCompany: SELECT TOP 1 @company=CompanyID FROM @companylist DELETE FROM @companylist WHERE CompanyID=@company; SELECT @companycount=COUNT(*) FROM @companylist IF @companycount<=0 BEGIN SET @continue='false'; END --Log Execution EXECUTE dbo.usp_writeLog @ExecutionID=@executionid, @Company=@company, @StepName='Set Company', @ExecutionStatus='Success', @ExecutionDescription='Set the company for a multi-company execution' GOTO ExecuteLoop; RETURN; /* ========================================== Execute procedure by company ========================================== */ ExecuteLoop: --Project Direct Source BEGIN TRY EXECUTE dbo.usp_procedure1 @company=@company; END TRY BEGIN CATCH --Log error SET @message=ERROR_MESSAGE(); EXECUTE dbo.usp_writeLog @ExecutionID=@executionid, @Company=@company, @StepName='Procedure 1 Failed', @ExecutionStatus='FAIL', @ExecutionDescription=@message; END CATCH IF @continue='false' BEGIN DECLARE @failureFlag int; SELECT @failureFlag=COUNT(*) FROM dbo.ExecutionLog as EL WHERE EL.ExecutionID=@executionid AND EL.ExecutionStatus='FAIL' IF @failureFlag>0 BEGIN THROW 900705, 'FAILURE OCCURRED', 1; END ELSE BEGIN RETURN; END END ELSE BEGIN GOTO SetCompany; END Conclusion GoTo Looping, as shown in the above example, shows a technique in SQL to loop that is not well known. WHILE loops, cursors, recursive CTEs, and other options are much more well known. Why can the GoTo looping code be preferred? It somewhat comes down to a matter of preference. As an opinion, I have found this technique easier to work with than WHILE loops as there are more flexible scenarios to exiting loops and rejoining the loop using the GoTo technique. This certainly is debatable.

The topic of truncating SQL transaction logs is a regular point of confusion. Two issues normally arise, and are the reason for this post. Transaction Log Full Error For anyone working with SQL long enough they have undoubtedly encountered the following error in an application, integration, reporting query, etc. The transaction log for database 'db_name' is full due to 'LOG_BACKUP' This error occurs when the transaction log is full and can no longer grow for various reasons. Transaction Log Consumes Giga/terabytes of space In some cases, the above error is never encountered because the drive that the transaction log resides on either contains enough space to allow the log to grow. Whether the drive was pre-sized or a system administrator expands it, this can lead to huge transaction log sizes. In some cases, the transaction log may be GB or TB larger than the entire database itself! Below are two options to address these issues. Option 1 | Simple Recovery One way to make sure that the SQL transaction log does not grow out of control is to set the database to SIMPLE recovery model from FULL recovery model. The SIMPLE recovery model truncates the transaction log once a transaction completes. The benefit Since the log is being truncated as transactions are performed, the log never grows out of control. The downside Due to the transaction log being truncated in "real-time", it is only possible to recover to the last full backup performed. This works for some use cases and not for others. Cases where this works well Data warehousing (that can be completely rebuilt from scratch) "Scripting" databases that only hold scripts/functions Development and test environments Cases where this does not work well Production Application databases (OLTP) Data warehousing that cannot be rebuilt from scratch Option 2 | Backup Transaction Log The second option to ensure the transaction log does not grow out of control is to properly backup the log. It is suggested to use SIMPLE recovery when it makes sense. With that said, this is not always possible as outlined above. If FULL recovery should be used, backing up the transaction log regularly will truncate the log. Note, this is backing up the TRANSACTION LOG itself, not backing up the data. While backing up the transaction log should be a part of any disaster recovery plan, sometimes only the data is backed up with full backups. This will lead to the transaction log growing out of control and encountering the issues mentioned at the beginning of this post. Conclusion Transaction log management is easy to implement using the various options above, yet easy to get wrong or overlook. Make life easy and use simple recovery or backup the transaction log to ensure the log size does not grow out of control.

A critical cybersecurity action from Microsoft to help the masses went relatively unnoticed. See the below article from ZDNet describing how Microsoft saved a potential disaster for unsophisticated IT organizations. One excerpt that is particularly frightening "...this means that whoever controls corp.com can passively intercept private communications from hundreds of thousands of computers that end up being taken outside of a corporate environment which uses this 'corp' designation for its Active Directory domain." Could you imagine the consequences if a nefarious actor purchased this domain given the sophisticated hacking operations now-a-days! Wow. Read more below Microsoft buys corp.com domain in the name of customer security | ZDNet

As a helpful quick reference to the production order status. Looking for a comprehensive list of all enum values and labels? Checkout Enumlytics for Dynamics 365 Finance and Supply Chain by Corterra Solutions here.

As a helpful quick reference to the production order status. Looking for a comprehensive list of all enum values? Checkout Enumlytics for Dynamics 365 Finance and Supply Chain by Corterra solutions here.

As a helpful quick reference to all the ledger posting types that define the type of transaction posted to the General Ledger in Dynamics 365 Finance and Operations Looking for a comprehensive list of all enum values? Checkout Enumlytics for Dynamics 365 F&O by Corterra Solutions here.

As a helpful quick reference, here are the enum labels and values for key InventTrans fields. Looking for a comprehensive list of all enum values? Checkout Enumlytics for Dynamics 365 Finance and Supply Chain by Corterra Solutions here. InventTransType This corresponds to the ReferenceCategory on the InventTransOrigin. InventTransPostingType