Updating vCentre 4.0 to 4.1 Database Upgrade Error

[StuPittwood]

Hi all,

I'm attempting to upgrade my vCentre server from 4.0 to 4.1. I'm migrating from a 32bit server to 64bit with a non-local database (SQL 2005 SP3).

Running through the datamigration tool, I get to the point where the vCentre install has kicked off & is trying to upgrade the database. The Process errors with 'SQL Exception thrown while running script"

In the VCDatabaseUpgrade.log I get the following lines

Error: Failed to execute command: create unique clustered index VPXI_DEVICE_COUNTER_INDEX on dbo.VPXV_DEVICE_COUNTER(entity_id, device_name, stat_id)

Got exception: ERROR [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.

Error while upgrading: ERROR [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.

Info: Exiting Upgrade Wizard

This leaves me with an unusable database & needing to go back to the backup.

Has anyone seen this? or know how to resolve it?

Regards

Stu

[RAMESA · Accepted solution]

Hi Stu,

Might due to ARITHABORT option is not set to ON. Can you let me know what compatibility level the db is at?

You can find it by right click the db , choose property, then choose option, it is the 3rd option from the top

or

run

sp_dbcmptlevel 'vcdb'

Regards,

Ramesh

[StuPittwood]

Ramesh,

That's spot on, the database was set to 2000 Compatibility. Change it to 2005, re-ran the upgrade & worked like a charm.

Thanks for your help.

Stu