KB12478: An error occurs when trying to create a new object or modify an existing object in MicroStrategy Developer 9.x

SYMPTOM:
When a user tries to create a new object or modify an existing object in Strategy Developer 9.x, the following error occurs:
 
Database Error <> : Execute Query failed. Error type:ODBC error. Odbc operation attempted: SQLExecDirect. Cannot resolve collation conflict for UNION operation.
CAUSE:
The above error usually occurs when there is a conflict among the collations selected for the metadata database, temp database, master database, etc. A collation is used to specify the bit patterns representing each character, and it also specifies how characters are sorted and compared, as shown below:
 

 
ACTION:
To resolve the issue, select the same collation for the metadata as is set for other related databases, such as the temp database or master database. As this is a database specific issue, seeking further assistance from a database administrator is recommended.
 
Consider the following example of how this can be solved in Microsoft SQL Server:
 
The default collation for SQL Server was Latin1_General_CI_AS. The temp database used same collation, but Strategy metadata repository was using SQL_Latin1_General_CP1_CI_AS.
 
To resolve this issue, change the Strategy metadata repository to use the default collation by following the steps below:
 

1. Backup the repository (always perform a backup if the database is going to be changed/worked on.)
2. Make sure that no one is using the Strategy metadata repository (Stop Strategy Intelligence Server and make sure there are no 2-tier connections open.)
3. Using Microsoft SQL Query Analyzer: ALTER DATABASE COLLATE Latin1_General_CI_AS

As every table also has collation (on the columns,) these will need to be changed too:
 

1. Open the Strategy metadata repository database with Strategy Enterprise Manager.
2. Right-click on each of the metadata tables listed below and select 'Design Table':
    
    
   
• DSSMDLNKITEM
• DSSMDLNKPROP
• DSSMDOBJCMNT
• DSSMDOBJDEFN
• DSSMDOBJDEPN
• DSSMDOBJPROP
• DSSMDOBJSECU
• DSSMDSYSPROP
• DSSMDUSRACCT
• DSSMDOBJINFO
3. Click on the square in the top-left corner of the Table Design window and then click in the field to the right of 'Collation' in the bottom of the window.
4. A button with three dots to the right (...) should appear. Click this to open the Collation Editor.
5. Press 'Restore Default', as shown in the image below:
6. Repeat these steps for all tables. Be aware that the table DSSMDOBJINFO must be changed last, as there is a trigger on this table.
7. When all tables have been changed and saved, restart Strategy Intelligence Server and it should be possible to create and change the metadata objects.

Another way of doing this can be to rebuild the master database; this is tricky however, as the whole database system is depending on this.