on 07-04-2008 12:00 PM
Hi
I'm using Crystal Reports 11.5 and connecting to a DB2 database on an AS/400 using ODBC.
I have used iSeries navigator's "Run SQL scripts" create a DB2 stored procedure which looks like this:
CREATE PROCEDURE schema_name.proc_name (IN in_param1 VARCHAR(7), IN in_param2 NUMERIC(3))
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE numRecords INT;
INSERT INTO schema_name.table_name (column1, column2) VALUES (in_param1, in_param1);
SELECT count(column1) INTO numRecords FROM schema_name.table_name;
END;
I'm able to create the procedure and when I call it within iSeries it operates as expected. When i add the stored procedure to a new
Crystal Report using the Report Creation Wizard I'm asked to supply the two parameters which is fine, thats what i expect to happen.
However after supplying the parameters I then get an error stating "Database Connector Error: Cannot obtain error message from server".
The stored procedure is not added to the selected tables part of the wizard. However before Crystal fails to add it to the report it DOES manage
to call the procedure. I know this as I can see a new record (which matches whatever parameters i have entered) appears in the table each
time this rather unhelpful error message appears.
Now I am aware Crystal always requires I do a SELECT statement in a stored procedure otherwise there is no data for the report to pull from it.
However I cannot write a DB2 stored procedure which has a SELECT statement which does not include an INTO clause. If i remove the INTO
clause i get the following error from iSeries navigator when trying to create the procedure:
SQL State: 42904
Vendor Code: -7032
Message: [SQL7032] SQL procedure, function, or trigger proc_name in schema_name not created. Cause
. . . . . : SQL procedure, function, or trigger proc_name in schema_namewas not created. The compile was not
successful. SQL creates an SQL procedure, function, or trigger as a C program that contains embedded SQL. Errors not found during
the initial parsing of the CREATE PROCEDURE, CREATE FUNCTION, or CREATE TRIGGER statement can be found during the precompile.
Recovery . . . : If a compile error occurred, see the appropriate listing in QSYSPRT. If the SQL precompile failed, there is
always a listing with the error. If the C compile failed, the listing is only created if requested. Specify SET OPTION OUTPUT=*PRINT
prior to the routine body in the CREATE statement if listings are required.
I have a feeling my problem is being caused by an issue with the SELECT statement, but cannot be certain.
Any help would be most appreciated!
Thanks
John
Edited by: John Hailey on Jul 4, 2008 12:14 PM
Edited by: John Hailey on Jul 4, 2008 12:21 PM
I would have a look at your SP. CR will only return the data from the last SELECT statement. Appears you are doing more than just the one.
Please re-post if this is still an issue to the Data Connectivity - Crystal Reports Forum or purchase a case and have a dedicated support engineer work with you directly
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Please suggest how to connect to the DB2 database from crystal report Designer using DB2 connect.
After report creation i want to publish the report to the repository so that i can view it in Infoview.
Please suggest!!!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.