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