CODEWITHSUNDEEP
sql-serveroracle-databasereporting-services
NJC59
NJC59

Reputation: 31

SSRS Report fails when trying to run Oracle Stored Procedure

I have an issue whereby I can execute an SSRS report which calls an Oracle Stored Procedure in VS2017, but when I deploy to the SSRS Server and run, it returns the following message:-

• An error has occurred during report processing. (rsProcessingAborted) o Query execution failed for dataset 'spTestSubDet'. (rsErrorExecutingCommand) For more information about this error navigate to the report server on the local server machine, or enable remote errors

The dataset 'spTestSubDet' is the Oracle Stored Proc.

Some configuration details:-

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

SSRS version is 15.0.19528.0.

SQL Server version is 2014.

I can execute SQL code and Views against the Oracle server with the same DSN from the deployed report (without the oracle stored proc being present), so I know the DSN configuration is not the issue.

I have also check marked the "Use single transaction when processing the queries" box in the DS Properties.

I’m guessing that it might be some form of “Execute” permissions issue on Oracle, rather than the Report Server, where the Stored Proc is concerned.

As a developer, I don’t have any DBA permissions to interrogate how the SSRS Server is set up, or the Oracle DB, so any suggestions will have to be passed on to my ICT dept.

I also can't enable "remote errors" on the Report Server, but have requested that with the ICT dept.

Any help greatly appreciated.

Seems I got lucky with enabling “Remote Errors” on the report server and not personally having to restart the service. I now have a more explicit error message from the SSRS report:-

“ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'SPTESTSUBDET' ORA-06550: line 1, column 7: PL/SQL: Statement ignored”

As mentioned in my original post, the report works fine locally from VS2017, so I don’t know why it’s telling me when deployed and run from the server that there seems to be a problem with the SQL code:-

create or replace 
PROCEDURE SPTESTSUBDET
(s1 OUT SYS_REFCURSOR)
IS 
BEGIN

OPEN s1 FOR

SELECT
  *
FROM
  onemain.sbceysubmitted sbceysub
WHERE
  sbceysub.STUD_ID = 167071

;
END SPTESTSUBDET;

It’s as simple a test as I can put together and doesn’t use any parameters to complicate things.

I’m wondering if it might be a driver issue, though why it works locally and not on the server is baffling me.

I have Oracle Developer tools “ODAC v18.3.0” installed for VS2017.

The user in the referenced post below had what looked like to be the same problem, but it's not clear what version of the ODAC tools has been used to resolve the issue:-

https://stackoverflow.com/a/60569788/2053847

Any thoughts/help greatly appreciated.

Upvotes: 0

Views: 691

Answers (1)

Ross Bush
Ross Bush

Reputation: 15185

The easiest thing to do is check the log files. I bet this is a SQL exception and it is related to something wrong with the way you are calling the stored procedure or within the stored procedure itself. The log files reside on the SSRS instance at -> SQL SERVER INTALL DIR\MSSQL.15(OR OTHER SSRS VERSION DIR)\Reporting Service\Log Files. Log files for the SSRS manager and SSRS service are saved here. Open the log for the SSRS Service after you encounter the error search for "spTestSubDet" and you should see the detail of the exception that is causing your problems.

Upvotes: 0

Related Questions