Problem upgrading from UCMDB 10.0 to 10.01

I recenctly upgraded to UCMDB 10.01 on our dev server and when I start UCMDB the State Management service says it fails. 

 

This is the error I am seeing in the error log:

 

2013-07-24 11:32:47,926 [Starter for "state_management" (during Writer server startup). (customer 1, id name: Default Client)] (DalAbstractCommand.java:64) ERROR - Failed to execute command [com.mercury.topaz.cmdb.server.manage.dal.dao.DaoFactory$TransactionProxy$ProxyDalAbstractCommand@38e47d45] time [11 ms] customer ID [1, id name: Default Client] operation stack [N/A]
com.mercury.topaz.cmdb.server.manage.dal.CmdbDalBatchException: [ErrorCode [-2147483648] undefined error code]
Can't execute BATCH prepared statement [MERGE INTO HA_REVISIONS R using (SELECT ? as MODEL_ID, ? as REVISION ) S ON (R.MODEL_ID = S.MODEL_ID) when not matched then insert (MODEL_ID, REVISION) values (S.MODEL_ID, S.REVISION) when matched then update set R.REVISION = S.REVISION;], failed on element at index [0] due to exception: com.mercury.jdbc.sqlserverbase.ddc: [mercury][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'MERGE'.
    at com.mercury.topaz.cmdb.server.manage.dal.CmdbDalPreparedStatementImpl.internalExecuteBatch(CmdbDalPreparedStatementImpl.java:355)

 

Any clues as to how I can recover from this?

  • Verified Answer

    Similar error has been seen when MSSQL database has not been migrated to SQL 2008 or if the database was migrated, the compatibility remains to 90.

     

    This problem shouldn't happen on SQL server 2008, even if it was upgraded from SQL server 2005.

     

    Please run the following sql statements of the customer's SQL server:
    Both of them should run in master context and in the second query replace <DB_NAME> with name of the customer's database name.


    1. SELECT @@VERSION
    2. SELECT compatibility_level FROM sys.databases WHERE name = <DB_NAME>


    The problem with the incorrect syntax of the merge SQL happened because their database's compatibility level is set to SQL server 2005 (compatibility_level = 90).


    Changing it to the compatibility level of SQL server 2008 will solve this problem in the future.
    You can use the following SQL query to change to compatibility level:
    ALTER DATABASE <DB_NAME> SET COMPATIBILITY_LEVEL = 100

     

    Best Regards,

    Juan Calderon