BC-You must specify Realized Losses Acc. in Currency Code

 If this happens on any journal screen, and you know things are setup correctly, most likely a user has entered two different values in the "Amount" and "Amount($)" fields.

Amount = Original customer currency

Amount($) = Your system base currency

If you enter two different values, an exchange rate is calculated and stored for that line.

You must delete the line and re-enter it, ensuring that you fill in the "Amount" field only, and allow the "Amount($)" field to populate automatically

"Could not remove directory"

 Your windows service account does not have access to the location.

• Navigate to the location it's trying to delete
• Right click>Properties>Sharing
• Add the ns service\mssqlserver (or whichever account does not have access)
• Choose read\write
• Click ok

Business Central - Adjust Cost - Item Ledger entry is Missing

 If a database has become corrupted, or item ledger entries have been lost, the Adjust cost job will give an error when it encounters value entries that point to non-existent item ledger entries.

First resolution - restore your last backup. Working with a damaged database is never fun. I do not 

recommend you ever do anything in the rest of this post.

-------------------------------------------------------

--------------------------------

Run this to identify VE's with missing ILE's.

You can either insert the missing ILE's, or delete the VE's with the bad references.

SELECT VE.timestamp, VE.[Entry No_], VE.[Item No_], VE.[Posting Date], VE.[Item Ledger Entry Type], VE.[Source No_], VE.[Document No_], VE.Description, VE.[Location Code], VE.[Inventory Posting Group], VE.[Source Posting Group], 

                  VE.[Item Ledger Entry No_], VE.[Valued Quantity], VE.[Item Ledger Entry Quantity], VE.[Invoiced Quantity], VE.[Cost per Unit], VE.[Sales Amount (Actual)], VE.[Salespers__Purch_ Code], VE.[Discount Amount], VE.[User ID], VE.[Source Code], 

                  VE.[Applies-to Entry], VE.[Global Dimension 1 Code], VE.[Global Dimension 2 Code], VE.[Source Type], VE.[Cost Amount (Actual)], VE.[Cost Posted to G_L], VE.[Reason Code], VE.[Drop Shipment], VE.[Journal Batch Name], 

                  VE.[Gen_ Bus_ Posting Group], VE.[Gen_ Prod_ Posting Group], VE.[Document Date], VE.[External Document No_], VE.[Cost Amount (Actual) (ACY)], VE.[Cost Posted to G_L (ACY)], VE.[Cost per Unit (ACY)], VE.[Document Type], 

                  VE.[Document Line No_], VE.[Order Type], VE.[Order No_], VE.[Order Line No_], VE.[Expected Cost], VE.[Item Charge No_], VE.[Valued By Average Cost], VE.[Partial Revaluation], VE.Inventoriable, VE.[Valuation Date], VE.[Entry Type], 

                  VE.[Variance Type], VE.[Purchase Amount (Actual)], VE.[Purchase Amount (Expected)], VE.[Sales Amount (Expected)], VE.[Cost Amount (Expected)], VE.[Cost Amount (Non-Invtbl_)], VE.[Cost Amount (Expected) (ACY)], 

                  VE.[Cost Amount (Non-Invtbl_)(ACY)], VE.[Expected Cost Posted to G_L], VE.[Exp_ Cost Posted to G_L (ACY)], VE.[Dimension Set ID], VE.[Job No_], VE.[Job Task No_], VE.[Job Ledger Entry No_], VE.[Variant Code], VE.Adjustment, 

                  VE.[Average Cost Exception], VE.[Capacity Ledger Entry No_], VE.Type, VE.No_, VE.[Return Reason Code],

                  ILE.[Entry No_] AS ILENo

FROM     [CRONUS$Item Ledger Entry] AS ILE RIGHT OUTER JOIN

                  [CRONUS$Value Entry] AS VE ON ILE.[Entry Type] = VE.[Item Ledger Entry Type] AND ILE.[Entry No_] = VE.[Item Ledger Entry No_]

WHERE  (ILE.[Entry No_] IS NULL and VE.[Posting Date]>= '2021-08-10')

Business Central vscode - dotnet is missing

 in VSCode go to Bottom left Settings>Extensions>Assembly Probing Paths

add this path, or your requivalent

C:\Program Files\Microsoft Dynamics 365 Business Central\180\Service\Add-ins\LSRetail

reopen vscode

Overflow under conversion of Decimal 18

LS 17.4.0394

 10016650 co utility - increment counter that does not do decimals

Resolution: Modify the base LS Code to remove this line

LS Insight - Execute dimension Foreach Error - Data would be truncated

 Left trim the Source.Description fields to 50

------------------------------------------

Update the dimMergedItemVariant stored procedure with this code

------------------------------------------------------------------------------------

 ALTER PROCEDURE [dbo].[dimMergedItemVariant] 

@ItemVariant [dbo].[ItemVariantType] READONLY

AS 

  BEGIN

      WITH tmaxrowid

           AS (SELECT sIVA.[companyprefix],

                      sIVA.[item no_],

                      sIVA.[code],

                      Max(sIVA.[bigint_timestamp]) AS [RowID]

               FROM   [stg$item variant] sIVA

               GROUP  BY sIVA.[companyprefix],

                         sIVA.[item no_],

                         sIVA.[code]),

           titemvariant

           AS (SELECT sIVA.[companyprefix],

                      sIVA.[item no_],

                      sIVA.[code],

                      sIVA.[description],

                      sIVA.[description 2],

                      sIVA.[common item no_],

                      Getutcdate()                 AS [BatchDate],

                      Max(sIVA.[bigint_timestamp]) AS [RowID]

               FROM   [stg$item variant] sIVA

               GROUP  BY sIVA.[companyprefix],

                         sIVA.[item no_],

                         sIVA.[code],

                         sIVA.[description],

                         sIVA.[description 2],

                         sIVA.[common item no_])

      MERGE [DW].[ditemvariant] AS Target

      using (SELECT COALESCE(dCOM.[sk_company], -1) AS [Company],

                    tIVA.[item no_],

                    tIVA.[code],

                    tIVA.[description],

                    tIVA.[description 2],

                    tIVA.[common item no_],

                    tIVA.[batchdate],

                    tMRI.[rowid]

             FROM   [titemvariant] tIVA

                    RIGHT JOIN [tmaxrowid] tMRI

                            ON tIVA.[companyprefix] = tMRI.[companyprefix]

                               AND tIVA.[item no_] = tMRI.[item no_]

                               AND tIVA.[code] = tMRI.[code]

                               AND tIVA.[rowid] = tMRI.[rowid]

                    LEFT JOIN [DW].[dcompany] dCOM

                           ON tIVA.[companyprefix] = dCOM.[companyprefix]) AS

            Source

      ON Target.[item no_] = source.[Item No_]

         AND Target.[code] = source.[Code]

         AND Target.[company] = source.[Company]

      WHEN matched THEN

        UPDATE SET [item no_] = Source.[item no_],

                   [code] = Source.[code],

                   [description] = left(Source.[description],50),

                   [description 2] = Source.[description 2],

                   [common item no_] = Source.[common item no_],

                   [batchdate] = Source.[batchdate],

                   [rowid] = Source.[rowid]

     

      

      WHEN NOT matched BY target THEN

        INSERT ([company],

                [item no_],

                [code] ,

                [description] ,

                [description 2],

                [common item no_],

                [batchdate],

                [rowid] 

                )

        VALUES (Source.[company],

                Source.[item no_],

                Source.[code] ,

                left(Source.[description],50),

                Source.[description 2],

                Source.[common item no_],

                Source.[batchdate],

                Source.rowid 

                );;

                

            

  END

SELECT

    'RowCount' = @@rowcount

LS Insight - GETLSCentralColumnMetadata - Msg 536, Level 16, State 4, Line 7 Invalid length parameter passed to the RIGHT function.

Other Errors 

Failure happened on 'Source' side. 'Type=System.Data.SqlClient.SqlException,Message=There is insufficient system memory in resource pool 'internal' to run this query.,Source=.Net SqlClient Data Provider,SqlErrorNumber=701,Class=17,ErrorCode=-2146232060,State=123,Errors=[{Class=17,Number=701,State=123,Message=There is insufficient system memory in resource pool 'internal' to run this query.,},],' 

Cause 

This line causes an error when table names without '$' in the name are encountered 

--------------------------------------------------------------------- 

WHEN 'Company' THEN 'Company' 

            ELSE UPPER(RIGHT(col.TABLE_NAME, CHARINDEX('$', REVERSE(col.TABLE_NAME)) - 1)) 

---------------------------------------------------------------------- 

Resolution 

Replace the entire sql command, dump the right into a new table and column

Do not use Temp tables in Azure Data factory, just use a regular table 

------------------------------------------------------------------------ 

 

IF OBJECT_ID('LSCentral..TempInf') IS NOT NULL DROP TABLE TempInf 
--select top 1 *, 
select *, 
CASE TABLE_NAME 
WHEN 'Company' THEN 'Company' 
--ELSE UPPER(RIGHT(col.TABLE_NAME, CHARINDEX('$', REVERSE(col.TABLE_NAME)) - 1)) 
--ELSE UPPER(RIGHT(col.TABLE_NAME, CHARINDEX('$', REVERSE(col.TABLE_NAME)) )) 
 
ELSE CASE CHARINDEX('$', TABLE_NAME) 
WHEN 0 THEN 'Company' 
else 
UPPER(RIGHT(TABLE_NAME, CHARINDEX('$', REVERSE(TABLE_NAME)) - 1)) 
end 
END AS ExtGuid 
into TempInf 
from INFORMATION_SCHEMA.COLUMNS 
 

 
 

/* 
WHERE (TABLE_NAME LIKE '_%$_%$_____%' 
AND TABLE_NAME NOT LIKE '%$VSIFT$%' 
OR TABLE_NAME = 'Company') 
*/ 
 
IF EXISTS (SELECT 
[App ID] 
FROM [dbo].[NAV App Installed App] 
WHERE [App ID] = '5ECFC871-5D82-43F1-9C54-59685E82318D') 
BEGIN 
-- Get the Primary key for each table 
WITH keycolumn 
AS 
(SELECT 
Col.TABLE_NAME 
,Col.COLUMN_NAME 
,Col.COLUMN_NAME + ' = ' + Col.COLUMN_NAME AS JoinOnCol 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab 
,INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col 
WHERE Col.CONSTRAINT_NAME = Tab.CONSTRAINT_NAME 
AND Col.TABLE_NAME = Tab.TABLE_NAME 
AND Tab.CONSTRAINT_TYPE = 'PRIMARY KEY' 
AND CHARINDEX('$', REVERSE(Col.TABLE_NAME)) > 0) 
 
 
 
-- Get APP ID for Installed apps and add Company as a fixed value 
, 
Ext 
AS 
(SELECT 
CAST([App ID] AS NVARCHAR(200)) [App ID] 
,Publisher 
FROM [NAV App Installed App] 
UNION 
SELECT 
'Company' 
,'Company') 
 
 
--Get all columns and metadata for all tables 
-- Extract the table name excluding the company prefix and extension GUID 
-- Extract the Company name / table prefix 
-- Extract the Extension GUID 
-- Mark the Primary Keys 
, 
AllTableColumns 
AS 
(SELECT DISTINCT 
col.TABLE_NAME 
,col.COLUMN_NAME 
,col.DATA_TYPE 
,col.ORDINAL_POSITION 
,col.CHARACTER_MAXIMUM_LENGTH 
,col.NUMERIC_PRECISION 
,col.NUMERIC_SCALE 
,rowcounts.rows CountRows 
,CASE col.TABLE_NAME 
WHEN 'Company' THEN col.TABLE_NAME 
ELSE SUBSTRING(col.TABLE_NAME, CHARINDEX('$', col.TABLE_NAME) + 1, ABS(CHARINDEX('$', col.TABLE_NAME, CHARINDEX('$', col.TABLE_NAME) + 1) - CHARINDEX('$', col.TABLE_NAME) - 1)) 
END AS BaseTableName 
,CASE col.TABLE_NAME 
WHEN 'Company' THEN 'Company' 
ELSE LEFT(col.TABLE_NAME, ABS(CHARINDEX('$', col.TABLE_NAME) - 1)) 
END AS TablePrefix 
,CASE col.TABLE_NAME 
WHEN 'Company' THEN 'Company' 
--ELSE UPPER(RIGHT(col.TABLE_NAME, CHARINDEX('$', REVERSE(col.TABLE_NAME)) - 1)) 
--ELSE UPPER(RIGHT(col.TABLE_NAME, CHARINDEX('$', REVERSE(col.TABLE_NAME)) )) 
/* 
ELSE CASE CHARINDEX('$', col.TABLE_NAME) 
WHEN 0 THEN 'Company' 
else 
UPPER(RIGHT(col.TABLE_NAME, CHARINDEX('$', REVERSE(col.TABLE_NAME)) - 1)) 
end 
*/ 
ELSE col.ExtGuid 
END AS ExtensionGUID 
,IIF(col.COLUMN_NAME = keycolumn.COLUMN_NAME, 'TRUE', 'FALSE') Keycol 
 
 
--FROM [INFORMATION_SCHEMA].[COLUMNS] col 
FROM TempInf col 
LEFT JOIN keycolumn 
ON col.TABLE_NAME = keycolumn.TABLE_NAME 
AND col.COLUMN_NAME = keycolumn.COLUMN_NAME 
LEFT JOIN (SELECT DISTINCT 
p.rows 
,t.name 
FROM sys.partitions p 
,sys.tables t 
WHERE p.object_id = t.object_id) rowcounts 
ON col.TABLE_NAME = rowcounts.name 
 
WHERE (col.TABLE_NAME LIKE '_%$_%$_____%' 
AND col.TABLE_NAME NOT LIKE '%$VSIFT$%' 
OR col.TABLE_NAME = 'Company') 
GROUP BY col.TABLE_NAME 
,col.COLUMN_NAME 
,col.DATA_TYPE 
,col.ORDINAL_POSITION 
,col.CHARACTER_MAXIMUM_LENGTH 
,col.NUMERIC_PRECISION 
,col.NUMERIC_SCALE 
,rowcounts.rows 
,keycolumn.COLUMN_NAME 
,col.ExtGuid 
) 
 
 
SELECT 
ATC.[TABLE_NAME] 
,ATC.[COLUMN_NAME] 
,[DATA_TYPE] 
,[ORDINAL_POSITION] 
,[CHARACTER_MAXIMUM_LENGTH] 
,[NUMERIC_PRECISION] 
,[NUMERIC_SCALE] 
,[CountRows] 
,[BaseTableName] 
,[TablePrefix] 
,[ExtensionGUID] 
,[Keycol] 
,Ext.Publisher 
FROM AllTableColumns ATC 
INNER JOIN Ext 
ON ATC.ExtensionGUID = Ext.[App ID] 
END 
ELSE 
 
 
--For LS Central versions <= 14.2 
BEGIN 
 
 
WITH keycolumn 
AS 
(SELECT 
Col.TABLE_NAME 
,Col.COLUMN_NAME 
,Col.COLUMN_NAME + ' = ' + Col.COLUMN_NAME AS JoinOnCol 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab 
,INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col 
WHERE Col.CONSTRAINT_NAME = Tab.CONSTRAINT_NAME 
AND Col.TABLE_NAME = Tab.TABLE_NAME 
AND Tab.CONSTRAINT_TYPE = 'PRIMARY KEY') 
 
 
--Get all columns and metadata for all tables 
-- Extract the table name excluding the company prefix and extension GUID 
-- Extract the Company name / table prefix 
-- Extract the Extension GUID 
-- Mark the Primary Keys 
, 
AllTableColumns 
AS 
(SELECT 
col.TABLE_NAME 
,col.COLUMN_NAME 
,col.DATA_TYPE 
,col.ORDINAL_POSITION 
,col.CHARACTER_MAXIMUM_LENGTH 
,col.NUMERIC_PRECISION 
,col.NUMERIC_SCALE 
,rowcounts.rows CountRows 
,CASE col.TABLE_NAME 
WHEN 'Company' THEN col.TABLE_NAME 
ELSE SUBSTRING(col.TABLE_NAME, CHARINDEX('$', col.TABLE_NAME) + 1, ABS(LEN(col.TABLE_NAME) - CHARINDEX('$', col.TABLE_NAME))) 
END AS BaseTableName 
,CASE col.TABLE_NAME 
WHEN 'Company' THEN 'Company' 
ELSE LEFT(col.TABLE_NAME, ABS(CHARINDEX('$', col.TABLE_NAME) - 1)) 
END AS TablePrefix 
,'N/A' AS ExtensionGUID 
,IIF(col.COLUMN_NAME = keycolumn.COLUMN_NAME, 'TRUE', 'FALSE') Keycol 
FROM [INFORMATION_SCHEMA].[COLUMNS] col 
LEFT JOIN keycolumn 
ON col.TABLE_NAME = keycolumn.TABLE_NAME 
LEFT JOIN (SELECT DISTINCT 
p.rows 
,t.name 
FROM sys.partitions p 
,sys.tables t 
WHERE p.object_id = t.object_id) rowcounts 
ON col.TABLE_NAME = rowcounts.name 
WHERE (col.TABLE_NAME LIKE '_%$_%' 
AND col.TABLE_NAME NOT LIKE '%$VSIFT$%' 
OR col.TABLE_NAME = 'Company') 
GROUP BY col.TABLE_NAME 
,col.COLUMN_NAME 
,col.DATA_TYPE 
,col.ORDINAL_POSITION 
,col.CHARACTER_MAXIMUM_LENGTH 
,col.NUMERIC_PRECISION 
,col.NUMERIC_SCALE 
,rowcounts.rows 
,keycolumn.COLUMN_NAME) 
 
 
SELECT 
[TABLE_NAME] 
,[COLUMN_NAME] 
,[DATA_TYPE] 
,[ORDINAL_POSITION] 
,[CHARACTER_MAXIMUM_LENGTH] 
,[NUMERIC_PRECISION] 
,[NUMERIC_SCALE] 
,[CountRows] 
,BaseTableName 
,[TablePrefix] 
,[ExtensionGUID] 
,[Keycol] 
FROM AllTableColumns 
 
 
 
END