Running pre-upgrade cleanup_orphaned_data_MSSQL.sql script fails with error: Cannot resolve the collation conflict in vCenter Server 6.0

Products

• VMware vCenter Server

Issue/Introduction

Symptoms:

When you run the cleanup_orphaned_data_MSSQL.sql script as part of the Prepare Microsoft SQL Server Database Before Upgrading to vCenter Server 6.0 section in the vSphere Upgrade guide, you experience these symptoms:

• You see the error:
  
  Msg 468, Level 16, State 9, Line 72
  Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
   
• The VMware vCenter Server database is set to a different collation than the default collation of the SQL Server instance.

Environment

VMware vCenter Server 6.0.x

Cause

This issue occurs due to the difference in collation of the TempDB database and the VMware vCenter Server database. When the TempDB database is created as a part of the cleanup script, the database uses the default collation of the Microsoft SQL Server instance. This causes a collation mismatch and the operation will fail.

Resolution

This is a known issue affecting VMware vCenter Server 6.0.

 

To resolve this issue while running the vCenter Server 6.0 pre-upgrade cleanup_orphaned_data_MSSQL.sql script , use the 2113899_cleanup_orphaned_data_MSSQL.sql file in the attached 2113899_cleanup_orphaned_data_MSSQL.zip. Re-run the steps in the Prepare Microsoft SQL Server Database Before Upgrading to vCenter Server 6.0 section of the vSphere Upgrade guide.

 

To avoid this issue, verify that the VMware vCenter Server database is created using the default collation of the Microsoft SQL Server instance.

Workaround:
To work around this issue:

1. Backup the VMware vCenter Server database.
2. Create a new Microsoft SQL Server instance with the same default collation as the VMware vCenter Server database.
3. Restore the VMware vCenter Server database to the new instance.

Additional Information

See the guide preparing a Microsoft SQL Server Database for Migration here   vSphere Upgrade guide .
To be alerted when this document is updated, click the Subscribe to Article link in the Actions box
运行 vCenter Server 6.0 升级前 cleanup_orphaned_data_MSSQL.sql 脚本失败，并显示以下错误：“无法解决排序规则冲突”
vCenter Server 6.0 のアップグレード前 cleanup_orphaned_data_MSSQL.sql スクリプトを実行すると、次のエラーで失敗する：照合順序の競合を解決できません