March 18, 2008 at 5:53 pm
-----
Select code, codesubcode from failurecodes where active =1 and productcode in
(select distinct text1 from listitems
where listname = 'Product Names' and listitem in
(select distinct ExText001 from subforms
where category = 'Products' and parentID = 2))
the above fails with
Server: Msg 245, Level 16, State 1, Line 2
Syntax error converting the varchar value 'CD' to a column of data type smallint.
We've narrowed the failure down to text1, which is varchar(50), and the productcode is smallint(2).
If we add a Ltrim(Rtrim(text1) it works fine.
Here's the real kicker. It works OK in a database from a prior backup.
I used Toad to do schema and data compare ... Nada...absolutely nada.
Implicit convert should be OK between varchar and smallint ????
Any thoughts about why this happens in one database and not another with the same schema and data??? would be much appreciated.
:hehe:
We unfortunatley have to know WHY it fails besides just fixing it with the trim.
March 18, 2008 at 9:49 pm
Validate your theory first - does text1 have an instance of 'CD' as a value? If not - that's not it. If it's theere in the "failing table", then chances are it's not in the other version.
If it is there, you might care to check that there is a 'CD' code in your other table. I suspect it's not in the table that's not poppign an error. The productcode is numeric so the assumption would be that the IN list would only have numeric values; any non-numeric values should make it fail.
Simply put - you will need to specifically convert the numeric to a character value, OR, remove the character codes from your IN statement.
Implicit conversions are dangerous to rely on.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 31, 2011 at 4:31 am
Matt Miller (#4) (3/18/2008)
Validate your theory first - does text1 have an instance of 'CD' as a value? If not - that's not it. If it's theere in the "failing table", then chances are it's not in the other version.If it is there, you might care to check that there is a 'CD' code in your other table. I suspect it's not in the table that's not poppign an error. The productcode is numeric so the assumption would be that the IN list would only have numeric values; any non-numeric values should make it fail.
Simply put - you will need to specifically convert the numeric to a character value, OR, remove the character codes from your IN statement.
Implicit conversions are dangerous to rely on.
😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply