Execute Store Procedure from SQL query object in Report Studio

[Dominguez]

Hi!

My report has an SQL query object that execute an SQL Server sotre procedure.  The store procedure insert data into temporary tables. When I click on the Validate button I get this error without any more detail: The server returned an unrecognizable query framework response

If I write an insert statement in the SQL in Report Studio (without calling an store procedure) I get the same error.

Is it a permissions problem in the database or in Cognos?   I verified the permissions and everything seems to be OK.

I appreciate any help.

Thanks!
Christian

[Grim]

Cognos is a reporting tool meant to read "Select" data not "insert". That said though, if the account performing the SQL query has proper DB rights you should be able to insert (Theoretically). So this might be a DB perms issue. Trace it with your DBA to see what the DB is coming back with.

Did you see the following technote:
http://www-01.ibm.com/support/docview.wss?uid=swg21576274

[Dominguez]

Hi Grim. Thanks for your response.

The DBA verify the accounts permissions and I still have the issue.   

Could it be something wrong in the expression?  This is the expression I am writing in the SQL window in Report Studio:  {exec Database_Name.Schema.Stroe_Procedure_Name #prompt('field1','integer')#, #prompt('field2','integer')#}

Thanks!

[Grim]

You'd probably get better results if you import your Stored Proc into FM. This is the proper way to use Stored Procs.

http://publib.boulder.ibm.com/infocenter/cbi/v10r1m1/index.jsp?topic=%2Fcom.ibm.swg.ba.cognos.ug_fm.10.1.1.doc%2Fc_stored_procedure_query_subjects.html

[Dominguez]

I agree with you.  But, this report is working in CRN.  It use the same SQL pointing to the same database.  I would like to migrate the report exactly like it is in CRN.  If there's no other option I will have to import the SP into FM.

Thanks for your help.