“Conversion failed when converting the varchar value 'NULL' to data type int”

8
votes

When I insert records into a long table,I am getting the error "Conversion failed when converting the varchar value 'NULL' to data type int" How can I determine which column errors out?

The table has many fields and millions of records. Each iteration takes 10 minutes to bomb when I try to insert "NULL" string into integer column somewhere. I thought SQL server can tell me exact name of the column :(

4
sql-server
What do you mean by a long table and could you show the SQL that is causing the problem? - wcm
Where are you seeing this error. Are you running a script from Query Analyzer or is it bubbling up from an executeScalar call in a dotNet app. Once we know this, maybe we can offer some better suggestions about how to solve this. - wcm

4 Answers

14
votes

If the value is truly NULL, there wouldn't be a conversion error. However, you have a string = "NULL" then you would get this error.

What you could do is...

NullIf(YourValueHere, 'NULL')

NullIf returns the value of the first parameter if it is not the same as the second parameter. If the parameters are the same, NullIf will return NULL.

Ex:

Select NullIf('Any Value', 'NULL')

Select NullIf('null','null')

The first will return 'Any Value' and the second will return NULL (not 'null')

4
votes

The fact that it mentions "the varchar value 'NULL'" indicates that you're trying to set the column's value to the string "NULL" instead of the value NULL.

Look in your statement for the word NULL surrounded by quotes. You'll need to remove these quotes.

3
votes

Start commenting stuff out one at a time until it stops bombing. Or, take a look and see which value you're passing is NULL for an int column.

2
votes

Do you need to fix the data or can you just convert nulls to 0's for that insert?

If you can just convert, you could wrap your varchars that are getting converted with an ISNULL function. Just put the following around any values that are inserting into int fields.

ISNULL(col1,0)