I have a SQL query that has a couple hundred lines of code.
我有一个SQL查询，它有几百行代码。
The query use to work fine but some data has changed in the queries underlying tables.
查询通常工作得很好，但是一些数据在查询基础表中发生了变化。
I am now getting the following error message
我现在得到以下错误消息
Error converting data type nvarchar to numeric.
将数据类型nvarchar转换为数值的错误。
How can I determine which one of the hundreds of lines of code in the script is causing the error?
如何确定脚本中数百行代码中的哪一行导致了错误?
I have several dozen lines of code that could potentially throw the error.
我有几十行代码可能会抛出错误。
Im thinking I'll just have to comment out all the lines I suspect might be causing the issue and then uncomment them 1 by 1 until I find the one(s) causing the data type error.
我想我只需要注释掉我怀疑可能引起这个问题的所有行，然后将它们取消注释1，直到我找到导致数据类型错误的那个。
Is this just a limitation of SQL Server 2014 where it just tells you "Hey there's an error somewhere in your hundreds of lines of code" as opposed to giving you the actual line number of the error (as it does with other error types).
这仅仅是SQL Server 2014的一个限制吗?它只是告诉您“嘿，在您的数百行代码中有一个错误”，而不是给出错误的实际行号(与其他错误类型一样)。
It does give me a line number but it just points to the main Select Statement line (so basically the first line of code)....
它给我一个行号但它只是指出主要的Select语句行(基本上第一行代码)....
2 个解决方案
#1
2
If this returns any records then the problem is your data and not your script:
如果它返回任何记录，那么问题是您的数据，而不是您的脚本:
Select *
from yourtable
where isnumeric(yourfield) <> 1
#2
1
It's hard to know exactly what is causing the error without the underlying code. I would advise checking if any of the table data contains invalid numerals, and if this is not the case then run the same test on intermediary data (data that has undergone one or more transformations by queries).
如果没有底层代码，很难确切地知道是什么导致了错误。我建议检查任何表数据是否包含无效的数字，如果不是这样，那么在中间数据上运行相同的测试(通过查询进行了一个或多个转换的数据)。
SELECT * AS invalidNumerics
FROM table
WHERE isNumeric(column) <> 1
#1
2
If this returns any records then the problem is your data and not your script:
如果它返回任何记录，那么问题是您的数据，而不是您的脚本:
Select *
from yourtable
where isnumeric(yourfield) <> 1
#2
1
It's hard to know exactly what is causing the error without the underlying code. I would advise checking if any of the table data contains invalid numerals, and if this is not the case then run the same test on intermediary data (data that has undergone one or more transformations by queries).
如果没有底层代码，很难确切地知道是什么导致了错误。我建议检查任何表数据是否包含无效的数字，如果不是这样，那么在中间数据上运行相同的测试(通过查询进行了一个或多个转换的数据)。
SELECT * AS invalidNumerics
FROM table
WHERE isNumeric(column) <> 1