错误含义 #

这个错误（Msg 8152, Level 16, State 14）的核心意思是：你试图写入某个字段的字符串或二进制数据长度超过了该字段定义的最大长度，导致数据被强制截断，SQL Server为了保证数据完整性，直接终止了这条语句的执行。

为什么只有UPDATE语句触发错误？ #

你的脚本在不执行这条UPDATE时能正常运行，说明其他操作并没有往[DWQA].[DWUTIL].[RULE].sql_code这个字段写入这么长的内容。而你现在要更新的SQL字符串非常冗长，远远超出了该字段当前的长度上限，所以只有执行这条UPDATE操作时才会触发截断报错。

排查与解决步骤 #

1. 先确认字段的长度限制
   执行下面的SQL语句，查看sql_code字段的具体类型和长度：

   SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_NAME = 'RULE' 
     AND TABLE_SCHEMA = 'DWUTIL' 
     AND TABLE_CATALOG = 'DWQA'
     AND COLUMN_NAME = 'sql_code'
   

   你会发现这个字段的CHARACTER_MAXIMUM_LENGTH（比如varchar(255)）远小于你要写入的SQL语句长度。

2. 修改字段以支持长文本
   如果业务确实需要存储这么长的SQL语句，建议将字段修改为支持大文本的类型，比如varchar(max)或者nvarchar(max)（如果需要支持Unicode字符）：

   ALTER TABLE [DWQA].[DWUTIL].[RULE]
   ALTER COLUMN sql_code VARCHAR(MAX) NULL; -- 可根据实际需求调整是否允许NULL值
   

   注意：修改字段前最好确认该字段没有其他依赖限制，并且做好数据备份。

3. 可选：精简SQL语句（可行性较低）
   如果不想修改字段类型，你可以尝试精简要写入的SQL——比如去掉不必要的空格、简化表/列别名，或者将部分逻辑拆分为存储过程，但这种方法对于复杂SQL来说实操性不强。

你提供的报错UPDATE语句 #

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

内容的提问来源于stack exchange，提问作者Efren Caballes