Comparing nvarchar with string has started generating weird conversion error

Question

Comparing nvarchar with string has started generating weird conversion error

shaunos

SSC Enthusiast

Points: 175
More actions
February 6, 2022 at 12:35 pm

#3980949

Hi,
We are running SQLSERVER 2016.
We have a process that has been running fine for years that this month has run into an issue.
There is a simple select statement that links 3 tables.
SELECT count(*)
FROM TABLE_A A
INNER JOIN TABLE_B A
ON A.X = B.X
INNER JOIN TABLE_C
ON B.Y = C.Y
This select still runs fine and returns the records as expected.
Normally there is a where clause on the end:
WHERE C.NVACHAR_FIELD <> 'SomeTextString'
This has worked without issue for years but this month has failed throwing:
'Error converting data type varchar to numeric.'
The where clause is comparing a nvarchar field with a simple three character text string, no numerics to be seen...
There are no numeric values in C.NVACHAR_FIELD
The odd thing is that the error message is displayed no matter what the where clause is or which field it relates to even something like where C.cust_ID = 123
I have tried re-creating the table under a new name and filling with data from the original table just in case there was something corrupted in the table (grasping I know) but same error is displayed.
Has anyone seen anything behaviour like this before?
Cheers..
Shaunos

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2169 More actions · Answer 1

shaunos wrote:

... has failed throwing:
'Error converting data type varchar to numeric.'
The where clause is comparing a nvarchar field with a simple three character text string, no numerics to be seen...
Shaunos

Cheers Shaunos. The error says the conversion is varchar to numeric while the column type for comparison is nvarchar. To make sure the comparison is type safe you could try adding the unicode prefix (N) to the string

WHERE C.NVACHAR_FIELD <> N'SomeTextString'

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

shaunos SSC Enthusiast Points: 175 More actions · Answer 2

Thanks for the suggestion Steve but no joy I'm afraid, still the same error..

Cheers,

~Shaunos

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 3

shaunos wrote:

Has anyone seen anything behaviour like this before?

Yes... and every time someone claims that something has been running for years and no one has made a change, someone has changed something somewhere. The only way that we could help you find it is for you to post all of the DDL involved, the DML being used without modification (the code you posted as an example is actually incorrect even as an example), and all of the data. You're probably not going to do that (and I don't blame you there in this case) and so my recommendation would to to verify everything hand over fist, skipping nothing and making no assumptions because a change somewhere was made and it could be in the data itself.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

irgp SSC Journeyman Points: 80 More actions · Answer 4

If you look at the code sample you said didn't work you would find that it couldn't even run. If you provide sample data in create table statements you will probably sort out the problem yourself

shaunos SSC Enthusiast Points: 175 More actions · Answer 5

Hi Jeff,

Thanks for your reply and I now see the typo in the table alias.... duh!

As it happens, I think we have just figured out the issue which seems to be data driven.

One of the joins in the statement does a cast(Field as decimal(15,0)), but this month one of the values for Field in the imported table has garbage in it.

Interestingly the select statement returns all the rows without error , it is only when we add a where clause that it throws the error for the failed cast statement.. this is what was throwing us in the wrong direction..

Cheers,

~Shaunos.

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 6

It always happens that way with such "no one made any changes" such things. 😀

It would be nice if you could post the actual code you're talking about so we could see for ourselves.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246974 More actions · Answer 7

Jeff Moden wrote:

It always happens that way with such "no one made any changes" such things. 😀
It would be nice if you could post the actual code you're talking about so we could see for ourselves.

Ideally, along with sample data which allows us to recreate this 'odd thing'.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 8

That is also one of the problems with creating a 'sample' query that isn't the same as the actual query being executed. If you had posted the original query as-is (maybe obfuscating table/column names) - we would have seen that cast and immediately looked to that as part of the issue.

The reason why adding the WHERE clause causes the error to be generated - but doesn't without the WHERE is because SQL Server is probably using a different index and different order of operations. Without the WHERE it never hits the 'bad' row...

There are several ways around the issue, but the best way is to fix the system so it doesn't allow garbage data. Since that is going to be an uphill climb and probably out of your control - the next best option is a computed column, persisted and indexed using: TRY_CAST(column AS decimal(15,0)). Now you can join to the computed column and avoid the issue because any values that cannot be cast will be NULL and eliminated from the join.

The next best option would be to use TRY_CAST in the join - and again, anything that cannot be cast will be excluded.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

shaunos SSC Enthusiast Points: 175 More actions · Answer 9

Hi Jeff,

Yes in hindsight I should have shown the exact select statement which would have shown the cast as part of the join. I guess I was thrown by the fact that the select statement on its own was working...

The try_cast does remove the error but that just hides another issue in that the third party has delivered bad data to us.

In fairness, as I mentioned before, these files have been fine for years except for this month but yes we will have to add some more defensive checks to the data once loaded to cover this potential crack going forward.

Everyday's a school day!! 🙂

Thanks again..

~Shaun.

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 10

shaunos wrote:

In fairness, as I mentioned before, these files have been fine for years except for this month but yes we will have to add some more defensive checks to the data once loaded to cover this potential crack going forward.
Everyday's a school day!! 🙂

You said a mouthful there. I don't believe there's a person on this thread that hasn't been burned by changes in data that are beyond our control and I believe you've just stated the unspoken mantra for us all. 😀 Welcome to the "Wanna Bet?" club. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)