Bize Ulaşın / Contact Us

Changing collation of Virtual Center Database

16 / 02 / 2011 by vCenter Yorum yok / No Comments

I needed to change VMWare ESX’s Virtual Center 2 Database collation while moving the DB to another MS SQL server using different collation, you might not need to change VC2 DB collation but I think it is better to use default server collation. As usual for most databases I got the error below; because of an object dependent on database collation.

  • Alter failed for Database ‘VirtualCenter2’.  (Microsoft.SqlServer.Smo)
  • An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
  • The object ‘sys_PhysicalIndexStatistics_Wrapper’ is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
  • ALTER DATABASE failed. The default collation of database ‘VirtualCenter2’ cannot be set to SQL_Latin1_General_CP1_CI_AS. (.Net SqlClient Data Provider) (Microsoft SQL Server, Error: 5075)

You can easily deal with this issue by using the steps below.

  1. After restoring the DB on target server , browse to VC2 DB\Programmability\Functions\Table-valued Functions, right click on sys_PhysicalIndexStatistics_Wrapper then select Script Function As > Create To > New Query Editor, it will create the query needed to re-create the  sys_PhysicalIndexStatistics_Wrapper object.
  2. Delete sys_PhysicalIndexStatistics_Wrapper
  3. Change collation of the database.
  4. Run the query you have created on step 1. , check if sys_PhysicalIndexStatistics_Wrapper created successfully.

Leave a Reply

Your email address will not be published. Required fields are marked *

Kontrol / Control * Time limit is exhausted. Please reload CAPTCHA.

This site uses Akismet to reduce spam. Learn how your comment data is processed.