IBM Support

** Troubleshooting ** Unable to determine database version. Invalid Object name 'xfrango'

Troubleshooting


Problem

Customer has recently restored a SQL database on their database server. They are trying to connect the new database to their Controller application server. Administrator launches 'Controller Configuration' from the Start Menu. Administrator opens 'Database Connections' section. Administrator highlights new connection name, and clicks on green 'play' icon ('Run' button'). This launches 'Database Conversion Utility' (also known as 'DbConv'). Administrator notices that the 'Db Version - Actual' is set to zero (0) instead of the expected version (for example 712) If they try to use the 'Run Steps' option (to upgrade the database), they receive an error. Also, all users receive an error when logging into Controller, if they select this database. However, if they create a brand-new database instead, then the 'Run Steps' process works OK, and users can logon to this new (blank) database.

Symptom

The error message will vary slightly, depending on Controller version:

Controller 8.x:


    Unable to determine database version. Invalid Object name 'xfrango'.


Controller 10.x:
    Error. com.microsoft.sqlserver.jdbc.sqlserverexception: Invalid Object name 'Xfrango'

Cause

The SQL login that is being used (for example 'fastnet') has inappropriate permissions/settings.

More Information:
There are several different potential scenarios for this. The most likely are:

  • Scenario #1 - The SQL login user (for example 'fastnet' or 'controller') has been wrongly assigned the SQL server role 'sysadmin' (also known as 'system administrator').
    • In most environments, this is not a correct role for the login to have.
    • For more details, see separate IBM Technote #1666025
  • Scenario #2 - The original SQL backup (.BAK) file was taken from a SQL server 'source' which is a different server from the 'target' SQL server, and the SQL script (for example 'Update user.SQL') has not been run (after restoring the database)
  • Scenario #3 - Cognos Configuration has been wrongly configured to point to a non-Controller database (e.g. 'master')
  • Scenario #4 - The restored SQL database has a user (for example 'fastnet') which does not exist on the SQL server itself, and therefore needs to be manually created.

Diagnosing The Problem

TIP: This Technote currently assumes that you are using SQL 2000 - however, the process is very similar for SQL 2005.

To help understand what is going wrong, you need to know the name of the 'owner' of the Controller tables. Therefore:

  1. Logon to the MS SQL server
  2. Launch SQL Enterprise Manager, and expand <servername> then expand 'Databases'
  3. Expand Controller application repository database (e.g. 'ControllerLIVE') and click on 'Tables'
  4. Ignore the tables which begin 'SYSxxxxxx' (these are system tables, which are owned by 'dbo'), but look at the column named 'Owner' for all the other tables (for example 'xdb07').
  5. Make a note of the value of 'Owner' (for example 'fastnet').
    • This is the name of the Controller database table owner, and shall be used later in this article!

Resolving The Problem

Scenario #1

Remove sysadmin rights from the SQL login.

  • In 99% of customer's environments, the SQL login should only have the roles 'bulk insert' and 'public' enabled.
  • If in doubt check which roles are ticked for another user and use the same.
  • For more details, see separate IBM Technote #1666025

Steps:

  1. Open SQL Server Enterprise Manager
  2. Expand the 'Security' folder, and click on 'Logins'
  3. Double-click on relevant SQL login (e.g. 'fastnet')
  4. Click on 'Server Roles' tab
  5. The *one-and-only* box that should be ticked is 'Bulk Insert Administrator'. Make sure that *no other* box is ticked!

Scenario #2

Run the script 'Update user.SQL' in SQL 2000 Query Analyser (or SQL 2005+ equivalent tool).

Steps:

  1. Launch SQL Enterprise Manager, and expand <servername> then expand 'Databases'
  2. Highlight Controller database (e.g. 'ControllerLIVE')
  3. Click 'Tools - SQL Query Analyser'
  4. Run the following script:
EXECUTE sp_change_users_login 'Update_One', 'fastnet', 'fastnet'
GO

IMPORTANT: Modify both 'fastnet' references (in the script) to the name of your table owner (e.g. 'cognos' or whatever) if you are using a different name, for example:
EXECUTE sp_change_users_login 'Update_One', 'cognos', 'cognos'

Scenario #3

Reconfigure Cognos Configuration to point to the correct Controller application repository database

Scenario #4

Check the correct SQL login name exists on the new server

    Steps:
    1. Open SQL Server Enterprise Manager
    2. Expand the 'Security' folder, and click on 'Logins'
    3. Create new SQL login to match the same name as your database owner (e.g. 'fastnet')
    4. Click on 'Server Roles' tab
    5. The *only* box that should be ticked is 'Bulk Insert Administrator'. Make sure that NO OTHER BOX IS TICKED
    6. Then perform the steps in 'Scenario 2'
    7. Finally, go back to the 'properties' page of the SQL login (e.g. 'fastnet') and click 'Database Access'
    8. Highlight the Controller application repository database (e.g. 'ControllerLIVE') and tick the box
    9. Inside the window underneath, tick the box 'public' and 'dbowner'. Ensure that no other boxes are ticked in this lower section.

    Update User.sql

    [{"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Controller","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.2.0;10.1.1;10.1;8.5.1;8.5;8.4;8.3","Edition":"All Editions","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

    Historical Number

    1041105

    Document Information

    Modified date:
    15 June 2018

    UID

    swg21366714