0

ERROR MESSAGE : Msg 8152, Level 16, State 14, Line 1 String or binary data would be truncated. The statement has been terminated.

HI, do you have idea on this error? what is the problem with my script, My script are working without UPDATE [DWQA].[DWUTIL].[RULE] set sql_code = ' but when I add this to my script, the error appears. What this error stands for ? my script are working but when i used UPDATE command it gives me error. Please help

 UPDATE [DWQA].[DWUTIL].[RULE]
    set sql_code = '
    ;with abc AS (
                   SELECT * FROM(SELECT  
                          CONVERT(varchar,BUSINESSDATE,12)     AS BARTER_BUSINESSDATE,
                          B.SITECODE       AS BARTER_SITECODE,
                          C.STORE_CODE     AS BARTER_STORE_CODE,
                          inventoryDocId   AS BARTER_INVENTORYDOCID,
                          terminalId       AS BARTER_TERMINALID,
                          transId          AS BARTER_TRANSID,
                          lineNumber       AS BARTER_LINENUMBER,
                          parentLineNumber AS BARTER_PARENTLINENUMBER,
                          B.productCode    AS BARTER_PRODUCT_CODE,  
                          CAST(ltrim(rtrim(PRODUCT.SKU)) AS INT) AS BARTER_SKU,
                          quantity         AS BARTER_QUANTITY,
                          b.actualquantity AS BARTER_ACTUALQUANTITY,
                          price            AS BARTER_PRICE,
                          b.subTotal       AS BARTER_SUBTOTAL
                   FROM DWSTAGE.POSDOCDETAIL B WITH(NOLOCK)
                   LEFT JOIN [DWMARTS].[DIM_SITE] C WITH(NOLOCK) ON B.SITECODE = C.SITECODE
                   LEFT JOIN [DWMARTS].[DIM_PRODUCT_BARTER] PRODUCT WITH(NOLOCK)          
                   ON B.productCode = PRODUCT.PRODUCTCODE AND B.SITECODE = PRODUCT.SITECODE
                   WHERE BUSINESSDATE= ''2021-05-30 00:00:00.000'' 
                                                       
                   )BARTER
                   LEFT JOIN(
                   SELECT  
                          B.TERMINALID      AS JDA_BARTER_HQ_TERMINAL,
                          A.CSSTOR          AS JDA_CSSTOR,
                          A.CSDATE          AS JDA_CSDATE,
                          A.CSREG           AS JDA_CSREG,
                          A.CSROLL          AS JDA_CSROLL,
                          A.CSTRAN          AS JDA_CSTRANS,
                          A.CSSEQ           AS JDA_CSSEQ,
                          A.CSDTYP          AS JDA_CSDTYP,
                          A.CSSKU           AS JDA_CSSKU,
                          A.CSRETL          AS JDA_CSRETL,
                          A.CSQTY           AS JDA_CSQTY,
                          A.CSEXPR          AS JDA_CSEXPR
                   FROM DWSTAGE.CSHDET A WITH(NOLOCK)
                   LEFT JOIN ( SELECT C.STORE_CODE AS STORE,B.* FROM DWSTAGE.TERMINAL_BARTER_HQ B WITH(NOLOCK)
                                LEFT JOIN [DWMARTS].[DIM_SITE] C WITH(NOLOCK) ON B.SITEID = LTRIM(RTRIM(C.SITECODE)) 
                            )B ON A.CSREG = CAST(ISNULL(LTRIM(RTRIM(CAST(B.REMARK AS VARCHAR))),0) AS INT) AND B.STORE = A.CSSTOR
                   ) JDA
                   
                   ON    JDA.JDA_CSSTOR             = BARTER.BARTER_STORE_CODE
                   AND   JDA.JDA_CSSKU              = BARTER.BARTER_SKU
                   AND   JDA.JDA_BARTER_HQ_TERMINAL = BARTER.BARTER_TERMINALID
                   AND   JDA.JDA_CSDATE             = BARTER.BARTER_BUSINESSDATE
                   AND   JDA.JDA_CSTRANS            = BARTER.BARTER_TRANSID
                   AND   JDA.JDA_CSSEQ              = BARTER.BARTER_LINENUMBER
    
    )
    select 
    BARTER_STORE_CODE as [sku]  
    ,BARTER_PRODUCT_CODE as [cost]  
    ,BARTER_SKU as [price]  
    ,BARTER_QUANTITY as [selling_uom]  
    ,BARTER_PRICE as [buying_uom]  
    ,BARTER_SUBTOTAL as [dept]  
    ,JDA_CSSKU as [subdept]  
    ,JDA_CSRETL as [class]  
    ,JDA_CSQTY as [subclass]  
    ,JDA_CSEXPR as [setcode]  
    ,JDA_CSTRANS as [upc]  
    ,BARTER_TRANSID as [vendor]  
    ,''0.0000'' as [allowances]  
    from abc where JDA_CSTRANS IS  NULL
    '
    WHERE RULE_ID = 209
Amit11794
  • 148
  • 1
  • 2
  • 15
Efren Caballes
  • 3
  • 5
  • 1
    Does this answer your question? [string-or-binary-data-would-be-truncated](https://stackoverflow.com/questions/6388756/sql-server-string-or-binary-data-would-be-truncated) – Stu May 30 '21 at 13:53
  • Hi stu - I cant get useful information for that topic. I try their suggestion but wont work for me, do you have any idea for this ? , Please help – Efren Caballes May 30 '21 at 14:02
  • what type is the column `[DWQA].[DWUTIL].[RULE]` especially what is the length of that column ... The error says, the string you are trying to put into that column is longer than what the column is defined to hold ... – derpirscher May 30 '21 at 14:12
  • Share the schema of your `Rule` table, specifically the `sql_code` column – Stu May 30 '21 at 14:19

0 Answers0