cancel
Showing results for 
Search instead for 
Did you mean: 

Call Stored Procedure From Remote HANA System

celo_berger
Active Participant
0 Kudos

Hello,

In my scenario, I have two HANA DBs connected to each other via SDI:

HDB1 -> HDB2

I have written a stored procedure in HDB1, and I would like to know if it is possible, from the stored procedure in HDB1, to call another stored procedure in HDB2.

Thank you,

Marcelo

Accepted Solutions (0)

Answers (2)

Answers (2)

pfefferf
Active Contributor

No, it is not possible to call a procedure on your HDB2 from within a procedure on your HDB1. Interactions have to be done (at least at the moment) on a higher layer, e.g. a service on HDB1 uses a service from HDB2 which uses your procedure on HDB2.

Also the available VIRTUAL PROCEDURE option will you not help, because it supports no SQLScript procedures until now.

The Synonym approach suggested in the other answer is also not supported, cause the "database" option in a synonym is not valid for procedures (only for views and tables). In addition the synonym option would only work for a cross tenant database access in a multi database container system (I assume your both mentioned databases are not just tenant databases within the same HANA MDC system).

celo_berger
Active Participant
0 Kudos

Hi Florian,

I have a modified question for you.

Here's my scenario: datasource <- HDB1 -> HDB2

HDB1 is my monkey in the middle (let's not discuss the architecture since we have some security constraints that don't allow us to go directly from datasource to HDB2).

I have added source virtual tables from datasource into HDB1, and target virtual tables from HDB2.

In the SQL console of HDB1, I'm able to write a SELECT statement from datasource and then an INSERT into HDB2, and it works perfectly.

However, when I take the exact same code and encapsulate it in a stored procedure, it returns 0 records.

Is that a limitation of stored procedures vs the SQL console, where it cant read from a virtual table and insert into another virtual table? It all has to be local?

Thanks,

Marcelo

pfefferf
Active Contributor
0 Kudos

No, there should be no difference.

celo_berger
Active Participant
0 Kudos

So could it be security? The stored procedure runs, but it says it processes 0 records, as opposed to the SQL console which processes the correct amount of records.

raphael_almeida
Active Contributor
0 Kudos

Hello celoberger,

Have you tried to use synonyms for it?

BR,

Pacheco.

celo_berger
Active Participant

Hello Raphael,

No I haven't. What would be that approach?

Thanks,

Marcelo