Display service program shows:
======================================
Service program . . . . . . . . . . . . : XX0504V
Library . . . . . . . . . . . . . . . : OELIB
Owner . . . . . . . . . . . . . . . . . : BILL
Service program attribute . . . . . . . : RPGLE
Detail . . . . . . . . . . . . . . . . . : *BASIC
Service program creation information:
Service program creation date/time . . . . . . . : 10/11/16 16:23:38
Export source file . . . . . . . . . . . . . . . : QRPGILE
Library . . . . . . . . . . . . . . . . . . . : OELIB
Export member . . . . . . . . . . . . . . . . . : XX0504B
Activation group attribute . . . . . . . . . . . : QILE
Shared activation group . . . . . . . . . . . . : *NO
Current export signature . . . . . . . . . . . . : E7E7F0F5F0F4E540E289874B40F34BF0
User profile . . . . . . . . . . . . . . . . . . : *USER
Use adopted authority . . . . . . . . . . . . . : *YES
Coded character set identifier . . . . . . . . . : 65535
Number of modules . . . . . . . . . . . . . . . : 1
Number of service programs . . . . . . . . . . . : 6
Number of unresolved references . . . . . . . . : 0
Number of copyrights . . . . . . . . . . . . . . : 0
All creation data . . . . . . . . . . . . . . . : *YES
Observable information compressed . . . . . . . : *NO
Run time information compressed . . . . . . . . : *NO
Allow update . . . . . . . . . . . . . . . . . . : *YES
Allow bound *SRVPGM library name update . . . . : *NO
Profiling data . . . . . . . . . . . . . . . . . : *NOCOL
Teraspace storage enabled modules . . . . . . . : *ALL
Storage model . . . . . . . . . . . . . . . . . : *SNGLVL
Argument optimization . . . . . . . . . . . . . : *NO
Text description . . . . . . . . . . . . . . . . : General purpose service program
Procedure Exports: [last of multiple screens]
Procedure Name ARGOPT
PRGETSECURITYFLAG *NO
PRTIMETOXLSOE *NO
PRGETPRINTDSC2 *NO
======================================
Hope this is helpful.
(Didn't include all the details with the first post thinking there might be a very simple answer).
Thanks!
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Wednesday, October 12, 2016 11:26 AM
To: midrange-l@xxxxxxxxxxxx
Subject: [BULK] - 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.
--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
midrange.com
