Re: UDF not found when importing to Excel via Microsoft Query

On 12-Oct-2016 08:45 -0500, Bill Reed wrote:

I have created a UDF to return a scalar function within an SQL Select
statement. The UDF references a procedure within a service program.
The Select works fine when using SQL on the IBM i - Run SQL Scripts
from Navigator, Run SQL Scripts from ACS (is that the same?), and
green-screen STRSQL. But when I try to reference it from within
Excel, using "Get external data" / "From other sources" / "From
Microsoft Query", I get "SQL0204 - XX0504V in OELIB type *SRVPGM not
found" (where XX0504V is in fact the name of the service program).

I have learned that the SQL0204 "not found" message can really mean
something like parameter mismatch, so I have checked and tinkered
with those to no avail. The connection defined in Excel has the same
user ID and the same library list, plus the UDF name is qualified; I
see no reason that it can't really be found. I suspect either a
difference in syntax between the two SQL's (MS and IBM i) or
something else relating to the connection definition. All other SQL
which I have copied over in this way works fine; it seems to be only
the UDF.

(I'm new on this list so forgive me if this has been addressed
before; I was not able to find anything in the history.)

Thanks for any direction.

What IBM i release, TR and cumulative? What Naming-option? What PATH? What was the CREATE FUNCTION statement? What is the procedure prototype? What was the invocation?; the words "reference it from within Excel" does not sufficiently explain. Present the DSPSRVPGM with the relevant details; i.e. Procedure Name from procedure exports, shown with the SrvPgm name.

As time had passed, I noticed a new followup that appears to have answered most but the Display Service Program details.