How do I determine my SQL collation settings?

Apr 24, 2020
Knowledge

Title

URL Name

How-do-I-determine-my-SQL-collation-settings

Article Number

000190126

Environment

Question/Problem Description

The collation can be applied to either the databases or the SQL Server. In order to be in a supported state both the instance and the database must be set to use the following collation:

SQL_Latin1_General_CP1_CI_AS

There are two different methods for getting into a supported collation state depending on how the collation is applied. The steps below will help determine your collation configuration.

The supported collation for the SQL server and WhatsUp databases (WhatsUp, Netflow and NFArchive is SQL_Latin1_General_CP1_CI_AS.

By default our installer delivers SQL express with the collation preset to SQL_Latin1_General_CP1_CI_AS.

If we are installed to a remote instance of SQL, the SQL server collation must be set to SQL_Latin1_General_CP1_CI_AS prior to the installation of WhatsUp.

If the SQL server collation is not SQL_Latin1_General_CP1_CI_AS then the WhatsUp databases will adopt the server collation, which is not supported according to our release notes and database migration guide.

In previous versions we have not enforced this, but due to the addition of new features in APM, wireless and Flow monitor the database upgrade scripts will halt if the collation is not set to SQL_Latin1_General_CP1_CI_AS for both the server and the WhatsUp Databases.

Please confirm the collation in the following manner:

This query is ran from the command line and will show you the SQL Server collation:

sqlcmd -E -S (local)\whatsup -d Whatsup -Q "SELECT CONVERT (varchar, SERVERPROPERTY('collation'));"

This query is ran from the command line and will show all of the database collation:

sqlcmd -E -S (local)\whatsup -d Whatsup -Q " SELECT name, collation_name FROM sys.databases;"

Or you can run these from within SQL Studio Manager:

SELECT CONVERT (varchar, SERVERPROPERTY('collation'));

SELECT name, collation_name FROM sys.databases;

Installation Error: Please Change the collation or choose another instance of SQL

Steps to Reproduce

Clarifying Information

Error Message

Defect Number

Enhancement Number

Cause

Resolution

There are three possible outcomes:

1. The SQL server and or the SQL instance are not set to SQL_Latin1_General_CP1_CI_AS and the WhatsUp databases are set to SQL_Latin1_General_CP1_CI_AS.

In this scenario the database can be backed up and restored to a SQL server with the SQL_Latin1_General_CP1_CI_AS collation or if WhatsUp is the only database residing on the SQL server then the SQL server collation can be changed.

https://msdn.microsoft.com/en-us/library/ms179254.aspx

2. The WhatsUp databases are not set to SQL_Latin1_General_CP1_CI_AS.

If the WhatsUp, Netflow and NFArchive have a non-supported collation then one option is to change the collation using the instructions found in the following kb article.

SQL Collation Change Procedure for WhatsUp

3. The SQL server and the WhatsUp databases are not set to SQL_Latin1_General_CP1_CI_AS.

Simply combined the two resolutions above.

Workaround

Notes

Keyword Phrase

Last Modified Date

4/28/2021 4:55 PM

How do I determine my SQL collation settings?

Files(0)

Was this article helpful?

Was this article helpful?

How do I determine my SQL collation settings?

Information

Related Articles