With SET SCHEMA the current schema is set when using SQL Naming Conventions.
But the current schema is only searched for Tables, Views and Indexes.
Stored Procedure and user defined functions are searched in the SQL PATH.
So the SQL PATH must be set correctly for an unqualified call.
I suspect more, that your stored procedures are generated with READS SQL
DATA, but if you perform a REFRESH, you need to create BOTH procedure with
MODIFIES SQL DATA
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Rob
Berendt
Sent: Dienstag, 3. November 2020 19:00
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SQL stored procedure calling another stored procedure with
sqlstate 42704 not found
The big clue is qualified vs unqualified.
Instead of trying
Call test010818.pricegrid_refresh()
If you were to try
Call pricegrid_refresh()
Would it work? Probably not. I suspect that test010818.pricegrid_export();
does not do a qualified call to pricegrid_refresh
See also
SET SCHEMA
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail
to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Mitch
Gallman
Sent: Tuesday, November 3, 2020 12:17 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: SQL stored procedure calling another stored procedure with sqlstate
42704 not found
CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know the
content is safe.
I have a SQL stored procedure calling another SQL stored procedure.
In the main procedure pricegrid_export it does a CALL pricegrid_refresh().
In Run SQL Scripts I've done the following...
Set schema = TEST010818;
Set path = TEST010818;
Call test010818.pricegrid_export();
The call fails with SQL State 42704 PRICEGRID_REFRESH in TEST010818 type *N
not found.
If I do the following it works...
Call test010818.pricegrid_refresh()
Why is my stored procedure having a problem finding another stored procedure
in the same location?
Thanks.
Mitch Gallman
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
