Select query issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select query issue

Author

Topic

mshsilver
Posting Yak Master

112 Posts

Posted - 2012-01-20 : 10:34:34

Hi,

I have the following SQL select query and one one server it works fine but on another i get an error. I am sure the answer is to add this "COLLATE Latin1_General_CI_AS NOT NULL" to part of the query but i am not sure where and why. Could somone point this out and give me an idea why. Many thanks.

Error:

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.


select u.contactid, u.wce_alias, u.wce_uid, u.wce_displayname, u.wce_locked, c.emailaddress from wces_users  as u 
join DBOSS_SITE..wce_contact as c on u.contactid = c.uniqueid  where ((u.wce_locked is null or u.wce_locked = '' and not u.contactid is null) and (not c.emailaddress is null or c.emailaddress =''))

bobmcclellan
Starting Member

46 Posts

Posted - 2012-01-20 : 10:44:34

I had this in my notes from a similar situation...
Planned on researching this further but never got around to it...
It worked and allowed the query to execute... hopefully this example
helps you...

USE COLLATE DATABASE_DEFAULT
Select r.Unit#
, RmiClass = r.Class
, r.SubClass
From #rmiUnits r

left join eis.dbo.Units u on r.unit# COLLATE DATABASE_DEFAULT = u.Unit# COLLATE DATABASE_DEFAULT

hth,
..bob

mshsilver
Posting Yak Master

112 Posts

Posted - 2012-01-20 : 11:20:28

Thanks for the help i will try that now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources