How to Find and Delete a HANA Temporary Table Created in Stored Procedure

[Former Member]

Hi,

I have a stored procedure scheduled as an XSJOB that periodically returns the error below, usually if there has been a system crash while the proc was running and the internal cleanup was not done.

"general error (no further information available); $function$=temp_index_create_itab_ex; $message$=unable to create itabindex XSJOB_SCHED:IT_0x00007fec619c47c0:2:0 (288230376313943604): index already existing."

I have been unable to find where the referenced object ( temporary table ?? ) exists to try and delete it. What I have had to do to work around this is create a new DB user to schedule the XSJOB and the issue goes away, presumably because it is a new user schema.

Can anyone provide guidance as to how to find this temporary object and delete it? Our HANA system is 1.00.097.

Thanks!! Mel

[SergioG_TX]

are you creating a temp table in your stored proc? maybe in your script there may be a reference...

anyway you can post the script u r using here?

[Former Member]

Hi,

I am not explicitly creating a temp table, rather selecting data into a table variable and the system is creating the temp table object \ definition for the variable. As noted in my post, this is only an issue if the system crashes while the procedure is running and the normal internal cleanup of these objects does not occur.

Thanks, Mel

[lbreddemann]

Just saw now that your HANA version is ancient and - by now - out of support.

[Former Member · Accepted solution]

Hi,

To record this in case anyone else has the same issue, the solution was to start and start the xsengine.

[Former Member]

Hi Lars,

I checked for a relevant Note first ( that is my default "go to" for issues like this 🙂 ), but could not find anything applicable.

I will request a consistency check be run, but as this is a Production system will need to find a window where this activity will be allowed.

Thanks for your input.

Mel

[lbreddemann]

This error message seems to relate to a SQL Script internal temp table (that's not the kind you can explicitly create or manage). For some reason, the name for this internal temp table seems to be taken already - which can be due to software bugs or catalog corruptions among other causes.

I'd try and check SAP notes for known issues with "temp_index_create_itab_ex" and also run a consistency check of the database.

If neither yields to explain or solve the issue, the next step is to open a support incident.