collate problem please resolve

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

General SQL Server Forums

New to SQL Server Administration

collate problem please resolve

Author

Topic

rajadadi
Starting Member

30 Posts

Posted - 2010-05-07 : 01:19:30

use invdb

SELECT DISTINCT

st.id AS seq_id, mst.machine_id, COALESCE (ppt.product_name, st.sname, N'') AS product, dbo.machineenvironment.variable_value AS company,

COALESCE (ppt.manufacturer, m.name, N'') AS manufacturer, COALESCE (ppt.product_model_version, st.version, N'') AS version,

COALESCE (ppt.product_name, st.sname, N'') AS description, st.sname AS orig_product, st.version AS orig_version, mst.base_dir AS location,

'0' AS app_size, dbo.machine.insert_time, dbo.mac_id.mac, '' AS app_category, '' AS app_type, CASE WHEN (COALESCE (ppt.product_model_version,

st.version, '') = '') THEN COALESCE (ppt.product_name, st.sname, '') ELSE COALESCE (ppt.product_name, st.sname, '')

+ ':' + COALESCE (ppt.product_model_version, st.version, '')

END COLLATE SQL_Latin1_General_CP1_CS_AS AS CMDBName,

CASE WHEN (COALESCE (ppt.product_model_version, st.version, '') = '')

THEN 'ProductName' ELSE 'ProductName:Version' END AS CMDBNameFormat, '0' AS CMDBToken /*, COALESCE (ppt.patchinstanceid, N'')

AS DictionaryID*/, COALESCE (ppt.product_categorization_tier_1, N'') AS category, COALESCE (ppt.product_categorization_tier_2, N'') AS type

-- COALESCE (ppt.product_categorization_tier_3, N'') AS item, mst.last_modified_time

FROM dbo.software_title AS st LEFT OUTER JOIN

dbo.pdl_productdictpatch_tbl AS ppt ON st.guid = ppt.guid INNER JOIN

dbo.manufacturers AS m ON st.manufacturer_id = m.id INNER JOIN

dbo.machine_software_title AS mst ON st.id = mst.swtitle_id INNER JOIN

dbo.machine LEFT OUTER JOIN

dbo.machineenvironment ON dbo.machineenvironment.machine_id = dbo.machine.id AND dbo.machineenvironment.name LIKE 'Company' ON

mst.machine_id = dbo.machine.id INNER JOIN

dbo.mac_id ON mst.machine_id = dbo.mac_id.id

UNION ALL

SELECT DISTINCT

ia.seq_id, ia.machine_id, ia.product, dbo.machineenvironment.variable_value AS company, ia.mname AS manufacturer, ia.version, ia.description,

ia.product AS orig_product, ia.version AS orig_version, ia.location, ia.app_size_bytes AS app_size, dbo.machine.insert_time, dbo.mac_id.mac,

ia.app_category AS app_category, ia.app_type AS app_type, CASE WHEN (ia.version IS NULL OR

ia.version = '') THEN ia. product + ':' + ia.location WHEN (ia.location IS NULL OR

ia.location = '')

THEN ia. product + ':' + ia.version ELSE ia. product + ':' + ia.version + ':' + ia.location END COLLATE Latin1_General_CP1_CS_AS

AS CMDBName, CASE WHEN (ia.version IS NULL OR

ia.version = '') THEN 'ProductName:Location' WHEN (ia.location IS NULL OR

ia.location = '') THEN 'ProductName:Version' ELSE 'ProductName:Version:InstallLocation' END AS CMDBNameFormat,

CASE WHEN (ia. product IS NOT NULL AND ia. product != '' AND ia.version IS NOT NULL AND ia.version != '' AND ia.location IS NOT NULL AND

ia.location != '') THEN ia. product + ':' + ia.version + ':' + ia.location ELSE '0' END AS CMDBToken, ia.app_category,

ia.app_type --ia.item, ia.last_modified_time

FROM dbo.inv_application AS ia LEFT OUTER JOIN

dbo.machineenvironment ON dbo.machineenvironment.machine_id = ia.machine_id AND dbo.machineenvironment.name LIKE 'Company' INNER JOIN

dbo.machine ON ia.machine_id = dbo.machine.id INNER JOIN

dbo.mac_id ON ia.machine_id = dbo.mac_id.id CROSS JOIN

dbo.machineenvironment AS menv

WHERE (dbo.mac_id.mac NOT LIKE 'win32:%')

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

rajesh

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-07 : 01:47:02

It seems that the tables in join are having different collations.
I feel that you need to explicit specify the collation in that case.

Example:
sELECT COALESCE( D.DNAME,E.ENAME1 COLLATE Latin1_General_CI_AS ,N'') FROM FUNDB..EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources