troubleshooting Question

Collation Error

Avatar of red_75116
red_75116Flag for United States of America asked on
Microsoft SQL Server 2005
6 Comments1 Solution272 ViewsLast Modified:
I am trying to run a query for an integration project.  The query ran fine on the old version of the database, but we recently upgraded the program to the new Unicode dtabase version.  As far as I can tell from the properties windows, both the old datatbase and the new one have the same collation type, but when I run the query, I get Msg 451, Level 16, State 1, Line 2
Cannot resolve collation conflict for column 11 in SELECT statement.

Here is the query.  The portion cv
Select
hr.res_id
,hr.fullname 
,hr.usr_id
,jt.descr50
,hr.comp
,hr.job_title
,hr.loc
,(Select cmp_fadd1 from cicmpy where hr.comp = administration and cmp_type = 'D') as address
,(Select cmp_tel from cicmpy where hr.comp = administration and cmp_type = 'D') as phone
,(Select cmp_fax from cicmpy where hr.comp = administration and cmp_type = 'D') as fax

--  THIS PART IS CAUSING THE PROBLEM
,(Select cmp_fcity + ', ' + statecode + ' ' + cmp_fpc  from cicmpy where hr.comp  = 'administration'  and cmp_type = 'D') as citystate
--------
,hr.ldatindienst
,hr.emp_stat
,hr.costcenter
,a.itemcode
,i.userYesNo_01
,a.enddate 
From humres as hr
left join (absences a inner join items i on i.itemcode = a.itemcode and i.userYesNo_01 = 1) on hr.res_id = a.empid and a.type = 86
left join hrjbtl as jt  on hr.job_title = jt.job_title
Order by hr.res_id
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros