Data error - Cannot Select - 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 error - Cannot Select

Author

Topic

cs_bhimavarapu
Starting Member

23 Posts

Posted - 2008-08-27 : 17:44:36

We have a table is updated by a third party application into our local SQL Server 2005 database.

Did any one run into such issues as below. I wasted lot of hours trying to solve this puzzle. Can any one provide insight into it please?

SELECT * from SSISVNDR WHERE VIDELT='Y' AND VIVNO LIKE '12361%'
result: 12361 Y

SELECT * from SSISVNDR WHERE VIDELT='Y' AND VIVNO = '12361'
result: 0 rows

SELECT Len(VIVNO) from SSISVNDR WHERE VIDELT='Y' AND VIVNO like '12361%'
result: 6 (which is length when actual length is 5)

SELECT Len(VIVNO) from SSISVNDR WHERE VIDELT='Y' AND
cast(LTRIM(RTRIM(VIVNO)) as int) = 12361
result: (I ran this to see if I can see something in the error message)

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '12361.' to data type int.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-27 : 17:49:08

How about DATALENGTH instead of LEN?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-08-27 : 19:54:51

There's an extra decimal in 12361., so your numbers are actually strings. try with REPLACE(VIVNO,',','')
Jim

cs_bhimavarapu
Starting Member

23 Posts

Posted - 2008-08-28 : 11:01:46

quote:
Originally posted by jimf

There's an extra decimal in 12361., so your numbers are actually strings. try with REPLACE(VIVNO,',','')
Jim

I tried replacing it didn't help.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-08-28 : 11:50:11

quote:
Originally posted by jimf

There's an extra decimal in 12361., so your numbers are actually strings. try with REPLACE(VIVNO,',','')
Jim

you meant REPLACE(VIVNO,'.','') I think..

cs_bhimavarapu
Starting Member

23 Posts

Posted - 2008-08-28 : 11:59:55

quote:
Originally posted by sakets_2000

quote:
Originally posted by jimf

There's an extra decimal in 12361., so your numbers are actually strings. try with REPLACE(VIVNO,',','')
Jim

you meant REPLACE(VIVNO,'.','') I think..

Yes. You are right.

cs_bhimavarapu
Starting Member

23 Posts

Posted - 2008-08-29 : 12:54:43

Changed the source and the destination columns to nvarchar datatype and it did the trick. This I assume is something to do with the code page issues while using OLEDB.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources