Solved

Executing a stored procedure through cognos

Posted on 2008-06-21
10
2,400 Views
Last Modified: 2010-04-29
Hi

I am trying to execute a store procedure from Cognos 8. When i validate the statement it does not generate any erros and it creates the data item. But when I run the report I get the error error near word execute. The command i am using is

Execute compcount (cumpcount is the procedure name)

Can anyone tell me if I am doing this correctly please?

Adam
0
Comment
Question by:ac_davis2002
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 21838983
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.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 21839121
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.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 21839321
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.
0
 

Author Comment

by:ac_davis2002
ID: 21840158
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?
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 21840387
Tty and Fully Qualify it as I proposed above and let us know if that changes behavior..
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:ac_davis2002
ID: 21841285
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?
0
 
LVL 12

Accepted Solution

by:
RWrigley earned 500 total points
ID: 21850395
Try putting {} around the command...this signfies "pass through" sql.
0
 
LVL 12

Expert Comment

by:RWrigley
ID: 21850402
0
 

Author Comment

by:ac_davis2002
ID: 21853402
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
0
 

Author Closing Comment

by:ac_davis2002
ID: 31469392
Thanks again
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now