Teradata Stored Procedure in FM

[eliza_jane]

I am unable to see any stored procedures that have been created in Teradata through FM. The ID Cognos uses to connect to the DB has permissions to this SP.

On digging, I notice that Cognos retrieves only Tables of type 'T' and 'V' and not 'P' from dbc.tables.

Has any one else come across this issue? Any help is appreciated.

[ksvchowdary]

First establish a DB connectivity well and look into your user ID permissions.While importing the tables or stored procedures in FM , we have options like Data Source Query subject , Model Query Subjects and Stored Procedure Query Subjects.In your case use 'Stored Procedure Query Subjects' and specify the parameters what ever you have in the stored procedures. If you choose Datasource one it will not show Stored procedure query Subjects.I hope it clears your issue.

Note: Please post FM related questions in 'Framework Manager' Group only.It would be easy to track and answer.

[eliza_jane]

We figured out the cause for this.

FM will not show up a schema if it does not contain a single Table / View only in Teradata. We created a dummy table for the schema to be pulled up and managed to import the Stored Procedure.

Now I am facing issues trying to get this to work. The SP throws error:

RQP-DEF-0177 An error occured while performing operation 'sqlPrepareWithOptions' status='9'.
UDA-SQL-0107 A general exception has occured during the operation "sqlPrepareWithOptions".

The SP when called through DB works fine.

Digging through DB logs I found that Cognos fires a "EXEC SP.." statement instead of a "CALL SP.." statement. Teradata returns an "SP.. not a macro" error due to this as EXEC is used only with macros in Teradata and not SPs.

I did come across many forums where this issue has been raised but no solutions have been provided.

Any help?

[arun_citla]

Hi,

I'm stuck on the same issue. Could you please help me with some details of the solution you chose. Were you able to write back to teradata eventually ?

Regards,
Arun