Network Management

NPM Configuration Wizard fails as it Cannot resolve the SQL collation conflict in the concat operation

What to do if The configuration wizard fails with the following error: Database Configuration failed: Error while executing script - Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the concat operation.

First published date

4/29/2019 5:38 PM

Last published date

10/2/2022 3:04 AM

Overview

While upgrading your server:

  • From: NPM12.0 / SAM 6.2.4 / NCM 7.5  
  • To: NPM12.3 / SAM 6.7.0 / NCM 7.8  

The configuration wizard fails with the following error:

  • Database Configuration failed: Error while executing script - Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the concat operation.

This SQL Server is running SQL 2012 and is dedicated to SolarWinds.

  • I have checked and the SQL Database is using the "SQL_Latin1_General_CP1_CI_AS Collation" (As recommended)
  • The SQL Server Instance is using the "Latin1_General_CI_AS" collation.
  • Is there a way to easily change the collation without impacting the database itself?

Product section

Network Performance Monitor

Cause

The SQL Instance Collation doesn't match the Orion DB Collation.

  • As a result, the TempDB and Model DB in the SQL Instance also have the incorrect Collation.

Resolution

You could check what SQL Collation you have configured for SQL Instance, TempDB, DB instance, and column collations.

  • You can also check it here too if you run diagnostics.
    • Check the the Orion Database  Table or Column level collations:
      • Diagnostics -> DBINFO -> NPMCollation.csv
    • Check the  SQL Instance TempDB on the SQL Server side:
      • Diagnostics -> DBINFO -> TempDBCollation.csv
    • Check the SQL Instance collation on the SQL Server side:
      • Diagnostics -> DBINFO -> ServerInfoCollation.csv

You cannot change the database collation for TempDB:

  • It is not possible to change the TempDB collation with ALTER DATABASE command.
  • Instead, you must change the collation of the Model database to the required collation.
  • After that restart SQL Server services and you will find that TempDB collation has changed.
  • The TempDB has changed collation as it auto recreates all TempDB on SQL instance restart.
  • On a restart, the TempDB recreates its tables by inherited the collation of the Model Database.
  • Editing the SQL instance collation will also in turn edit the ModelDB collation.

To edit the SQL Server Instance Collation.

  • If need to edit the SQL Instance Collation, you will need to stop the SQL Server Instance.
    (You can use Services.msc, cmd or SQL Mgmt Studio to do this)
    • NET STOP "MSSQLServer"
  • Execute the command below.
  • A lot of information will appear and no user action is required, just close the prompt window after the execution ends.
  • The parameter "-s" is only necessary if more than one SQL Server instance exists on the target machine.
    • sqlservr -m -T4022 -T3659 -s"MSSQLServer" -q"SQL_Latin1_General_CP1_CI_AI"
    • (Screenshot property of © 2020 Microsoft)
  • Parameters used: 
    • [-m] single user admin mode 
    • [-T] trace flag turned on at startup 
    • [-s] SQL server instance name 
    • [-q] new collation to be applied
  • Note:
  • If issues with CW persist, you may have to detach the Orion Database, then restart SQL and then reattached to stop the CW error from occurring.