IBM Support

Error -2147217865 Cannot drop the table 'xmenu' because it does not exist in the system catalog

Troubleshooting


Problem

I.T. administrator has restored a Controller database on their Microsoft SQL server. Administrator logs onto the Controller application server, and launches 'Controller Configuration' from the Start Menu. Next, the user navigates to 'Web Services server' - 'Database Connections' and chooses a database connection (for example 'ControllerLIVE'). User clicks on green 'run' icon ('play' button) to launch the 'Database Conversion Utility'. User notices that the 'Actual' value for 'DB Version' is 0 (zero), not the expected value (for example '711'). User clicks on 'Create Db' or 'Run Steps'. User receives an error message.

Symptom

There are several potential error messages that might appear, such as:

    INFORMATION: Unable to determine current database version
    error -2147217865 (Hex 80040E37);
    Cannot drop the table 'xmenu', because it does not exist or you do not have permission.

and/or
    DBConv Error. Unable to determine database version.
    Error -2147217865 Cannot drop the table 'xmenu' because it does not exist in the system catalog.

and/or
    'Unable to detect current DB version'

Cause

The SQL login (specified inside Controller Configuration) cannot read the tables inside the database.

There are several potential causes for this:

  • Scenario #1 - The SQL login (for example 'fastnet') has dbowner rights, whereas the database is configured to use a non-dbo user
  • Scenario #2 - The SQL login (for example 'fastnet') does not have dbowner rights, whereas the database is configured to use a dbo user
  • Scenario #3 - The newly-restored database has come from a different SQL server, and the administrator has forgotten to synchonise the SQL logins (to remove orphaned users)

Resolving The Problem

TIP: For more information, including print-screens of the exact steps involved, please refer to attached documents:

  • Controller 8.x with SQL 2000 - '07. How to Create new _or copy existing_ databases inside Controller8 - v1.0c - based on SQL 2000.pdf'
  • Controller 8.x with SQL 2005 -'17. Basic database tasks using SQL 2005 and Controller 8 MR1 _sp7+_.pdf' - for Controller 8.x with SQL 2005 database server
  • Controller 2.x with SQL 2000 - '49. Copying a Controller SQL database to a different SQL server.pdf'


Scenario #1 - The SQL login (for example 'fastnet') has dbo rights, whereas the database is configured to use a non-dbo user

Cognos best practices state that the SQL login that you use (e.g. 'fastnet') should NOT be a system administrator. This ensures that 'fastnet' will communicate with the SQL database using non-system admin (non-dbo) rights.

For this to work:
  • Do not use the SQL login 'sa'
  • Instead, use a different SQL login (e.g. 'fastnet') which has only ONE 'role' enabled
  • This SQL login should ONLY have the SQL role 'bulkadmin' enabled (this is purely for 'Optimise2' to work - see separate KB 1032417).

Steps:
Assuming you are using the SQL login called 'fastnet', and are using SQL 2000:
  1. Launch SQL Enterprise manager
  2. Expand 'Security - Logins'
  3. Right-click user name (example 'fastnet') and select 'properties'
  4. Select 'Server Roles' tab
  5. Untick all roles except 'Bulk Insert Administrators'
  6. Click OK
  7. Retest

Scenario #2 - RARE - The SQL login (for example 'fastnet') does NOT have dbowner rights, whereas the database is configured to use a dbo user

Cognos best practices state that the SQL login that you use should NOT be a system administrator (e.g. 'sa'). However, if you intend to connect to the database with 'dbo' rights, then you must ensure that you connect using a sysadmin account.

Steps:
Either:
  • use the built-in 'sysadmin' account called 'sa'.
  • or modify the SQL login (e.g. 'fastnet') to have sysadmin rights by doing the following:

    • 1. Launch SQL 2000 Enterprise manager
    2. Expand 'Security - Logins'
    3. Right-click user name (example 'fastnet') and select 'properties'
    4. Select 'Server Roles' tab
    5. Tick the role 'sysadmin', and also ensure that the role 'Bulk Insert Administrators' is also ticked
    6. Click OK
    7. Retest

Scenario #3 - LIKELY - The newly-restored database has come from a different SQL server, and the administrator has forgotten to synchonise the SQL logins (to remove orphaned users)

To solve this, run the script 'update user.sql', which will be similar to:
    EXECUTE sp_change_users_login 'Update_One', 'fastnet', 'fastnet'
    GO

Steps:
NOTE: If your user is NOT called 'fastnet' then change BOTH values to the different name

For example: EXECUTE sp_change_users_login 'Update_One', 'cognos', 'cognos'

Steps (for SQL 2000):
  1. Logon to SQL 2000 server as an administrator
  2. Launch 'Query Analyser' from the Start Menu
  3. Logon using Windows Authentication
  4. Choose the relevant database (e.g. 'ControllerLIVE') from the selction box at the top
  5. Type in the following script (modify 'fastnet' as appropriate): EXECUTE sp_change_users_login 'Update_One', 'fastnet', 'fastnet'
  6. Press the green 'play' button to 'run' the script

07. How to Create new _or copy existing_ databases inside Controller8 - v1.0c - based on SQL 2000.pdf
17b. Basic database tasks using SQL 2005 and Controller 8 MR1 _sp7+_.pdf17b. Basic database tasks using SQL 2005 and Controller 8 MR1 _sp7+_.pdf
49. Copying a Controller SQL database to a different SQL server.pdf

[{"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":"8.5;8.4;8.3;8.2","Edition":"All Editions","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Historical Number

1040325

Document Information

Modified date:
15 June 2018

UID

swg21347969

Page Feedback