[Solved] Stored Procedure - Data modification Type Issue

[pram.b]

Hello All,

I am trying to create Stored Procedure based Query subject in Framework Manager(version 10.2.1 with FP1) of type 'Data Modification'. Stored procedure doesn't return any output but inserts data in database table/s. I am able to import procedure and validate it but when I am testing it, this error is thrown:

"RQP-DEF-0177 An error occurred while performing operation 'sqlExecute' status='-69'.
UDA-SQL-0043 The underlying database detected an error during processing the SQL request.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'ProcedureName"

I am not able to resolve this issue. Why is it throwing above error at testing when I can import the procedure.
Also, I tried publishing the package and check in Event Studio but I can not see the Stored Procedure Query Subject.

Also, I believe Data modification Query subjects can be accessed through Event Studio only, can we access them in Report Studio also, if yes, how, as the reports based on procedure just returns confirmation message saying 'Report Run successfully'.

Please help me with above issues.

Database: SQL Server
Framework Manager : 10.2.1 with FP1

Thanks in advance

[pram.b]

Hi,

Is there any solution for this? Please help.

thanks

[MFGF]

Hi,

Is there any solution for this? Please help.

thanks

Try switching the data source from using ODBC to using SQL Server Native Client. I'm guessing the ODBC might not support what you are trying to do.

MF.

[pram.b]

Thanks MFGF for response.

Highlighted issue is resolved, issue was in Query Subject Definition window in Definition tab - "Stored Procedure Name:" for procedure name say "ABC", it was coming as "ABC;1". After removing extra characters - ";1", procedure gets validated and tested successfully with even running stored procedure successfully and inserting data in required table.

Now the issue is when package is published, the Query Subject is not displayed in Event Studio/Report Studio, can anyone please help how to resolve this.

[MFGF]

Now the issue is when package is published, the Query Subject is not displayed in Event Studio/Report Studio, can anyone please help how to resolve this.

Hi,

You won't ever see it displayed in the package tree in Report Studio (or Event Studio) - but you can call it in Event Studio if you select "Add a task" and point to "Advanced" and select the "Update a database" option.

Cheers!

MF.

[pram.b]

Thanks MFGF for response, it worked  :)

[sunlight]

my problem is also from this topic, so I post it here:

when I create Data modification Stored Procedure with parameters, I cannot find these parameters in Report Studio under existing parameters. do you have any idea, what I am doing wrong? Pls refer to the screenshot for the details.

UPD: I created this stored procedure in the database, and need it to write back to databse vie Cognos, i.e create comments in Cognos reports (like the recepy in Report Studio Cookbook).

[MFGF]

my problem is also from this topic, so I post it here:

when I create Data modification Stored Procedure with parameters, I cannot find these parameters in Report Studio under existing parameters. do you have any idea, what I am doing wrong? Pls refer to the screenshot for the details.

UPD: I created this stored procedure in the database, and need it to write back to databse vie Cognos, i.e create comments in Cognos reports (like the recepy in Report Studio Cookbook).

Hi,

You can't use a data modification stored procedure in Report Studio. They are for use in Event Studio when you select "Add a task" and point to "Advanced" and select "Update a database"

MF.