Hi,
Running a stored procedure, I get **Error in Stored Procedure Execution.
Couple of Facts:
1-It occurs only when multiple databases are connected.
2-The stored procedure does a select count(*) on all tables.
3-I have a DSN connection with several auxiliary databases. Stored procedure run as follows:
{call catalog1.pub.sp001()}
{call catalog2.pub.sp001()}
...
{call catalog9.pub.sp001()}
4-All auxiliary databases (and primary) have the same stored procedure.
5-Doing a direct connection (no auxiliary dbs) and running the stored procedure works.
-------
I assume the cause stored procedure fails because the catalog name is not specified in the select count() query.
I feel I should not have to pass the catalog name to the SP. I will if this is the only solution.
Anyone had similar issues with stored procedures and mutliple catalogs ?
Any input appreciated.
Thanks!
Etienne
Hi Etienne,
Your problem looks similar to an OpenEdge sql bug that we recently fixed (in 11.7.1).
We will check out this possibility, and should be able to post a reply here tomorrow (Thursday).
....steve pittman [OE sql software architect]
We were able to check this out a little quicker than expected.
The problem you are seeing is a known problem which will be fixed in the upcoming 11.7.1 service pack.
There is no simple workaround.
One possible workaround would be to give all your stored procs unique names and make changes to all the callers.
If using this workaround, don't disconnect and re-connect to one of your aux databases. That hits the same problem exposure.
Clearly this workaround is a lot of work and disruption, so it may not be so useful.
11.7.1 is your best alternative.
hope this helps, ....steve pittman
thanks for your reply!
I have done some testing and I cannot seem to be able to execute a stored procedure on an auxiliary database at all.
I have created an SP empty shell just as below on one of the databases.
create procedure z1sp006 ()
begin
end;
I can execute only if the database is primary. If the database is auxiliary it does not work (error 7853). Using set catalog in SQLEXP does not work either.
I see no workaround to this except if I start an SQL broker for every single database I have.
Etienne
I have decided to change the stored procedure to accept a input parameter for catalog name. The stored procedure is always executed on the PRIMARY database, althought the queries inside the SP will qualify the schema with the catalog passed as input.
If in_catalog.equals(""), do not prefix the query with catalog - query runs on primary DB. Otherwise, qualify all queries schema with input catalog to allows querying auxiliary DB.
This allows me to run the stored procedure in any context and is an acceptable solution right now.
Thanks,
Etienne