An Enterprise Vault (EV) upgrade fails due to SQL collation issues.

Problem

Enterprise Vault depends on having a uniform SQL collation across the Master and all of the Enterprise Vault databases. An inconsistent SQL Collation will affect both upgrades as well as certain daily operations depending on where the mismatch occurs.

Error Message

Example of errors during the upgrade:

Directory Database - Sample errors

Event Type: Error
Event Source: Enterprise Vault
Event Category: Directory Service
Event ID: 13360
Description:
An error was detected while accessing the Vault Database 'EnterpriseVaultDirectory' (Internal reference: .\ADODataAccess.cpp (CADODataAccess::ExecuteSQLCommand) [lines {1378,1380,1395,1432}] built Feb 22 02:44:10 2007):
Description:  Cannot resolve collation conflict for equal to operation.
SQL Command:
CREATE PROCEDURE [dbo].[SetObjectSecurity2]
@VEID [EVGUID],
@AutoSecurityDesc [image] = 0x,
@ManualSecurityDesc [image] = 0x,
@SIDList [ntext] = NULL,
@Debug bit  = 0
AS
SET NOCOUNT ON
DECLARE @AutoSecurityDescLen int
DECLARE @ManualSecurityDescLen int
DECLARE @Error int
DECLARE @Rowcount int
 

Vaultstore Database Sample errors 
 
Event Type: Error
Event Source: Enterprise Vault
Event Category: Storage Server
Event ID: 13360
Description:
An error was detected while accessing the Vault Database '' (Internal reference: .\ADODataAccess.cpp (CADODataAccess::ExecuteSQLCommand) [lines {1378,1380,1395,1432}] built Feb 22 02:44:10 2007):
Description:
[Microsoft][ODBC SQL Server Driver][SQL Server]The index 'IX_ItemId_Qualifier' is dependent on column 'ItemId'.
SQL Command:
if not exists (select * from syscolumns where name = 'OriginalSize' and id in
(select id from sysobjects where name = 'SavesetProperty' and XType = 'U'))
begin
ALTER TABLE SavesetProperty ALTER COLUMN ItemId EVPropertyID NULL
ALTER TABLE SavesetProperty ALTER COLUMN Qualifier EVQualifier NULL
ALTER TABLE SavesetProperty ALTER COLUMN Properties EVProperties NULL
ALTER TABLE SavesetProperty ADD OriginalSize bigint NULL
end
 

 

Cause

The problem may occur if there has been a change of the SQL server (possibly due to a disaster recovery or migration to a different server) and a different collation was used on the new SQL Server.
If there is a collation mismatch and an upgrade of Enterprise Vault (EV) is performed, the upgrade is very likely to fail, but if not, new columns are added to certain tables as part of the upgrade, which will inherit the new server Default collation, resulting in a mix of collations.

Here are a number of SQL queries to help identify if collation issues will be encountered during upgrades.

Note:  These collation checks are incorporated in the Deployment Scanner as from EV 7 SP4 and EV 2007 SP3. 


1.     The first check is to confirm if the collation of the Enterprise Vault databases match that of the Master database. To do so, run the following SQL query:
 

SELECT name, collation_name FROM sys.databases


 

  

2.    The second check is within each database to see if there is a mismatch with the columns of the database (db) for the EnterpriseVaultDirectory and each vault store database and will return the columns name affected. Run the following SQL query against each Enterprise Vault Database. If the query returns any records, there is a collation mismatch between the columns and the database itself:

SELECT

      collation,

      sysobjects.name tablename,

      syscolumns.name as columnname,

      syscolumns.xtype

FROM

      sysobjects

      JOIN syscolumns on sysobjects.id = syscolumns.id and sysobjects.xtype = 'U'

WHERE 

      collation != convert(sysname,DATABASEPROPERTYEX(DB_NAME(), 'Collation'))

Solution

Normally changing the SQL collation of Microsoft (MS) SQL Databases is the responsibility of the SQL Database Administrator (DBA). 
The method used to address the Collation Mismatch issue varies.
Depending on the level of the Collation Mismatch (for example if the SQL Collation within the Database and Columns is uniform but differs from the Master), the SQL Server can be rebuild with the right Collation.  However if this is not an option and assistance is needed please contact Symantec Technical Support.

Applies To

This issue can occur when moving EV databases from one SQL Server to another, either during EV upgrade or as a standalone operation.

 

Terms of use for this information are found in Legal Notices.

Search

Survey

Did this article answer your question or resolve your issue?

No
Yes

Did this article save you the trouble of contacting technical support?

No
Yes

How can we make this article more helpful?

Email Address (Optional)