Im getting this when running a query like this WITHOUT inserting in any table. in SSMS 2008.
select iav.* from ITEM_ATTRIBUTE_VALUE iav where iav.attribute_value != '' and ISNUMERIC(iav.attribute_value) = 0
Why would it do this ?
+1
A:
For a start you will probably want "<>" as opposed to "!=" in SQL Server.
Also, ISNUMERIC will probably have to truncate iav.attribute_value to try and test if it is a number, probably to the length of maximum int which is 2147483647 (signed) - so to 10 characters long - hence the warning.
Edit:
If you look at the spec of ISNUMERIC you can see it looks for anything up to BIGINT.
BIGINT's maximum is 9223372036854775807, which is 19 characters long. So if your attribute_value is greater than 19 characters long it will be truncated in an attempt to test it is a valid number that is usable within SQL Server.
joshcomley
2009-08-14 08:46:07
ok, I tried the same query with ISNUMERIC(LEFT(iav.attribute_value, 5)) = 1 /* not 0 this time */and iav.item_id = 74261;and it returns 1 record and that isitem_id | attribute_value ------- ----------------74261 ٠٠٠٠ ?? whats happening
Binder
2009-08-14 09:16:33
ok that doesnt look as formatted as i typed it in.results wereitem_id = 74261attribute_value = ٠٠٠٠
Binder
2009-08-14 09:17:34
Well it seems clear what is happening: it's found that a row with an item_id of 74261, checked the first five characters of attribute_value are numeric and returned the row! Without knowing more about your setup it's difficult to determine otherwise. Is item_id a primary key? Or does it have a UNIQUE constraint? If so, then of course you'll only get one result!
joshcomley
2009-08-14 09:51:36
no, theres only 1 row that matches item_id 74261 and the full value in that row is ".....", thats the problem !
Binder
2009-08-14 12:23:04
Do you mean the full value is actually just periods - i.e. "....."? The dots don't represent something?
joshcomley
2009-08-14 13:33:25
Yups !! its mind boggling.
Binder
2009-08-15 14:53:26
A:
Is it possible that the length of all fields returned, is greater then 8000 characters?
I think that might cause that error.
Try only returning the columns you need, or just a portion of a column, if it's greater then 8000 characters in length.
Bravax
2009-08-14 08:46:15
A:
The main reason is one must be taking a temporary table(or something same) within the stored procedure and temporary table's datatype and the actual table's datatype must be having a mismatch in size.
Justin
2010-10-06 08:49:32