Updating an encyclopedia in IBM Rational System Architect results in "Cannot drop the procedure 'LogFilesHistoryUpdate' " error

Technote (troubleshooting)


This document applies only to the following language version(s):

English

Problem(Abstract)

Attempts to update an encyclopedia in Rational System Architect fails with database error : "Cannot drop the procedure 'LogFilesHistoryUpdate' ".

Symptom

Updating an encyclopedia in IBM Rational System Architect tool results in an error:


    A Database error has occurred in Microsoft OLEDB Provide for SQL Server:

    Cannot drop the procedure 'LogFilesHistoryUpdate', because it does not exist or you  do not have permission

    Executing:  DROP PROCEDURE LogFilesHistoryUpdate



Cause

Execute permissions on the 'LogFilesHistoryUpdate' stored procedure is not provided to the respective user on the encyclopedia.


During the update process Rational System Architect needs to update at least one of the stored procedures, 'LogFilesHistoryUpdate' is one of those. To perform the update the existing version of stored procedures might get deleted and the same copy with a new version is created.
The DROP can fail either because:

  • The copy of the store procedure can not be found.
    This happens because Rational System Architect is expecting 'dbo' as the owner of the stored procedure and it is not the owner.

    Or

  • Because you do not have DROP rights on this stored procedure on the SQL server.

Diagnosing the problem

To investigate the problem:

  1. Check if the Rights and Execute Permissions are assigned for the User on the SQL server for the specific encyclopedia.
    • Normal users:
      • Database Rights
        • DB_DataReader
        • DB_DataWriter

    • Administrator users:
      • Server Role
        • Database Creator


  2. Check that the user has EXECUTE rights on the following Stored Procedures within each Database (encyclopedia):
    EntityExistsByID
    GetNextID
    LockEntityByID
    GetFileSize
    CreateSnapshot
    GetHistoryLoggingStatus
    LogEntityHistoryUpdate
    LogFilesHistoryUpdate
    PurgeHistory
    SaveAuditIDSettings


    To do the check:
      1. Go to System Architect Encyclopedia Manager (SAEM)
      2. Select the encyclopedia from the drop down list
      3. Go to Query > New and type in SP_HELP
      4. Execute the query (F5)
      5. Check if the user tables and stored procedures are owned by 'dbo'.

Resolving the problem

To resolve the problem you must change the ownership of the stored procedures that are not owned by 'dbo':

  1. Go to System Architect Encyclopedia Manager (SAEM)


  2. Select the encyclopedia from the drop down list


  3. Go to Query > New and type in:
    Exec sp_changeobjectowner 'OldUser.<Stored Procedure>', dbo

    Where <Stored Procedure> is from the list
    EntityExistsByID
    GetNextID
    LockEntityByID
    GetFileSize
    CreateSnapshot
    GetHistoryLoggingStatus
    LogEntityHistoryUpdate
    LogFilesHistoryUpdate
    PurgeHistory
    SaveAuditIDSettings


  4. Execute the query (F5)


  5. Execute this query for each table where the owner is other than 'dbo'.



Note: Best practice

Only a user with SQL server role ' sysadmin' should upgrade the encyclopedias.

In case the encyclopedias were upgraded by a 'non-admin' user, the encyclopedia may not be accessible to other users.


Rate this page:

(0 users)Average rating

Document information


More support for:

Rational System Architect
General Information

Software version:

11.3, 11.4, 11.4.2

Operating system(s):

Windows

Software edition:

All Editions

Reference #:

1420360

Modified date:

2013-06-07

Translate my page

Machine Translation

Content navigation