Loading

×Sorry to interrupt
CSS Error

Refresh

Cookie Preference Center

General Information on cookies

When you visit our website, we use cookies to ensure that we give you the best experience. This information does not usually directly identify you, but it can give you a more personalized web experience. Because we respect your right to privacy, you can choose not to allow some types of cookies by clicking on the different category headings to find out more and change your settings. However, blocking some types of cookies may impact your experience on the site and the services we are able to offer. Further information can be found in our

Cookie Policy.

Strictly Necessary

Strictly Necessary

Always Active

Strictly Necessary

Strictly necessary cookies are always enabled since they are essential for our website to function. They enable core functionality such as security, network management, and website accessibility. You can set your browser to block or alert you about these cookies, but this may affect how the website functions. For more information please visit www.aboutcookies.org or www.allaboutcookies.org.

Cookie Details‎

Performance

Performance

Performance

Performance cookies are used to analyze the user experience to improve our website by collecting and reporting information on how you use it. They allow us to know which pages are the most and least popular, see how visitors move around the site, optimize our website and make it easier to navigate.

Cookie Details‎

Please confirm your settings by refreshing the page.

Functional

Functional

Functional

Functional cookies help us keep track of your past browsing choices so we can improve usability and customize your experience. These cookies enable the website to remember your preferred settings, language preferences, location and other customizable elements such as font or text size. If you do not allow these cookies, then some or all of these services may not function properly.

Cookie Details‎

Advertising

Advertising

Advertising

Advertising cookies are used to send you relevant advertising and promotional information. They may be set through our site by third parties to build a profile of your interests and show you relevant advertisements on other sites. These cookies do not directly store personal information, but their function is based on uniquely identifying your browser and internet device.

Cookie Details‎

Advertising Cookies

Consent Leg.Interest

Select All Vendors

Select All Vendors

Select All Hosts

Select All

Clear Filters

Information storage and access

Apply

Confirm My Choices Reject All Allow All

My Subscriptions

Loading

Knowledge Base

Indexing using a Unicode database on a Microsoft SQL Server fails with error (1031174)

Last Updated: 11/30/2010Categories: Total Views: 4729Language:
English

Subscribe to get an email
newsletter with new updates

Please provide article feedback:

Symptoms

You see errors in the Windows event log when indexing (KB, calls, request, etc.) using a Unicode database on a Microsoft SQL Server. For example:

INSERT INTO IN_IS_WORDS (WORD, DOCUMENT_ID, WORD_COUNT) SELECT WORD, 802610, WORD_COUNT FROM @IN_IS_WORDS_LOAD
END (-1) Violation of PRIMARY KEY constraint 'IN_IS_WORDS_PK'. Cannot insert duplicate key in object 'dbo.IN_IS_WORDS'.

You are unable to index some Unicode words.

Purpose

This article provides information on

Ensuring a Microsoft SQL Server database is compatible with the indexing of calls, requests, and KBs when Unicode characters are used.

Solving issues that may be caused by indexing Unicode characters.

This article does not deal with:

Oracle specifically, although some of the notions will be valid for Oracle as well.

The configuration of indexing.

Resolution

Note: This resolution concentrates on Microsoft SQL Server and the following might not be true for Oracle.

For the indexing of Unicode characters (accentuated characters, chinese characters, and other non-standard characters) to take place properly at a database level, you need:

Unicode database (including a Unicode license key).

Database collation that is Unicode compliant (SQL_Latin1_General_CP1_CI_AS).

Note: Characters that are phonetically/grammatically equivalent to other characters do not index well. This is a limitation of MS SQL Server and Infra.

To resolve this issue, you must meet the criteria below:

The database needs to be a Unicode database for indexing to work on special characters. Otherwise, you may encounter errors during the indexing process.

The type of the WORD column in IN_IS_WORDS should be NVARCHAR. Otherwise, you may experience difficulty inserting those special characters. For a database to be Unicode, you need an Infra Unicode license key, and to select the Unicode option when creating your database.

If the column WORD in the IN_IS_WORDS table is of type VARCHAR, then you will have issues indexing Unicode characters. Changing a database type from non-Unicode to Unicode is possible, but might have implications. If you are experiencing this specific issue, contact technical support.

The database collation must be Unicode compliant. A valid collation for indexing most characters is: SQL_Latin1_General_CP1_CI_AS.

The collation defines sort order, and comparisons, and thus are important when indexing knowledge (where we check if words are identical or not in one article in order to count words).

The collations abbreviations appended at the end mean:

CI: Case Insensitive

AS: Accent Sensitive

Note: These are necessary for the Unicode indexing of most of the common Unicode characters (European characters) to take place properly.

The default collation (although it is not shown in the collation description) is:

KS: Kana sensitive (When Japanese Kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive).

Width sensitivity: When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently, it is width sensitive.

Note: These last two are more focused on Asian Languages. European characters will not cause a problem.

So, the default (and valid for indexing) collation of database, if expressed in full, is:

SQL_Latin1_General_CP1_CI_AS ( KI WI )

The database collation for a database can be found by running this query:

SELECT DATABASEPROPERTYEX('your database name here', 'Collation')

If this is different from SQL_Latin1_General_CP1_CI_AS, there is the risk that Unicode characters will not be indexed properly.

Collation can be changed by running this following query (replace myDatabase with the name of your database).

ALTER DATABASE MyDatabase COLLATE SQL_Latin1_General_CP1_CI_AS

For more information see:

http://www.databasejournal.com/features/mssql/article.php/3302341/SQL-Server-and-Collation.htm

The default collation at http://msdn.microsoft.com/en-us/library/ms188046.aspx

There are specific issues with a defined and limited set of characters that are phonetically/grammatically equivalent to other characters with Microsoft SQL Server databases, even if all of the above conditions are met.

Some very special characters (foreign characters such as ß Œ) are not handled similarly by Infra and MS SQL Server during indexing. This causes issues during insertion of records in IN_IS_WORDS_LOAD and IN_IS_WORDS.

There is a defined and limited list of these characters. For example, ß, Œ are considered as SS and OE respectively in Microsoft SQL Server. This causes the issue described in point #3.

The characters SPIEß SPIESS or OEUFS ŒUFS cannot be indexed. Attempting to index these characters results in Infra returning this error:

The text of these articles cannot be indexed. Infra returns an error:
Violation of PRIMARY KEY constraint 'PK__#75F7B86E__76EBDCA7'. Cannot insert duplicate key in object 'dbo.@IN_IS_WORDS_LOAD'.

The SQL Server ß is considered an equivalent of SS, and Œ is considered an equivalent of OE. However, when Infra scans the article, it finds both words as unique and creates a separate INSERT statement for each word in IN_IS_WORDS_LOAD.
At this point, SQL Server returns the primary key error, because it finds these words as identical rather than unique.

The convention chosen by Infra and SQL Server are different convention around the handling of those specific characters. In future releases, SQL Server ß will not be considered an equivalent of SS, so the indexing would work with the current code for ß and SS.

To check whether you are facing the same issue described in step #3, examine these sample scripts that cause the issue with the specific characters mentioned in this article:

BEGIN
SET NOCOUNT ON
DECLARE @IN_IS_WORDS_LOAD TABLE (
WORD NVARCHAR(128) NOT NULL PRIMARY KEY,
WORD_COUNT INT
)
INSERT INTO @IN_IS_WORDS_LOAD VALUES (N'SPIEß',1)
INSERT INTO @IN_IS_WORDS_LOAD VALUES (N'SPIESS',1)
END

BEGIN
SET NOCOUNT ON
DECLARE @IN_IS_WORDS_LOAD TABLE (
WORD NVARCHAR(128) NOT NULL PRIMARY KEY,
WORD_COUNT INT
)
INSERT INTO @IN_IS_WORDS_LOAD VALUES (N'OEUFS',2)
INSERT INTO @IN_IS_WORDS_LOAD VALUES (N'ŒUFS',2)
END

Detectable by VMware SkylineTM

Actions

Copy

link to clipboard
copied!

Print

Language:
English

Attachments

Additional Resources

KB
•
End Of General Availability of the Free vSphere Hypervisor (ESXi 7.x and 8.x) (2107518)

Result 1 of 1

Ask The Community

Get answers quickly from VMware experts in the community

Post Subject

CONTINUE IN COMMUNITIES

Related Products:

VMware Service Manager

Related Versions:

VMware Service Manager 9.x

VMware Service Manager 8.x

Take Our Survey

Support Assistant