IBM Support

Error 'RQP-DEF-0177 - ...'sqlPrepareWithOptions' status='-56'' when running some standard reports

Troubleshooting


Problem

User clicks 'Maintain - Rights - Reports'. The 'User Rights and Security Group Reports' selection box appears. User ticks/selects (checks) the option 'Menu Groups' (inside the Security Group Reports' sectuon). User clicks 'Preview' button. When the 'User Rights and Security Group Reports (Preview)' windows appears, user receives error message.

Symptom

The problem only affects some (very few) standard reports.

  • In addition, it only affects some selections inside those standard reports.

Example
The problem is seen if the user ticks the option 'Menu Groups' inside 'User Rights and Security Group Reports':

    [HOWEVER, if you only tick 'User Groups' and 'Users' (in other words, you do not tick 'Menu Groups'), then the report will run successfully].

Below is the error seen (when you tick 'Menu Groups'):


User Rights and Security Group Reports (Preview)
IBM Cognos 8 Help
RQP-DEF-0177
X An error occurred while performing operation 'sqlPrepareWithOptions' status='-56'.

Details
UDA-SQL-0115 Inappropriate SQL request.UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Deferred prepare could not be completed.UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Statement(s) could not be prepared. (SQLSTATE=42000, SQLERRORCODE=8180)UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Invalid object name 'cognos.crdConnectedUsersSub'. (SQLSTATE=42S02, SQLERRORCODE=208)RQP-DEF-0149 The query specification is incorrect.RQP-DEF-0457 Referenced Query 'QueryConnectedUsers' is not defined or its query items contain unresolved references.RSV-SRV-0042 Trace back:....

Cause

Code production problem (reference APAR PK98851) in Controller, where it does not fully allow the use of 'dbo' as a table owner.

More Information:
The problem is demonstrated by the following part of the error message: Invalid object name 'cognos.crdConnectedUsersSub'

  • The report is looking for a table 'cognos.crdConnectedUsersSub' when in fact the table's name is 'dbo.crdConnectedUsersSub'

In other words, for a *very small* number of reports/options, Controller (incorrectly) looks for a table called '<sql_login_name>.xxxx' instead of 'dbo.xxxxx'.
  • For example, it may incorrectly look for "cognos.xxxx" if your SQL login name is 'cognos'.

Environment

Controller database tables are owned by "dbo".

  • In other words, all the tables (inside "SQL Server Management Studio" are preceded by the name 'dbo' (see below):


There are several scenarios where this occurs:
  • Scenario #1 (RARE) - Customer using SQL 2000, and is using a 'system administrator' (SA) account.
In other words, customer is using a ('sysadmin') SQL login user (most often the built-in 'sa' account), rather than using the recommended settings which is a 'standard' SQL user (which is then granted dbowner rights).

For example, the problem will affect both the following two separate scenarios:
(1) customer using the built-in SQL login 'sa'
(2) or customer using a custom (created) SQL login (for example 'cognos') but has mistakenly given this 'sysadmin' rights
    • This is not a recommended method of using SQL logins
    • instead, the customer should have created a customer SQL login (for example 'cognos') and given it DBOWNER rights to the database(s).
  • Scenario #2 (LIKELY) - Customer using SQL 2005 or 2008
This problem also affects customers who are using the 'recommended' (i.e. not sysadmin) SQL login settings but are using either SQL 2005 or SQL 2008.
  • This is because Microsoft has changed (from SQL 2005 onwards) some fundamental ways that SQL handles users/schemas. Specifically, schemas are no longer equivalent to database users; each schema is now a distinct namespace that exists independently of the database user who created it. In other words, a schema is simply a container of objects. A schema can be owned by any user, and its ownership is transferable.
  • For more details, see third-party (Microsoft) information.

Resolving The Problem

Fix:

Upgrade to Controller 10.2.1 (10.2.5100.95) or later.

Workaround:

There are two different methods that you can perform to workaround the problem:

  • Method #1 - Create a new user (schema) plus three views which directly point to the actual data tables.
  • Method #2 - Change the owner of the tables (inside the Controller SQL database) from the current ("dbo") to a 'standard' SQL login (for example "fastnet" or "cognos")
    • Although you only need to change the table's owner to fix this problem, other problems will occur unless you change the owners of other SQL objects
    • Specifically, you will need to change the owner of the 'stored procedures' and 'views' too.

Steps

(1) Method #1 - Easiest for most customers
Create a new user (schema) plus three views which directly point to the actual data tables.

Let us assume that:
  • The Controller database is called "ControllerLive"
  • The current user (connecting to the database) is "sa"

  • The future user (that you want to use to connect to the database) is "cognos"
  • The SQL server does not already have a user called "cognos" created
In this scenario, you must ask your SQL administrator (DBA) perform the following steps:

1. Obtain a short period of downtime (no users on the system)
2. Create a new SQL login called "cognos" with server roles "bulkadmin" and "public":


3. Inside "User mapping" tick the 'map' box for "ControllerLive" and ensure that "db_owner" and "public" are the only options ticked
4. Run the following SQL script (naturally you should modify this script if your user/database-name is different):
    /******************************************************************************
    *** Workaround code to allow a Controller User to run the System User Group ***
    *** Security Reports correctly and avoid the APAR 98851 ***
    *** A schemea called cognos is created and the main SQL User is its owner ***
    *** Three Views are then created to simply select all data from the ***
    *** corresponding dbo.crd... tables ***
    ******************************************************************************/

    USE [ControllerLive]
    GO

    /*** Create cognos schema and assign user cognos as its owner ***/
    /*** Change user to your actual username i.e. fastnet if applicable ***/
    CREATE SCHEMA [cognos] AUTHORIZATION [cognos]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    /*** Create three views using the cognos schema to read all from corresponding table ***/
    CREATE VIEW [cognos].[crdConnectedUsersSub]
    AS
    SELECT dbo.crdConnectedUsersSub.*
    FROM dbo.crdConnectedUsersSub
    GO

    CREATE VIEW [cognos].[crdJournalTypesSub]
    AS
    SELECT dbo.crdJournalTypesSub.*
    FROM dbo.crdJournalTypesSub
    GO

    CREATE VIEW [cognos].[crdSecurityItemsSub]
    AS
    SELECT dbo.crdSecurityItemsSub.*
    FROM dbo.crdSecurityItemsSub
    GO

    /*** REMEMBER TO DROP THE VIEWS AND THE SCHEMA BEFORE YOU UPGRADE THE DATABASE ***/
    /*** RERUN THIS CODE AFTER THE DATABASE UPGRADE UNTIL APAR PK98851 IS FIXED ***/


5. Modify the database connection so that it uses the new user (cognos) and save changes:
6. Inside "Controller Configuration", open the section "Report Server"
7. Click the green "tick" button
8. Click "Repair"
9. Test.

(2) Method #2 - Probably more complicated for most customers
IMPORTANT:
  • Although there are no foreseen problems with using this work around, IBM cannot be held responsible for any problems that may result from the use of the following instructions!
      • => use the following at your own risk!
  • The following instructions will only work on 'standard' configured SQL server systems.
    • You may need to modify the instructions to work on your environment!
  • If you do want to try this work around, then it is recommended that you try it on your 'test' database first (perform a 'before' and 'after' test) before trying it on "live".

1. Check what the name of the "SQL login" user is, that the application server is using to connect to the database with.

TIP: You can check this by either:
    • (a) Launching Controller Configuration on your Controller application server, and looking at the 'database connection'
    • or (b) Reading the error message carefully (for example, look for the text such as ''Invalid object name 'cognos.crdConnectedUsersSub'). In this example, you can see that the SQL login name is 'cognos'.

2. Ensure no users are using Controller (period of downtime)
3. Create a backup of the SQL database, as a precaution
4. Change the owners of the 'tables', 'stored procedures' and 'views' from the original name (typically "dbo") to the SQL login name (for example 'cognos' or 'fastnet').
  • For instructions on how to do this, see separate IBM Technote #1442534 - see link below.

[{"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"}}]

Document Information

Modified date:
15 June 2018

UID

swg21378301

Page Feedback