Search the VMware Knowledge Base (KB)
View by Article ID

Upgrading to vCenter Server 4.1 or later fails when the installer upgrades the database (1024449)

  • 15 Ratings

Symptoms

  • Upgrading to from vCenter Server 2.5 or 4.0 to vCenter Server 4.1 or vCenter Server 5.0 fails
  • The upgrade fails when the installer upgrades the database.
  • The VCDatabaseUpgrade.log file (located at %temp%, or C:\Document and Settings\<logged user>\Local Settings\Temp) contains the entry:

    create unique clustered index VPXI_DEVICE_COUNTER_INDEX on dbo.VPXV_DEVICE_COUNTER(entity_id, device_name, stat_id)
    [14/07/2010 11:14:43] Error: Failed to execute command: create unique clustered index VPXI_DEVICE_COUNTER_INDEX on dbo.VPXV_DEVICE_COUNTER(entity_id, device_name, stat_id)
    [14/07/2010 11:14:43] Got exception: ERROR [42000] [Microsoft][SQL Native Client][SQL Server]CREATE INDEX failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

Resolution

To access an indexed view or indexed computed column, these options must be set to on:
  • ANSI_NULLS
  • ANSI_WARNINGS
  • ANSI_PADDING
  • CONCAT_NULL_YIELDS_NULL
  • QUOTED_IDENTIFIER
  • ARITHABORT
To determine the current settings of these options:
  1. Use SQL Management Studio to connect to the SQL database for vCenter Server.
  2. Give focus to the vCenter database specifically, and select New Query.
  3. Enter this information in the Query window and click Execute:

    SELECT SESSIONPROPERTY ('ARITHABORT')
    SELECT SESSIONPROPERTY ('ANSI_NULLS')
    SELECT SESSIONPROPERTY ('ANSI_WARNINGS')
    SELECT SESSIONPROPERTY ('ANSI_PADDING')
    SELECT SESSIONPROPERTY ('CONCAT_NULL_YIELDS_NULL')
    SELECT SESSIONPROPERTY ('QUOTED_IDENTIFIER')

    In the Results tab of the lower right window (below the Query window), you see a 1 if the setting is on and a 0 if the setting is off.

    Note
    : For more information, see the Microsoft Knowledge Base article 305333.

    The preceding link was valid as of July 21, 2010. If you find the link to be broken, provide feedback on the article and VMware employee will update the article as necessary.
These options can be set at the server (global) level or at the database (local) level. Before making any change, determine if the vCenter Server database is the only database running on the server or if it is sharing with any other databases on the server. If the vCenter Server database is the only database on the server, use the GUI method. If the vCenter Server database is running along with other databases on the server, use either the GUI method or the T-SQL method.
 
GUI Method
 
To change the setting of the options from the GUI, from Enterprise Manager (SQL Server Management Studio):
  1. Right-click the database, then click Properties > Options.
  2. Click on each option and set to True.
T-SQL method
 
To change the setting of the options using the T-SQL method:
  1. Log in as the vCenter Server database user.
  2. Choose New Query to launch the query panel.
  3. Select the vCenter Server database from the pull down menu.
  4. Type these SQL statements in the query panel:

    SET ANSI_NULLS ON;
    GO
    SET ANSI_WARNINGS ON;
    GO
    SET ANSI_PADDING ON;
    GO
    SET CONCAT_NULL_YIELDS_NULL ON;
    GO
    SET QUOTED_IDENTIFIER ON;
    GO
    SET ARITHABORT ON;
    GO

    Note: To verify the options, run this SQL statement:

    DBCC USEROPTIONS
    GO


    Note: Most of these options (QUOTED_IDENTIFIER, ARITHABORT, ANSI_NULLS, CONCAT_NULL_YIELDS_NULL) are default ON for compatibility 90(sql2k5) and 100(sql2k8).

Additional Information

Note: The arithabort error message may still occur if the SQL compatibility mode is set to 2000(80) instead of 2005 or 2008, even if all options are set to 1.

Tags

install-upgrade-vcenter-database-fails vcenter-database-upgrade-fails

See Also

Update History

03/14/2011 - Added note on arithabort error message in the Additional Information section

Request a Product Feature

To request a new product feature or to provide feedback on a VMware product, please visit the Request a Product Feature page.

Feedback

  • 15 Ratings

Did this article help you?
This article resolved my issue.
This article did not resolve my issue.
This article helped but additional information was required to resolve my issue.

What can we do to improve this information? (4000 or fewer characters)




Please enter the Captcha code before clicking Submit.
  • 15 Ratings
Actions
KB: