Solved

Executing a stored procedure through cognos

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

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DTS Connection Failed 7 70
Sql server function help 15 31
What's wrong with this T-SQL Foreign Key? 7 46
Tsql query 6 22
Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

809 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