div class='cap-left'/>

Geekstuff (EPMA) - ZAP! that orphaned EPMA application!

Monday, May 9, 2016

Introduction

Probably most of you who worked with EPM Architect before faced the situation where an application in EPMA is in a corrupted deployment state and cannot be deleted properly from EPMA the usual way, or EPMA complains that the application already exists while attempting to recreate the EPMA application you deleted.

In most cases running diagnostic against the application would get you back on-track. If not, as a last resort you can drop all tables from the EPMA database by re-configuring the EPMA data source. However in most cases this is not an option.

If you have manually removed all references of the application in all EPM components (how is out-of-scope of this post) and still you cannot re-create the application in EPMA, consider using my SQL script below to ZAP! that orphaned application from the EPMA database.


Disclaimer

Make sure to create a backup of the EPMA database before using this script. In addition you use this script at you own risk! I have tested this script in 11.1.2.4 on MS SQL 2012 only, however it should work on all version of EPMA.

USE ONLY AS A LAST RESORT!

Zap!

Below the script. Simply replace &ltname&gt with the actual application name you wish to ZAP! from the EPMA database and execute the script. If you want to perform a dry-run, replace "DELETE" with "SELECT *".

4 comments :

  1. Nice, I've written something very similar, but got stuck on one point I couldn't get the syntax right for. Your get-object-id statement is too general: If you want to kill APP but not APP2, you're in trouble. I tried to get something like this going (modified for your setup):

    select @c_object_id = c_object_id from or_object where cast(x_object_data as nvarchar) like '%name=\"' + @c_application_name + '\"%';

    but abandoned it and resorted to manual deletes in that table. I needed the cast for SQL 2008, maybe not needed for 2012. If you can work it out, I'd love to see the right syntax.

    ReplyDelete
  2. Dear Anonymous,

    You are right. I have improved the code by replacing "like '%' + @c_application_name + '%';" with "like '%name="' + @c_application_name + '"%';". This should address the issue.

    ReplyDelete
    Replies
    1. Cool, yours works. So I tried again on my syntax.

      I had used lower() because one source of our troubles was that distinct apps got created under the same name, except that the case of the name was different, leading to all sorts of fun & games...

      The cast is forced by the lower function. Turns out my problem was not specifying a length in the cast, resulting in the value getting truncated to 30, which, wouldn't you know it, cut off right in the app name!

      Delete
  3. Dear Anonymous,

    Good to read it works for you now. It is not very common (and absolutely not recommended) to distinguish different applications only by case.

    ReplyDelete