collation issues

hello,
i have a sql script/batch that i run against a db every day on my
workstation w/o a problem. recently i tried running it on my laptop
(replication of usual db)
i get an error on one select statement. the statement is a join b/t two
tables, on a field of type varchar(256) . i've tried both like and = as the
operator. one of the fields may actually be of type char(16) -- regardless,
this join always works on my desktop.
on my laptop, the text of the error message is:
Cannot resolve collation conflict for equal to operation
(or when i use the like operator)
Cannot resolve collation conflict for like operation
guessing this has to do w/ some collation setting for my sql server instance
on my laptop, but don't know. also, the sql server on my laptop is
development edition, while on my desktop it's enterprise edition -- don't
know if that matters
thanks for any help
matthewIt sounds as if the collation on the two columns is different. Use QA to scr
ipt
the Create Table statement to the clipboard and paste it into a message. The
re
is a way to coerce one collation into another if you know the collations on
the
columns.
Thomas
"matthew c. harad" <matthewcharad@.discussions.microsoft.com> wrote in messag
e
news:51C9A1FA-0DD3-44C8-AD49-309B5637CC24@.microsoft.com...
> hello,
> i have a sql script/batch that i run against a db every day on my
> workstation w/o a problem. recently i tried running it on my laptop
> (replication of usual db)
> i get an error on one select statement. the statement is a join b/t two
> tables, on a field of type varchar(256) . i've tried both like and = as t
he
> operator. one of the fields may actually be of type char(16) -- regardles
s,
> this join always works on my desktop.
> on my laptop, the text of the error message is:
> Cannot resolve collation conflict for equal to operation
> (or when i use the like operator)
> Cannot resolve collation conflict for like operation
> guessing this has to do w/ some collation setting for my sql server instan
ce
> on my laptop, but don't know. also, the sql server on my laptop is
> development edition, while on my desktop it's enterprise edition -- don't
> know if that matters
> thanks for any help
> matthew|||Check the collation_name of both columns from information_schema.columns and
use COLLATE to force the collations to be the same.
Example:
use northwind
go
create table t1 (
c1 char(10) collate SQL_Latin1_General_CP1_CI_AS
)
go
create table t2 (
c1 char(10) collate SQL_Latin1_General_CP1_CS_AS
)
go
insert into t1 values('microsoft')
insert into t2 values('Microsoft')
go
-- will give an error
select
*
from
t1 inner join t2
on t1.c1 = t2.c1
go
select
*
from
t1 inner join t2
on t1.c1 = t2.c1 collate SQL_Latin1_General_CP1_CI_AS
go
drop table t1, t2
go
AMB
"matthew c. harad" wrote:

> hello,
> i have a sql script/batch that i run against a db every day on my
> workstation w/o a problem. recently i tried running it on my laptop
> (replication of usual db)
> i get an error on one select statement. the statement is a join b/t two
> tables, on a field of type varchar(256) . i've tried both like and = as t
he
> operator. one of the fields may actually be of type char(16) -- regardles
s,
> this join always works on my desktop.
> on my laptop, the text of the error message is:
> Cannot resolve collation conflict for equal to operation
> (or when i use the like operator)
> Cannot resolve collation conflict for like operation
> guessing this has to do w/ some collation setting for my sql server instan
ce
> on my laptop, but don't know. also, the sql server on my laptop is
> development edition, while on my desktop it's enterprise edition -- don't
> know if that matters
> thanks for any help
> matthew