June 8, 2003 at 10:56 am
I need to compare the a text from two DBs. I cannot control the collation definition for one of the DBs. Anyone have an easy way to accomplish this.
I tried the "Collation" option, didn't help shown in one of the articles on the site.
One alternative would be to copy the data from the other table to a temp table, defined with the correct collation, do the comparisons ...
But this seems like an ugly alternative, although it probably won't be lots of rows, a few thousand, but they are text fields.
KlK, MCSE
KlK
June 8, 2003 at 8:12 pm
Kevin,
When you say "I tried the "Collation" option, didn't help shown in one of the articles on the site.", can you elaborate? Did you get syntax problems, or did you get incorrect/unexpected results?
Cheers,
- Mark
June 9, 2003 at 4:27 am
whe you say collation option - do you mean this
SELECT T1.*
FROM TableA T1
INNER JOIN OtherDb..TableA T2 ON T1.TxFld LIKE T2.TxFld COLLATE SQL_Latin1_General_CP1_CI_AS
June 9, 2003 at 11:19 am
The collation error is occuring on a
WHERE t1.text <> t2.text Collation ....
It says it can't resolve it.
KlK, MCSE
KlK
June 9, 2003 at 11:40 am
You need to make sure that both the right and the left are using the some collation when comparing. Here is an example that works, because the column on the right is converted to the collation of the left column by using the collate clause.
create table #a (char_set1 varchar(50) collate Latin1_General_CI_AS)
create table #b(char_set2 varchar(50) collate Latin1_General_BIN)
insert into #a values ('collate')
insert into #a values ('collate a')
insert into #b values ('collate')
insert into #b values ('collate b')
select * from #a, #b
where char_set1 <> char_set2 collate Latin1_general_CI_AS
drop table #a, #b
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply