November 6, 2012 at 2:17 pm
Hi all, I've been trying to solve the collating sequence issue for one of my update statement but couldn't get it working. Here it is:
refno, REFNO = int
track1,TRACK1 = varchar
jobno = int
update m1
set m1.TRACK1 = STUFF(m1.TRACK1, LEN(m1.TRACK1) - LEN(c1.track1) - 14, LEN(c1.track1), c1.track11)
from job30 m1
inner join
volume1 c1
on m1.REFNO = c1.refno
WHERE m1.JOBNO = 73
when I execute the statement, I got this error message:
Msg 468, Level 16, State 9, Line 13
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Turkish_CI_AS" in the stuff operation.
but when I change the collating sequence like this:
on m1.REFNO = c1.refno collate database_default
I got this error message:
Msg 447, Level 16, State 0, Line 1
Expression type int is invalid for COLLATE clause.
How can I resolve it?
thanks for all the help
November 6, 2012 at 2:52 pm
I got it working
set m1.TRACK1 = STUFF(m1.TRACK1 collate database_default , LEN(m1.TRACK1 collate database_default) - LEN(c1.track1 collate database_default) - 14, LEN(c1.track1 collate database_default), c1.track1 collate database_default)
November 6, 2012 at 6:44 pm
You probably only needed to add the COLLATE to the 1st and/or 4th parameters to STUFF.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply