Process Failed

Support - Assistance technique
1

Hey everyone, i have encountered a problem…
yesterday i tried to do sql view “select * from organisationunit” but nothing executed so i deleted the sql view…
after that i went to analytic tables to performe analytics and also i went to resources table to generate resources but i get these error
"((2019-04-18 08:34:06 Process failed: StatementCallback; uncategorized SQLException for SQL [drop table _orgunitstructure;]; SQL state [2BP01]; error code [0]; ERROR: cannot drop table _orgun))

community
community1789×759 80.7 KB
"

1 Like
2

HI @ahly,

My thoughts on this:

How did you go about removing the SQL View? and was it the View you were removing or the Table _orgunitstructure

From the error, it seems you were removing a very important table from the system when you were deleting the SQL View…

Drop table is

Removing one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables. NOTE − You should be very careful while using this command because once a table is deleted then all the information available in that table will also be lost forever (Unless you have a backup of your db).

Any view or stored procedure that references the dropped table must be explicitly dropped by using DROP VIEW or DROP PROCEDURE. you can Check out more info on views from Here and Here

Let me know if this helps.

Best,
James.

3

but when i wrote the sql view i didnt intend to remove any table i intended to get the list of organisations… so i wrote “select * from organisationunit”…
then it executed nothing and i decide to delete the view… then when i did analytics it brought me that error…

1 Like
4

Hi @ahly,

A follow up - have you found a fix for the error displayed?
The error log 2BP01 >> dependent_objects_still_exist - kindly check out the running scripts and the views in your DB, and also try to clear your cache then let us know how it goes.

Best,
James.

Best,
James.

5

sorry for late follow up… Some one else took care of it and now it works well

1 Like
6

That’s great to know @ahly!

Please share the resolution so that other users facing the same can know how to work around it.

Best
James.

7

i really don’t know how he did it

1 Like
8

For anyone who would come in 2021 or later looking for this solution.

  1. A person created views that were conflicting with the resources tables thus failing to generate and or drop _orgunitstructure.

  2. You need to manually drop all the existing views if you have backend Postgres access by writing the following query against the target databases which will generate the drop statements automatically.

SELECT 'DROP VIEW ’ || table_name || ‘;’ FROM information_schema.views WHERE table_schema NOT IN (‘pg_catalog’, ‘information_schema’) AND table_name !~ ‘^pg_’;

This will list all the views like

DROP VIEW _view_datavalue_audit_ms;
DROP VIEW _view_data_audit_ms;
DROP VIEW _view_facility_de_ms
DROP VIEW _view_org_unit;

From that list, you can just pick the problematic views and drop them by copying and paste the drop view for example if it’s _view_org_unit you do DROP VIEW _view_org_unit; if you want to clear everything then select all and run them scripts.

After you have done this, you can generate the resource tables. Fix your SQL views correctly now. Rerun the resource table generation and see if you still have issues. But that’s the solution.

2 Likes