I understand the error and how to fix I am just interested in finding the field to fix. Let me start from the top. I am running a scheduled task daily which executes a process that at some points runs some sprocs in sql which run insert statements. Unfortunately after checking my logs I am getting the error in question and therefore my sprocs arent working. I could update every field to a bigger length and this would probably fix it but id rather not. Is there any way of knowing (without manually checking as there are many fields and thousands of rows) the field that contains the value that is too big for the field it is being inserted into?
我理解错误以及如何修复我只是想找到要修复的字段。让我从顶部开始。我每天运行一个计划任务，执行一个进程，在某些点运行一些运行插入语句的sql中的sprocs。不幸的是，在检查我的日志后，我收到了有问题的错误，因此我的sprocs无法正常工作。我可以将每个字段更新到更大的长度，这可能会修复它但id却不是。是否有任何方法可以知道（没有手动检查，因为有很多字段和数千行）包含对于插入的字段来说太大的值的字段？
1 个解决方案
#1
9
Import the data into a new table using VARCHAR(MAX) as the datatype for the columns. Then you can use DATALENGTH to get the maximum size of each column.
使用VARCHAR（MAX）作为列的数据类型将数据导入新表。然后，您可以使用DATALENGTH获取每列的最大大小。
SELECT MAX(DATALENGTH(col1)) AS col1, MAX(DATALENGTH(col2)) AS col2, etc.
FROM newTable
This will tell you which column(s) exceed the size of your column(s).
这将告诉您哪些列超出了列的大小。
#1
9
Import the data into a new table using VARCHAR(MAX) as the datatype for the columns. Then you can use DATALENGTH to get the maximum size of each column.
使用VARCHAR（MAX）作为列的数据类型将数据导入新表。然后，您可以使用DATALENGTH获取每列的最大大小。
SELECT MAX(DATALENGTH(col1)) AS col1, MAX(DATALENGTH(col2)) AS col2, etc.
FROM newTable
This will tell you which column(s) exceed the size of your column(s).
这将告诉您哪些列超出了列的大小。