Strange error !!! datatype mismatch when infact all datatypes consistent in my query and in database

Question

Strange error !!! datatype mismatch when infact all datatypes consistent in my query and in database

SQLNW

SSC-Addicted

Points: 433
More actions
December 16, 2010 at 5:01 pm

#232536

Hello gurus
I have made @table variable which collects results by joining 7-8 tables and
# all my datatypes in are consistent with the tables in the database
# there's varchar field that has data like (1.25, No, Yes, 200)
# error type "failed to convert varchar into int",
# Tried cast and convert and forcefully tried to fool sql by
CONVERT( varchar(25) , CONVERT(int , field_trouble))
Then again getting " failed to convert 0.00 to int" , with various permutations only thing changes is like "failed to convert No to int" :crazy:
I am not even trying to convert it into int, even the database has field_trouble has Varchar in database. have showed the error to my superiors and they also could not figure it out.
HELP !!!!!!!!!!!!!!!!!!! Please. Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

The short form, if I had to make a guess without the code/samples, is that you're using a where clause to limit the results to your actual ints.

This will fail because you can't guarantee the order of events in a query. The where doesn't necessarily run before the data is run through the conversion process.

The only way to control that is with a subquery and an OPTION (FORCE ORDER), or to drop the restricted results from the where clause into a different temp table, and then use that afterwards.

If you'd like more help, post your full query.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Craig Farrell (12/16/2010)
The short form, if I had to make a guess without the code/samples, is that you're using a where clause to limit the results to your actual ints.
This will fail because you can't guarantee the order of events in a query. The where doesn't necessarily run before the data is run through the conversion process.
The only way to control that is with a subquery and an OPTION (FORCE ORDER), or to drop the restricted results from the where clause into a different temp table, and then use that afterwards.
If you'd like more help, post your full query.

Have to agree with Craig. We really can't he;p you unless you show us what you are trying to accomplish. We can't see what you see from here.

SQLNW SSC-Addicted Points: 433 More actions · Answer 3

thanks for the reply, here is the query, problem is in value fields, segregated in 3 fields but still getting the same error...

SQLNW SSC-Addicted Points: 433 More actions · Answer 4

thanks for the reply...please see below..problem with value fields, segregated but still same error

DB Dan SSC Veteran Points: 290 More actions · Answer 5

One problem with your SQL is the @results table variable contains 6 columns yet you are attempting to insert 7 (looks like p.prod_id should not be in the select). Not sure if this is causing you any errors?

Are you sure that none of your records where data_type_id = 1 contain non integers in the result column?

SQLNW SSC-Addicted Points: 433 More actions · Answer 6

Thank you for your reply. with prod_id it was just a copy paste mistake.