Executing a stored procedure through cognos

My gut here is that you are not specifying the database and it's schema. So through cognos you may be connecting to the default database of your user or may be purposefully connecting to a different database than your proc exists in. It is actually a best practice to schema qualify your procedure calls as it means the database knows who owns your proc and it doesn't have to make a guess about the owner which creates latches and can cause wait issues.

So I would try:

EXECUTE databasename.owner.compcount

Owner is probably just DBO if you haven't made any changes to default behavior.

What kind of report are you running? If it's from Crystal Reports won't work. This kind of report aren't able to run SP's.

VMontalvao -

They are using Cognos according to the subject line and question. Crystal Reports is different. Judging by the error they are getting the SQL is going through. Probably can't find a proc name.

Yep Cognos 8, it seems to be finding the procedure as it creates the data itm when i run the query in validation its
when i run the entire report it crashes with the error?

Tty and Fully Qualify it as I proposed above and let us know if that changes behavior..

Hi mike

I fully qualified the execute comand as you suggested and when I validate it creates the data items. But when I compile the report I get the following

UDA-SQL-0115 Inappropriate SQL request. UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Deferred prepare could not be completed. UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Statement(s) could not be prepared. (SQLSTATE=42000, SQLERRORCODE=8180) UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Line 1: Incorrect syntax near ')'. (SQLSTATE=42000, SQLERRORCODE=170) UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Incorrect syntax near the keyword 'EXECUTE'. (SQLSTATE=42000, SQLERRORCODE=156) RSV-SRV-0042 Trace back: RSReportService.cpp(610): QFException: CCL_CAUGHT: RSReportService::process() RSReportServiceMethod.cpp(177): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchWait_Request RSASyncExecutionThread.cpp(520): QFException: RSASyncExecutionThread::checkException RSASyncExecutionThread.cpp(173): QFException: CCL_CAUGHT: RSASyncExecutionThread::run(): promptPagingForward_Request RSASyncExecutionThread.cpp(570): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): promptPagingForward_Request Execution/RSRenderExecution.cpp(514): QFException: CCL_RETHROW: RSRenderExecution::execute Execution/RSRenderExecution.cpp(784): QFException: CCL_RETHROW: RSRenderExecution::processActiveDocuments Assembly/RSDocAssemblyDispatch.cpp(235): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssembly Assembly/RSLayoutAssembly.cpp(135): QFException: CCL_RETHROW: RSLayoutAssembly::assemble Assembly/RSDocAssemblyDispatch.cpp(295): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForward Assembly/RSReportPagesAssembly.cpp(113): QFException: CCL_RETHROW: RSReportPagesAssembly::assemble Assembly/RSDocAssemblyDispatch.cpp(254): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssembly Assembly/RSPageAssembly.cpp(236): QFException: CCL_RETHROW: RSPageAssembly::assemble Assembly/RSDocAssemblyDispatch.cpp(254): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssembly Assembly/RSTableRowAssembly.cpp(115): QFException: CCL_RETHROW: RSTableRowAssembly::assemble Assembly/RSDocAssemblyDispatch.cpp(254): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssembly Assembly/RSTableCellAssembly.cpp(94): QFException: CCL_RETHROW: RSTableCellAssembly::assemble Assembly/RSDocAssemblyDispatch.cpp(295): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForward Assembly/RSTableAssembly.cpp(89): QFException: CCL_RETHROW: RSTableAssembly::assemble Assembly/RSDocAssemblyDispatch.cpp(295): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForward Assembly/RSTableRowAssembly.cpp(115): QFException: CCL_RETHROW: RSTableRowAssembly::assemble Assembly/RSDocAssemblyDispatch.cpp(254): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssembly Assembly/RSTableCellAssembly.cpp(94): QFException: CCL_RETHROW: RSTableCellAssembly::assemble Assembly/RSDocAssemblyDispatch.cpp(295): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForward Assembly/RSDocAssemblyDispatch.cpp(295): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForward Assembly/RSAssembly.cpp(488): QFException: CCL_RETHROW: RSAssembly::createListIterator RSQueryMgr.cpp(744): QFException: CCL_RETHROW: RSQueryMgr::getListIterator RSQueryMgr.cpp(809): QFException: CCL_RETHROW: RSQueryMgr::getResultSetIterator RSQueryMgr.cpp(961): QFException: CCL_RETHROW: RSQueryMgr::createIterator RSQueryMgr.cpp(1591): QFException: CCL_RETHROW: RSQueryMgr::executeRsapiCommand RSQueryMgr.cpp(1574): QFException: CCL_RETHROW: RSQueryMgr::executeRsapiCommand QFSSession.cpp(1424): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest() QFSSession.cpp(1422): QFException: CCL_CAUGHT: QFSSession::ProcessDoRequest() QFSSession.cpp(1393): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest() QFSConnection.cpp(750): QFException: CCL_RETHROW: QFSConnection::Execute QFSQuery.cpp(112): QFException: CCL_RETHROW: QFSQuery::Execute v2 CoordinationQFSQuery.cpp(3471): QFException: CCL_THROW: CoordinationPlanner


I beleave its complaining about syntax. again the query works ok sql query analyser but not in the report. i am running the query in cognos as native sql?

Here's a cognos KB that talks about it:

http://support.cognos.com/knowledgebase/googlesearch?load_kb_document=1&dr=kb1&uniqueid=137076

hoooraahhhhh

It works thanks RWrighley been pulling my hair our with this!!!!

Now I need to know if it is possible to pass the start and end report prompts from the reports to the stored proc so will raise as a new questions if you would be so kind to look at chaps

Thanks again