urgent help with this query - 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

General SQL Server Forums

New to SQL Server Programming

urgent help with this query

Author

Topic

rajadadi
Starting Member

30 Posts

Posted - 2010-05-06 : 05:42:42

when i run this query it shows below error. please tell me what to do.

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.category AS app_category, ia.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 SQL_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.patch_instance_id AS DictionaryID, ia.category,

ia.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

apodemus
Starting Member

30 Posts

Posted - 2010-05-06 : 05:50:27

you have different collations between fields you try to compare, what type are fields st.guid, ppt.guid ?

apodemus

rajadadi
Starting Member

30 Posts

Posted - 2010-05-06 : 06:03:24

If you don't mind can you modify and post the query please.

rajesh

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-06 : 06:04:02

Can you post the CREATE TABLE scripts for the tables in your query.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-06 : 11:30:37

look in BOL (Books Online) for: COLLATE clause

One way to solve the issue by COLLATEing one or both of the strings so they are the same collation so the system can compare them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources