Cannot resolve the collation conflict between SQL_Latin1_General_CP1_CI_AS and …

by ·Comments Off on Cannot resolve the collation conflict between SQL_Latin1_General_CP1_CI_AS and …

Product:
Cognos Controller 10.4.2
Microsoft Windows 2019 server
Microsoft SQL server 2016

Issue:

When open data entry, you get a system.exception error:

Source: ControllerForms12
Description: System.Web.Services.Protocols.SoapException: Server was unable to process request. —> System.Exception: Cannot resolve the collation conflict between “SQL_Latin1_General_Pref_CP1_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.
at Microsoft.VisualBasic.ErrObject.Raise(Int32 Number, Object Source, Object Description, Object HelpFile, Object HelpContext)
at ControllerServerCommon.RaiseErrSrv.RaiseException….

This on a new SQL server installation, where you have restored your Controller database backup.

Solution:

Change collation settings on the SQL server. Cognos Controller demand that the SQL server and its TEMPDB have the same collation as the controller databases. Therefor you often have dedicated SQL servers for Cognos Controller systems.

Recommended format is: “SQL_Latin1_General_CP1_CI_AS”

Other collations will work, as described here: https://www.ibm.com/support/pages/which-sql-collations-are-supported-controller

You can change the SQL server collation without reinstall it, by this hidden feature.

  • Stop all Cognos services.
  • Detach all user databases (right click on Controller database in SQL manager and select Tasks – Detach)
  • Make backup of all other stuff
  • Stop SQL Server Service – can be done in Windows services.
  • Start a CMD prompt as Administrator
  • Go to folder C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn
  • Start the SQL Server Service using this command:
    sqlservr -m -T4022 -T3659 -q”SQL_Latin1_General_CP1_CI_AS”
  • Now check for the message “Recovery is completed”, and then press CTRL+C to Stop the SQL Server service
  • Start the SQL Services Normally – from Windows services.
  • Check for the change in SQL Management Studio with below query:
    select serverproperty(‘COLLATION’) as servercollation
  • Attach the user databases back (right click on Databases in SQL manager and select Attach)

Use this with extra care and with your own risk!

 

More information:

https://www.sanssql.com/2013/10/change-sql-server-collation-without.html

https://www.mssqltips.com/sqlservertip/3519/changing-sql-server-collation-after-installation/

Download SQL manager from https://docs.microsoft.com/en-us/sql/ssms/release-notes-ssms?view=sql-server-ver15#previous-ssms-releases

https://www.dbvis.com/download/