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

Article:TECH55063  |  Created: 2007-01-29  |  Updated: 2014-02-18  |  Article URL http://www.symantec.com/docs/TECH55063
Article Type
Technical Solution

Product(s)

Environment

Subject

Issue



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



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
 

 

Environment



This issue can occur when upgrading to one of the following EV versions:

  • EV 7 Service Pack X 
  • EV 2007 Service Pack X
  • EV 8 Service Pack X
  • EV 9 Service Pack X
  • EV 10 Service Pack X

 

 


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.

Supplemental Materials

SourceETrack
Value1153247
Description

Collation issues


SourceETrack
Value1153259
Description

wrong index on saveset property which can cause upgrade to fail if there is collation issues

 


SourceETrack
Value13360
Description

Cannot resolve collation conflict for equal to operation

 


SourceETrack
Value13360
Description

An error was detected while accessing the Vault Database

 



Legacy ID



293475


Article URL http://www.symantec.com/docs/TECH55063


Terms of use for this information are found in Legal Notices