October 26, 2017 at 12:49 pm
Hi All: I have a simple query concatenating some varchar fields with some parentheses as strings:select a_code, a_description, concat(a_code, ' (' , a_description, ')') description from...
and I'm getting the dreaded collation error:
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN" in add operator.
The query worked for a long time (at least a year) but is throwing the error today. After googling around I got it to work again by adding some default collation to the strings: concat(a_code, ' (' collate database_default, a_description, ')' collate database_default)
just not sure why I'm getting the collation error in this scenario? I have many SProcs where I do this type of string concatenation and am concerned they're all going to start breaking unexpectedly.
October 26, 2017 at 3:05 pm
sqlnoobie - Thursday, October 26, 2017 12:49 PMHi All: I have a simple query concatenating some varchar fields with some parentheses as strings:select a_code, a_description, concat(a_code, ' (' , a_description, ')') description from...
and I'm getting the dreaded collation error:Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN" in add operator.
The query worked for a long time (at least a year) but is throwing the error today. After googling around I got it to work again by adding some default collation to the strings: concat(a_code, ' (' collate database_default, a_description, ')' collate database_default)
just not sure why I'm getting the collation error in this scenario? I have many SProcs where I do this type of string concatenation and am concerned they're all going to start breaking unexpectedly.
Someone must have changed the collation at some point, or managed to enter data into a field that won't translate between different collations. It may be that until now, the difference in collation made no difference, but someone entered some kind of oddball character that is throwing things for a loop.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 27, 2017 at 10:50 am
sgmunson - Thursday, October 26, 2017 3:05 PMsqlnoobie - Thursday, October 26, 2017 12:49 PMHi All: I have a simple query concatenating some varchar fields with some parentheses as strings:select a_code, a_description, concat(a_code, ' (' , a_description, ')') description from...
and I'm getting the dreaded collation error:Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN" in add operator.The query worked for a long time (at least a year) but is throwing the error today. After googling around I got it to work again by adding some default collation to the strings:
concat(a_code, ' (' collate database_default, a_description, ')' collate database_default)
just not sure why I'm getting the collation error in this scenario? I have many SProcs where I do this type of string concatenation and am concerned they're all going to start breaking unexpectedly.
Someone must have changed the collation at some point, or managed to enter data into a field that won't translate between different collations. It may be that until now, the difference in collation made no difference, but someone entered some kind of oddball character that is throwing things for a loop.
Thanks Steve: So you think it's something in one of the fields "a_code" or "a_description" that's causing the error, not the strings I'm concatenating with those fields? The default database collation has not changed.
Those fields, however, may have some stray characters in them, as they're refreshed nightly by a Unix-based script that rebuilds the database.
October 27, 2017 at 12:40 pm
Yes, that's what I think, based on what you've said so far. I'd be looking for any characters that are not [a-zA-Z 0-9]. If you find any, then you may well have your culprit.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply