Solved

Executing a stored procedure through cognos

Posted on 2008-06-21
10
2,397 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 45

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Mirror and Replication 5 18
SQL server 2008 SP4 29 34
combine an MS SQL string in Idera DM 9 31
Slow SQL query 12 27
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

762 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

19 Experts available now in Live!

Get 1:1 Help Now