sql server 2012中varchar值的转换溢出了一个int列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24446897/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
The conversion of the varchar value overflowed an int column in sql server 2012
提问by user3782230
I'm getting an error with a query to get data... The error message is: Msg 248, Level 16, State 1, Line 1: The conversion of the varchar value overflowed an int column... I cant resolve this problem, so if any one can help me, thanks in advance, Here is My Sql:
我在查询获取数据时遇到错误...错误消息是：Msg 248, Level 16, State 1, Line 1: The conversion of the varchar value overflowed an int column...我无法解决这个问题，所以如果有人可以帮助我，在此先感谢，这是我的 Sql：
This happens when I inserted 3 new joins in the query, I made them BOLD, otherwise it works perfectly...
当我在查询中插入 3 个新连接时会发生这种情况，我将它们设置为 BOLD，否则它可以完美运行...
SELECT DISTINCT
ACR_ART_ID, DES_TEXTS.TEX_TEXT AS CRITERIA_DES_TEXT,
COALESCE(DES_TEXTS2.TEX_TEXT, ACR_VALUE) AS CRITERIA_VALUE_TEXT,
(DES_TEXTS.TEX_TEXT + ': ' + COALESCE(DES_TEXTS2.TEX_TEXT, ACR_VALUE)) AS CEL_OPIS
FROM
Inventory.dbo.ARTICLE_CRITERIA
LEFT JOIN
Inventory.dbo.DESIGNATIONS AS DESIGNATIONS2 ON DESIGNATIONS2.DES_ID = ACR_KV_DES_ID
LEFT JOIN
Inventory.dbo.DES_TEXTS AS DES_TEXTS2 ON DES_TEXTS2.TEX_ID = DESIGNATIONS2.DES_TEX_ID
LEFT JOIN
Inventory.dbo.CRITERIA ON CRI_ID = ACR_CRI_ID
LEFT JOIN
Inventory.dbo.DESIGNATIONS ON DESIGNATIONS.DES_ID = CRI_DES_ID
LEFT JOIN
Inventory.dbo.DES_TEXTS ON DES_TEXTS.TEX_ID = DESIGNATIONS.DES_TEX_ID
INNER JOIN
Inventory.dbo.ART_LOOKUP al ON ARTICLE_CRITERIA.ACR_ART_ID = al.ARL_SEARCH_NUMBER
AND al.ARL_KIND in (1,3)
INNER JOIN
Inventory.dbo.ARTICLES a ON al.ARL_ART_ID = a.ART_ID and (a.ART_SUP_ID=21 or a.ART_SUP_ID=11091)
INNER JOIN
Inventory.dbo.SUPPLIERS ON SUPPLIERS.SUP_ID = ART_SUP_ID**
WHERE
(DESIGNATIONS.DES_LNG_ID IS NULL OR DESIGNATIONS.DES_LNG_ID = 25)
AND (DESIGNATIONS2.DES_LNG_ID IS NULL OR DESIGNATIONS2.DES_LNG_ID = 25);
回答by Kavan
As Commented by @T I
正如@TI 所评论的那样
The varchar col is implicitly converted to an int and is overflowing (i.e. is >larger than 2,147,483,647). To resolve this try casting the columns to bigint
varchar col 被隐式转换为 int 并且正在溢出（即大于 2,147,483,647）。要解决此问题，请尝试将列转换为 bigint
you can cast column value data type as
您可以将列值数据类型转换为
select cast( max(columnname) AS bigint)+1 from atable ;
But if you will get a varchar than it will fail, with error So it is better to change your DB schema. Use this approach only if you are sure that only numeric string will be present.
但是，如果您将获得一个 varchar，那么它就会失败，并出现错误，因此最好更改您的数据库架构。仅当您确定只会出现数字字符串时才使用此方法。
