Tuesday, October 5, 2010

FM and Teradata Stored Procedures

I have been working on using Stored Procedures to update tables through Event Studio based on a condition. The back end DB is Teradata.

With Teradata if you have a separate schema to create Stored Procedures, the schema will not show up in FM when you use the run meta data wizard unless it has at least 1 table or view. To overcome this issue, a dummy table was created in the schema after which the schema showed up in FM. I then imported the SP. The SP is a data modification SP. Teradata SPs cannot be run through FM or through Event Studio because Cognos fires a "CALL SPName" statement rather than an "EXEC SPName" statement.

To work around the above issue create macros and use them through FM or Event Studio. This is only for Teradata. Oracle Stored Procedures do not have any issues.


Arash Z said...

Actually I've been having issues with having an oracle stored procedure integration into event studio. When I import the SP the input parameters being used are grayed out and can't be selected. Ever seen that?

Zephyr said...

Hi Arash, The SP needs to be set up to accept prompt values in FM. You need to assign prompts as values for the arguments in FM. Let me know if this helps.

Anonymous said...

Given the Call vs Exec issue, is one able to run a Teradata proc from tabular SQL in report studio?
Anyone got the syntax correct?