?
Solved

Executing a stored procedure through cognos

Posted on 2008-06-21
10
Medium Priority
?
2,436 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 51

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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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
 

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 2000 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

752 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