Stored Procedure Report Issue

[laxmanrao83]

Hi All,

I have report with 12querys with 12 charts.And when i run individula query using view tabular data is giving data,
But wehn i run the report is giving below error.

An error occurred while performing operation 'sqlOpenResult' status='-28'.

FYI-Report has been generated using Stored Procedure creatd in database(db2)
Please help me in this iisue.
Please find the attachemnt for more information
Thanks & regards,
Laxman

[CognosPaul]

That error is a very general "I can't read the SQL results". It doesn't give any information besides that. Click on the details and make sure you read carefully what it says. If you're still having problems, copy and paste those here.

[laxmanrao83]

Hi PaulM

Thanks for your reply.

After upgrade to cognos 10.2 for all errors we are getting below details only

DPR-ERR-2082 An error has occurred. Please contact your administrator. The complete error has been logged by CAF with SecureErrorID:2013-02-14-21:47:55.581-#97

[CognosPaul]

Detailed error have been disabled for your user group.

Ask the Cognos admin to check the cognos log, or even better give authors permissions.

Go to Launch -> Administration -> Security -> Capabilities. Click on the drop down next to Detailed Errors -> Set Properties -> Permissions. Many admins remove the "everyone" from that capability, without bothering to add another group. This can make troubleshooting next to impossible.

[bi4u2]

You can usually work around not having access to view detailed errors by clicking the 'Validate Report' icon from within Report Studio.

[laxmanrao83]

Hi PaulM

I have done the setting which you mentioned above.

Now i am geetting below error.

An error occurred while performing operation 'sqlScrollBulkFetch' status='-232'.

Thanks & Regrads,
Laxman Rao

[CognosPaul]

Can you post the full error details?

[laxmanrao83]

Hi,

Please find the full error message details below.

UDA-SQL-0114 The cursor supplied to the operation "sqlOpenResult" is inactive.UDA-SQL-0107 A general exception has occurred during the operation "execute".[IBM][CLI Driver][DB2/LINUXX8664] SQL0950N The table or index cannot be dropped because it is currently in use. SQLSTATE=55006 RSV-SRV-0042 Trace back:RSReportService.cpp(726): QFException: CCL_CAUGHT: RSReportService::process()RSReportServiceMethod.cpp(263): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchWait_RequestRSASyncExecutionThread.cpp(828): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(277): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): promptPagingForward_RequestRSASyncExecutionThread.cpp(884): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): promptPagingForward_RequestExecution/RSRenderExecution.cpp(670): QFException: CCL_RETHROW: RSRenderExecution::executeAssembly/RSDocAssemblyDispatch.cpp(292): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSLayoutAssembly.cpp(79): QFException: CCL_RETHROW: RSLayoutAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(359): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSReportPagesAssembly.cpp(179): QFException: CCL_RETHROW: RSReportPagesAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(309): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSPageAssembly.cpp(303): QFException: CCL_RETHROW: RSPageAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(309): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableRowAssembly.cpp(177): QFException: CCL_RETHROW: RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(309): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(137): QFException: CCL_RETHROW: RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(359): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSDocAssemblyDispatch.cpp(359): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSChartAssembly.cpp(281): QFException: CCL_RETHROW: RSChartAssembly::assembleRSQueryMgr.cpp(1132): QFException: CCL_RETHROW: RSQueryMgr::getResultSetIteratorRSQueryMgr.cpp(1296): QFException: CCL_RETHROW: RSQueryMgr::createIteratorRSQueryMgr.cpp(1570): QFException: CCL_RETHROW: RSQueryMgr::executeRsapiCommandRSQueryMgr.cpp(1560): QFException: CCL_RETHROW: RSQueryMgr::executeRsapiCommandRSQueryMgrExecutionHandlerImpl.cpp(170): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()RSQueryMgrExecutionHandlerImpl.cpp(162): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()QFSSession.cpp(1147): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1145): QFException: CCL_CAUGHT: QFSSession::ProcessDoRequest()QFSSession.cpp(1102): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1078): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSConnection.cpp(788): QFException: CCL_RETHROW: QFSConnection::ExecuteQFSQuery.cpp(213): QFException: CCL_RETHROW: QFSQuery::Execute v2CoordinationQFSQuery.cpp(4469): QFException: CCL_THROW: CoordinationPlanner

[CognosPaul]

The salient detail is here:

Code Select Expand

[IBM][CLI Driver][DB2/LINUXX8664] SQL0950N The table or index cannot be dropped because it is currently in use.

The sp is modifying the structure of the database, which is generally not something you would want to do. I suspect the SP is creating a temporary table, selecting from it, and returning to Cognos, and finally dropping it . Cognos is probably holding the cursor open on that temp table, which is effectively locking it.

Since this looks like a problem with the SP, there's not much I can recommend short of rewriting the SP.