Data Type Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Data Type Error

Author

Topic

abhwhiz
Starting Member

37 Posts

Posted - 2007-09-10 : 10:48:08

I have a very interesting error occuring my db. I have table with a varchar column. I have the same db on two diffrent instances on differnt servers. Both instances are having sql 2005. The problem is when i run a select query

select * from TestTable where
ID=14858462

without '' it works fine on one server but fails on the other server. On both the servers the ID column is varchar.

select * from TestTable where
ID='14858462'

obviously works fine on both. What could be the reason ?. its really confusing. I couldnt find any hotfix for this in net or BOL, for sql 20005.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 10:51:29

Do you have the same collation?

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-10 : 10:51:41

If the column is a VARCHAR, you should always use the single quotes. Otherwise an implicit conversion is occurring. If the data is numeric, then you should not be using a VARCHAR data type.

- Jeff
http://weblogs.sqlteam.com/JeffS

abhwhiz
Starting Member

37 Posts

Posted - 2007-09-10 : 11:01:33

peso - i have the same collation
smith- dont you think its quite funny? it works both ways, i checked the quoted identifier stuff, but i dunno how this is working! can you think of something, some reason for this trsange behaviour?

sshelper
Posting Yak Master

216 Posts

Posted - 2007-09-10 : 11:35:23

One possible reason is that you have a value in your ID column that is not numeric. You query without the quotes will fail because it will try to convert that non-numeric ID into a numeric value, which will generate an error.

SQL Server Helper
http://www.sql-server-helper.com

mandm
Posting Yak Master

120 Posts

Posted - 2007-09-10 : 14:49:45

Are both instances running under the same compatiblity level?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-10 : 15:18:04

quote:
Originally posted by sshelper

One possible reason is that you have a value in your ID column that is not numeric. You query without the quotes will fail because it will try to convert that non-numeric ID into a numeric value, which will generate an error.

SQL Server Helper
http://www.sql-server-helper.com

Good call, I bet that's it.

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 15:20:30

Or "out if INT"? Some value larger than 2,147,483,647?

E 12°55'05.25"
N 56°04'39.16"

abhwhiz
Starting Member

37 Posts

Posted - 2007-09-11 : 23:40:24

A bit late to update..
It turned out to be a data issue, when i restored the db (luckily we have this option) from another db, everything looked fine.
mandm- Both have the same comaptibility.
Peso- I didnt get any "overflowing" values, i checked it.
SShelper- Your tip did the trick for me, many thanks.

Thanks to all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources