PME2020-Adding and Managing Web Content via the Application Framework-Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and " " in the replace…

Search FAQs

PME2020-Adding and Managing Web Content via the Application Framework-Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and " Chinese_PRC_CI_AS " in the replace operation.

Question

The issue is related to PME9.0 Application Note-- Adding and Managing Web Content via the Application Framework. In this document’s prerequisites—"A system running Power Monitoring Expert version 9.0 or higher”.So we think it can be used for PME2020.
Since this is a project for Chinese customer, the OS,SQL, choose Chinese version; PME2020 install choose Chinese language.
But when we try to run the -- Add an Application Tab.sql—got the following error:

Error message:
Msg 468, Level 16, State 9, Line 43
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and " Chinese_PRC_CI_AS " in the replace operation.

Answer

On the Chinese server can you run the following SQL on the ApplicationModules database to determine what Collation the tables have?

SELECT col.name, col.collation_name FROM sys.columns col WHERE object_id = OBJECT_ID('[Configuration].[ConfigurationItemType]')
SELECT col.name, col.collation_name FROM sys.columns col WHERE object_id = OBJECT_ID('[Configuration].[ConfigurationItem]')

This should tell us what collation the Tables are using.
cid:62f7ae75$2$16f8f0b4d07$Coremail$haojingjing.hz$163.com

cid:62f7ae75$2$16f8f0b4d07$Coremail$haojingjing.hz$163.com

Then we can match the collation in the replace statement by adding it behind the value we are replacing. (I have bold what was added to the replace statement)

Example: REPLACE(@id COLLATE SQL_Latin1_General_CP1_CI_AS,'&', '&') or REPLACE(@id COLLATE Chinese_PRC_CI_AS,'&', '&') depending on which collation you need to match to.