joejosephk
asked on
Db2 stored procedure problem (Pl help)
When I run the first stored procedure I'm getting the error
ERROR [42884] [IBM][CLI Driver][DB2/NT] SQL0440N No authorized routine named "REPORTSAMPLE" of type "PROCEDURE" having compatible arguments was found. SQLSTATE=42884
<code>
SET CURRENT SCHEMA = IS @
SET PATH = IS @
CREATE PROCEDURE RG
(
IN ReportID INT,
IN StartDate TIMESTAMP,
IN EndDate TIMESTAMP
)
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
DECLARE Statement VARCHAR(1000);
DECLARE SpName VARCHAR(50);
SELECT "SQLStoredProcedure" INTO SpName
FROM "Report"
WHERE "ReportID" = ReportID;
SET Statement = 'CALL ' || SpName || '(?,?)';
PREPARE sl FROM Statement;
EXECUTE sl USING StartDate, EndDate;
END @
<code>
SET CURRENT SCHEMA = IS @
SET PATH = IS @
CREATE PROCEDURE ReportSample
(
IN StartDate TIMESTAMP,
IN EndDate TIMESTAMP
)
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
DECLARE REPORT_SQL CURSOR WITH RETURN FOR
SELECT "SiteProfile"."SiteName",
"SiteProfile"."ProfileName ",
"SiteProfile"."Application Name"
FROM "SiteProfile"
WHERE "SiteProfile"."SiteProfile ID" <> 0;
OPEN REPORT_SQL;
END @
</code>
Thanks
j
ERROR [42884] [IBM][CLI Driver][DB2/NT] SQL0440N No authorized routine named "REPORTSAMPLE" of type "PROCEDURE" having compatible arguments was found. SQLSTATE=42884
<code>
SET CURRENT SCHEMA = IS @
SET PATH = IS @
CREATE PROCEDURE RG
(
IN ReportID INT,
IN StartDate TIMESTAMP,
IN EndDate TIMESTAMP
)
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
DECLARE Statement VARCHAR(1000);
DECLARE SpName VARCHAR(50);
SELECT "SQLStoredProcedure" INTO SpName
FROM "Report"
WHERE "ReportID" = ReportID;
SET Statement = 'CALL ' || SpName || '(?,?)';
PREPARE sl FROM Statement;
EXECUTE sl USING StartDate, EndDate;
END @
<code>
SET CURRENT SCHEMA = IS @
SET PATH = IS @
CREATE PROCEDURE ReportSample
(
IN StartDate TIMESTAMP,
IN EndDate TIMESTAMP
)
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
DECLARE REPORT_SQL CURSOR WITH RETURN FOR
SELECT "SiteProfile"."SiteName",
"SiteProfile"."ProfileName
"SiteProfile"."Application
FROM "SiteProfile"
WHERE "SiteProfile"."SiteProfile
OPEN REPORT_SQL;
END @
</code>
Thanks
j
have you tried an execute immediate instead of the prepare & execute?
(no real reason why it shouldn't work with the 2 steps.)
(no real reason why it shouldn't work with the 2 steps.)
ASKER
Some one else develped this stored procedure.I'm debugging it.The procedures are already compiled.No error in compilation.
Report sample is working when it is executed alone.
Thanks
J
Report sample is working when it is executed alone.
Thanks
J
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
2 points:
-Your procedure may be registered on another schema than your login one.
-Your 2 arguments MUST have the TimeStamp datatype
1/ Explicit naming
Using ghp7000 query
SELECT * FROM syscat.procedures where procname in ('RG,'ReportSample')
Try to call your stored procedure using an explicit name containing its owner as a prefix:
LoginUsedToCompileAndRegis terTheSP.R eportSampl e(...)
2/ TimeStamp datatype
If you are not sure of your datatype, use one of the TIMESTAMP functions to enclose your parameters.
Hope this helps.
-Your procedure may be registered on another schema than your login one.
-Your 2 arguments MUST have the TimeStamp datatype
1/ Explicit naming
Using ghp7000 query
SELECT * FROM syscat.procedures where procname in ('RG,'ReportSample')
Try to call your stored procedure using an explicit name containing its owner as a prefix:
LoginUsedToCompileAndRegis
2/ TimeStamp datatype
If you are not sure of your datatype, use one of the TIMESTAMP functions to enclose your parameters.
Hope this helps.
call reportsampl (current timestamp,current timestamp + 1 hour)
does it work?
are you compiling both procedures together or separatly...?