Collation Error:Help - 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)

Collation Error:Help

Author

Topic

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-10 : 12:06:54

I got this error:

Cannot resolve the collation conflict between "Latin1_General_BIN" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

SELECT

analyst_lastname, analyst_firstname,
time_spent, AHD.View_Request.type, AHD.View_Request.description AS Ticket_Desc, team, (AHD.View_Request.close_date - AHD.View_Request.open_date) as close_secs,

AHD.View_Request.active_flag, customer.c_nx_string1 AS SDCust_BusUnit, Customerps.BUSINESS_UNIT AS PSCust_BusUnit,

CASE WHEN Customerps.Business_Unit IS NULL THEN Customer.c_nx_string1 ELSE Customerps.Business_Unit END AS BU, CONVERT(CHAR(8),

DATEADD(ss, AHD.View_Request.time_spent_sum, 0), 108) AS TotalTicket_Time, Assigneeps.DEPTNAME, Assigneeps.DEPTID,

Assigneeps.BUSINESS_UNIT, AHD.View_Request.status_name, AHD.View_Request.group_name, DATEADD(ss, AHD.View_Request.last_mod_dt,

CONVERT(DATETIME, '1969-12-31 20:00:00', 101)) AS moddate, DATEADD(ss, AHD.View_Request.close_date, CONVERT(DATETIME,

'1969-12-31 20:00:00', 102)) AS closedate, AHD.View_Request.close_date, DATEADD(ss, AHD.View_Request.open_date, CONVERT(DATETIME,

'1969-12-31 20:00:00', 102)) AS opendate,

AHD.View_Request.close_date - AHD.View_Request.open_date AS Time_to_Close,

CASE Assigneeps.W_HOME_DEPARTMENT when '001510' then '510'

when '001811' then '811'

when '001824' then '824'

when '001830' then '830'

when '001833' then '833'

when '001835' then '835'

when '001836' then '836'

when '001837' then '837'

when '001838' then '838'

when '001839' then '839'

when '001840' then '840'

when '001841' then '841'

when '001842' then '842'

when '001843' then '843'

when '001844' then '844'

when '001853' then '853'

when '010999' then '999'

when '101520' then '520'

when '103520' then '520'

when '104520' then '520'

when '106520' then '520'

when '110520' then '520'

when '112520' then '520'

when '114520' then '520'

when '121520' then '520'

when '122520' then '520'

when '123520' then '520'

when '123550' then '550'

when '123560' then '560'

when '131356' then '836'

when '131357' then '836'

when '131520' then '520'

when '133450' then '450'

when '133520' then '520'

when '133550' then '550'

when '138520' then '520'

when '142450' then '450'

when '142520' then '520'

when '149451' then '451'

when '149452' then '452'

when '149550' then '550'

when '151450' then '450'

when '151520' then '520'

when '151550' then '550'

when '169520' then '520'

when '204520' then '520'

when '205520' then '520'

when '206520' then '520'

when '211520' then '520'

when '213520' then '520'

when '216520' then '520'

when '217520' then '520'

when '218520' then '520'

when '219520' then '520'

when '221520' then '520'

when '225520' then '520'

when '230520' then '520'

when '231356' then '840'

when '231357' then '840'

when '231450' then '450'

when '231520' then '520'

when '232520' then '520'

when '33A510' then '510'

when '450312' then '450'

when '450331' then '450'

when '450421' then '450'

when '451491' then '450'

when '452491' then '450'

when '45033A' then '450'

when '520011' then '520'

when '520331' then '520'

when '520381' then '520'

when '520421' then '520'

when '520511' then '520'

when '536520' then '520'

when '550061' then '550'

when '550231' then '550'

when '550331' then '550'

when '550491' then '550'

when '551450' then '450'

when 'A33450' then '450'

when 'A42450' then '450'

when 'A49451' then '451'

when 'S73A50' then '833'

when '835010' then '835'

when '842010' then '842'

when '843010' then '843'

when '999100' then '999'

when 'S73A25' then 'A25' ELSE 'Blank' END AS Budget_ID

FROM AHD.ctct AS GroupName LEFT OUTER JOIN

AHD.grpmem ON GroupName.id = AHD.grpmem.group_id LEFT OUTER JOIN

sdreport.Activity_Rpt_Act_log INNER JOIN

AHD.View_Request ON call_req_id = AHD.View_Request.persid ON

AHD.grpmem.member = analyst LEFT OUTER JOIN

AHD.ctct AS assignee ON analyst = assignee.id AND AHD.View_Request.assignee = assignee.id LEFT OUTER JOIN

AHD.ctct AS customer ON AHD.View_Request.customer = customer.id LEFT OUTER JOIN

dbo.ps_w_srvcdesk_alloc AS Customerps ON customer.c_nx_string6 = Customerps.EMPLID LEFT OUTER JOIN

dbo.ps_w_srvcdesk_alloc AS Assigneeps ON assignee.c_nx_string6 = Assigneeps.EMPLID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-10 : 12:08:54

Cast the COLLATIONS of columns to be same using COLLATE clause.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-10 : 12:11:39

I tried casting with Collate clause but i get this error:

Expression type int is invalid for COLLATE clause.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-10 : 12:13:53

Do it only for string fields

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-10 : 12:18:27

Still its throwing a error:

Expression type int is invalid for COLLATE clause.

My head is blowing up.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-10 : 12:38:29

But error looks as if you're trying it on integer field.Are you sure you're doing this for text field alone?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-10 : 13:32:54

It's still not working.

What are the things I have to consider while doing COLLATE SQL_Latin1_General_CP1_CS_AS in ON clause?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-10 : 13:56:42

Have you narrowed down which part of the query is giving the error? If not, start removing stuff from the query until you've narrowed it down, then let us know where it is and also post the DDL for those tables.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources