In a stored procedure, I'm trying to insert data into a local table (@natureLogement). This data is the result of another stored procedure (avantContrat.getnaturelogement). The problem here is that Babelfish does not resolve even the schema. I got the error :
Msg 33557097, Level 16, State 1, Line 40
schema "avantcontrat" does not exist
I am using Babelfish 3.1.0 version with Postgres 15.2.
When I call my stored procedure like this: EXEC avantContrat.getnaturelogement 3232629;, everything works fine, and I get a result in table format. However, when I attempt to insert this data into a local table like below, Babelfish doesn't recognize the avantContrat schema.
DECLARE @natureLogement dbo.NatureLogementType;
INSERT INTO @natureLogement
(col1,
col2,
...
)
EXEC avantContrat.getnaturelogement 3232629;
The solution I am currently employing is to modify the code of avantContrat.getnaturelogement so that it stores the result in a regular table. Then, in my current stored procedure, I retrieve the same data from this regular table and insert it into the local table. This is a solution if I only have a single stored procedure, but the issue is that I have around a hundred of them.