MSBUILD : error MSB1009: Project file does not exist. Switch: C:\DynamicsSDK\VSOAgent\_work\4\s\AXModulesBuild.proj ##[error]Process 'msbuild.exe' exited with code '1' in Dynamics 365 finance and operations

I got the below error while trying to builld a new deployable package on visual studio azure DevOps.

MSBUILD : error MSB1009: Project file does not exist. Switch: C:\DynamicsSDK\VSOAgent\_work\4\s\AXModulesBuild.proj ##[error]Process 'msbuild.exe' exited with code '1'.


Reason:  AXModulesBuild is missing from the branch, but it is defined in "Build the solution". And when I kick off a new build it's looking for AXModulesBuild.

Solution:In my case I copied it from another branch.

Click on 3dots-> Download

And go to the branch where you are trying to kick- off the new build and upload the file.

After that check-in the change.

That's it. Now kick-off the new build.

Error message: *** Error importing database:Could not import package. Error SQL72014: .Net SqlClient Data Provider: Msg 195, Level 15, State 10, Procedure AXPerf_CreateIndex

I was getting the below error while trying to import the database into my DEV box which was originally exported from sandbox environment.

Error message: 

*** Error importing database:Could not import package.

Error SQL72014: .Net SqlClient Data Provider: Msg 195, Level 15, State 10, Procedure AXPerf_CreateIndex, Line 1 'TRIM' is not a recognized built-in function name.

Error SQL72045: Script execution error.  The executed script:

CREATE PROCEDURE [dbo].[AXPerf_CreateIndex]

@idxStmt NVARCHAR (MAX), @bypass BIT=0, @message NVARCHAR (MAX) OUTPUT, @returnStatus INT OUTPUT, @createdIdxStmt NVARCHAR (MAX) OUTPUT

AS

BEGIN

    SET NOCOUNT ON;

    PRINT 'AXPerf_CreateIndex v1.0';

    DECLARE @DoOpenCursorExistsOnObject AS INT = 0;

    DECLARE @kstart AS INT, @kend AS INT, @istart AS INT, @iend AS INT, @isColStrIdx AS BIT, @IncColCount AS INT;

    DECLARE @iName AS VARCHAR (1000), @fullyQualifiedTName AS VARCHAR (1000), @scName AS VARCHAR (20), @tName AS VARCHAR (128), @KeyCol AS VARCHAR (MAX), @IncCol AS VARCHAR (MAX);

    SET @idxStmt = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@idxStmt, CHAR(13), ' '), CHAR(10), ' '), CHAR(9), ' '), ' ', '<>'), '><', ''), '<>', ' ');

    SET @kstart = CharINDEX('(', @idxStmt);

    SET @kend = CharINDEX(')', @idxStmt);

    SET @istart = CASE WHEN CharIndex('include', @idxStmt, @kend + 1) != 0 THEN CHARINDEX('(', @idxStmt, @kend + 1) ELSE 0 END;

    SET @iend = CHARINDEX(')', @idxStmt, @kend

Error SQL72014: .Net SqlClient Data Provider: Msg 195, Level 15, State 10, Procedure AXPerf_CreateIndex, Line 1 'TRIM' is not a recognized built-in function name.

Error SQL72045: Script execution error.  The executed script:

CREATE PROCEDURE [dbo].[AXPerf_CreateIndex]

@idxStmt NVARCHAR (MAX), @bypass BIT=0, @message NVARCHAR (MAX) OUTPUT, @returnStatus INT OUTPUT, @createdIdxStmt NVARCHAR (MAX) OUTPUT

AS

BEGIN

    SET NOCOUNT ON;

    PRINT 'AXPerf_CreateIndex v1.0';

    DECLARE @DoOpenCursorExistsOnObject AS INT = 0;

    DECLARE @kstart AS INT, @kend AS INT, @istart AS INT, @iend AS INT, @isColStrIdx AS BIT, @IncColCount AS INT;

    DECLARE @iName AS VARCHAR (1000), @fullyQualifiedTName AS VARCHAR (1000), @scName AS VARCHAR (20), @tName AS VARCHAR (128), @KeyCol AS VARCHAR (MAX), @IncCol AS VARCHAR (MAX);

    SET @idxStmt = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@idxStmt, CHAR(13), ' '), CHAR(10), ' '), CHAR(9), ' '), ' ', '<>'), '><', ''), '<>', ' ');

    SET @kstart = CharINDEX('(', @idxStmt);

    SET @kend = CharINDEX(')', @idxStmt);

    SET @istart = CASE WHEN CharIndex('include', @idxStmt, @kend + 1) != 0 THEN CHARINDEX('(', @idxStmt, @kend + 1) ELSE 0 END;

    SET @iend = CHARINDEX(')', @idxStmt, @kend

Error SQL72014: .Net SqlClient Data Provider: Msg 195, Level 15, State 10, Procedure AXPerf_CreateIndex, Line 1 'TRIM' is not a recognized built-in function name.

Error SQL72045: Script execution error.  The executed script:

CREATE PROCEDURE [dbo].[AXPerf_CreateIndex]

@idxStmt NVARCHAR (MAX), @bypass BIT=0, @message NVARCHAR (MAX) OUTPUT, @returnStatus INT OUTPUT, @createdIdxStmt NVARCHAR (MAX) OUTPUT

AS

BEGIN

    SET NOCOUNT ON;

    PRINT 'AXPerf_CreateIndex v1.0';

    DECLARE @DoOpenCursorExistsOnObject AS INT = 0;

    DECLARE @kstart AS INT, @kend AS INT, @istart AS INT, @iend AS INT, @isColStrIdx AS BIT, @IncColCount AS INT;

    DECLARE @iName AS VARCHAR (1000), @fullyQualifiedTName AS VARCHAR (1000), @scName AS VARCHAR (20), @tName AS VARCHAR (128), @KeyCol AS VARCHAR (MAX), @IncCol AS VARCHAR (MAX);

    SET @idxStmt = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@idxStmt, CHAR(13), ' '), CHAR(10), ' '), CHAR(9), ' '), ' ', '<>'), '><', ''), '<>', ' ');

    SET @kstart = CharINDEX('(', @idxStmt);

    SET @kend = CharINDEX(')', @idxStmt);

    SET @istart = CASE WHEN CharIndex('include', @idxStmt, @kend + 1) != 0 THEN CHARINDEX('(', @idxStmt, @kend + 1) ELSE 0 END;

    SET @iend = CHARINDEX(')', @idxStmt, @kend

Error SQL72014: .Net SqlClient Data Provider: Msg 319, Level 15, State 1, Procedure AXPerf_CreateIndex, Line 1 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a chan

Reason: The problem is with the stored procedure AXPerf_CreateIndex   

Solution: Connect to your sandbox database and drop this stored procedure before exporting it again. Once you drop it reexport the database again and import into the DEV box.

Below are the queries I used . 

--Query 1

select * from dbo.sysobjects where id = object_id(N'dbo.AXPerf_CreateIndex')

-- Query 2

DROP PROCEDURE  AXPerf_CreateIndex; GO

@Rahul Talaseela

The record already exists error in data entities in dynamics 365 finance & operations

I was keep getting the below error while exporting sales invoice lines. I created a composite data entity for exporting sales invoices with lines into XML format.

Cannot create a record in Test Sales invoice line (SalesInvoiceLineStagingV2). Item: XXXXXX.
The record already exists.

Reason:
After doing some research I realized that the cluster & staging index is missing for sales invoice header and lines.

Solution:

I added both cluster & staging index for sales invoice header and lines staging tables and added them in the properties.

Recompile the models & refresh entities.

That's it.

Hire New Worker Button Disabled in Dynamics 365 Finance & Operations

One of my functional consultants complained that she was not able to create new Employees/workers in Human resources module.

After doing some research I realized that the personal numbers are already exist in the HcmWorker table what the number sequence is using.

Ex: Personal number 131 is alreasy exist in HcmWorker, But the number sequence is assigning the same number for the new worker.

Solution:

Go to Human resources -> setup-> Human resources shared parameters -> Number sequence and find the number sequence code for Personnel number.

Open HcmWorker table and find out the latest personal number and update the number sequence with the next number.

Please note that disable continuous button before updating the next number. Enable it after the updating it.


That's it.

Posting An attempt was made to set a report parameter 'CustInvoiceJourAdjustmentRecId_TH' that is not defined in this report.

I was getting the below errors while tying to run sales invoice report in Dynamics 365 F&O .
Version: 8.0
PU: 23

Posting An attempt was made to set a report parameter 'CustInvoiceJourAdjustmentRecId_TH' that is not defined in this report.

Posting An attempt was made to set a report parameter 'CreatedTransactionId' that is not defined in this report.


Reason: 

In my case we upgraded D365 from 7.0 version to 8.0 version and Microsoft added some new parameters in the sales invoice report and those are not refreshed in the sales invoice report.


Solution: 

I refreshed the data sets in the sales invoice report. Then, automatically below parameter are added to the report. 

CustInvoiceJourAdjustmentRecId_TH


I was not able to add the "CreatedTransactionId" automatically in the parameters. So, I duplicated one of existing parameters and I renamed it to CreatedTransactionId.

Later, I made them nullable and allow blanks true.

And redeployed the report.


That's it.

Debug a copy of the production database in Dynamics 365 Finance & Operations

Database movement operations are a suite of self-service actions that can be used as part of data application lifecycle management (DataALM). This tutorial shows how to debug specific data and transactions from a recent copy of production data.

In this tutorial, you will learn how to:

• Refresh the user acceptance testing (UAT) environment.
• Add the IP address of your developer environment to an approved list ("whitelist").
• Update your developer environment so that it connects to the UAT database.
• Set a breakpoint, and start to debug the data.

As an example of this scenario, a customer who has already gone live with Microsoft Dynamics 365 for Finance and Operations wants to debug a recent copy of production transactions from his or her development environment. In this way, the customer will be able to debug specific transactions that are stuck, or develop new features and reports by using realistic datasets.

Prerequisites

To do a refresh operation, you must have your production environment deployed, or you must have a minimum of two standard UAT environments. To complete this tutorial, you must have a developer environment deployed.

 Important

For debugging, we highly recommend that you use a DevTest environment that runs the same code and business logic that are available in your UAT environment. If you use multiple branches in version control, we recommend that the DevTest environment that is used to debug recent UAT or production transactions be connected to the same branch that you use to build packages for UAT and, later, for production. In this way, you don't have to run a database synchronization between your DevTest environment and UAT database, because the schema will be compatible. Historically, this environment is known as a Hotfix/Support environment, because it's outside your usual code promotion path.

Refresh the UAT environment

This refresh operation overwrites the UAT environment with the latest copy of the production database. To complete this step, follow the instructions in Refresh for training purposes.

Add your IP address to a whitelist

By default, all Sandbox Standard Acceptance Test environments use Microsoft Azure SQL Database as their database platform. The databases for these environments are protected by firewalls that restrict access to the Application Object Server (AOS) with which it was originally deployed.

However, an exception can be made so that you can connect your developer environment (cloud-hosted or Microsoft-managed) directly to the UAT database. To connect your developer environment directly to the UAT database, you must obtain your IP address on the developer virtual machine (VM).

On the sandbox AOS VM, open Microsoft SQL Server Management Studio (SSMS), and connect to the database by using the information that is available from the environment details page in Microsoft Dynamics Lifecycle Services (LCS). Find the username record for axdbadmin, and note the server and database in the format {sqlServer\sqlDatabase}.

In SSMS, enter the SQL Server, username, and password. On the Connection Properties tab, explicitly enter the database name from the axdbadmin record in LCS.

After you're connected, open a query against the database, and enter your IP address in the following Transact-SQL (T-SQL) command.

-- Create database-level firewall setting for IP a.b.c.d 
EXECUTE sp_set_database_firewall_rule N'Debugging rule for DevTest environment', 'a.b.c.d', 'a.b.c.d'; 

Back in your developer environment, open SSMS, and try to connect by using the same axdbadmin credentials against the UAT database. Verify that you can connect before you continue with the next steps.

 Note

Every time that a refresh is done, the firewall whitelist is reset. You must add your DevTest environments back to this database when they are required in the future.

Update a OneBox DevTest environment to connect to the UAT database

In your developer environment, you must now update the web.config file to change the database connection. This step will let you run your local code and binaries that are configured against the database from UAT.

On your Services drive, go to the AoSService\WebRoot directory. (Typically, the Services drive is drive J or K.) Find the file that is named web.config, and make a backup of it. Then open the web.config file in Notepad or another editor, and find the following configurations:

• DataAccess.Database
• DataAccess.DBServer
• DataAccess.SqlPwd
• DataAccess.SqlUser

Update these configurations so that they use the values from the environment details page for the UAT environment in LCS.

<add key="DataAccess.Database" value="<example_axdb_fromAzure>" />
<add key="DataAccess.DbServer" value="<example_axdb_server.database.windows.net>" />
<add key="DataAccess.SqlPwd" value="<axdbadmin_password_from_LCS>" />
<add key="DataAccess.SqlUser" value="axdbadmin" />

Save the file. If you're operating in a cloud-hosted environment, run IISRESET. If you're on a Microsoft-managed developer machine and have limited permissions, make sure that Microsoft Visual Studio is closed.

Finally, open a web browser, go to the URL of your DevTest environment, and verify that you're pulling data from the UAT database.

Debug transactions in the DevTest environment

Now that your environment is correctly reconfigured, you can open Visual Studio and set a breakpoint in the application code that best meets your needs. Note that users in the UAT environment aren't affected while you debug in the DevTest environment.

Best practices

Here are some common best practices that will help guarantee that your debugging experience is quick, reliable, and doesn't disrupt other users in your organization:

• Make sure that the version of the code and binaries in the DevTest environment exactly match the version in the UAT environment. Connect the DevTest environment to the same branch that you build packages for deployment from. Alternatively, connect it to a "HotfixSupport" branch that is kept up to date with the latest customizations that are released.
• Don't run a database synchronization from Visual Studio. Otherwise, you will affect the availability of the schema in the UAT database and might affect users in the UAT environment.

Actual Post : https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/database/dbmovement-scenario-debugdiag

Error: The transaction on voucher do not balance as per dd/mm/yy (accounting currency: xx -reporting currency:xx) in D365 finance & operations

 I was getting the below error while posting the sales invoice in D365 F&O.

The transactions on voucher ARI00958686 do not balance as per 2/27/2019. (accounting currency: -0.12 - reporting currency: -0.06)


In this case my accounting currency is EUR and reporting is USD.

Reason:

The problem was with the rounding limit.

Solution:

In my case I updated the rounding to 0.12 and posted the transaction successfully.

@Rahul Talasila