pokerplayer23 pokerplayer23 - 4 months ago 27
SQL Question

What line is my error on? Error converting data type nvarchar to numeric

I have a SQL query that has a couple hundred lines of code.

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.


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.

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).

It does give me a line number but it just points to the main Select Statement line (so basically the first line of code)....

Answer

If this returns any records then the problem is your data and not your script:

Select * 
from yourtable 
where isnumeric(yourfield) <> 1