Data Modification Stored Procedure Reporting

[Birdie]

Hi All-

I have a situation where I need to allow users to enter text into a prompt and allow that information to write back to my database table. The Sp works fine against SQL Server direct and also works fine in Framework Manager. I cannot- for the life of me- get it to write a value from a text prompt in Reporting.

Any help at all is greatly appreciated! I'm on CA 11.0.9 and using SQL Server 2008R2.

Thanks!!

[Lynn]

Hi All-

I have a situation where I need to allow users to enter text into a prompt and allow that information to write back to my database table. The Sp works fine against SQL Server direct and also works fine in Framework Manager. I cannot- for the life of me- get it to write a value from a text prompt in Reporting.

Any help at all is greatly appreciated! I'm on CA 11.0.9 and using SQL Server 2008R2.

Thanks!!

Does the SP also return a result set? I seem to recall it needs to return a result in order to function within reporting.

[Birdie]

This SP does not return a result at this point. It simply writes to the database.

The odd thing (and maybe I just don't know how to implement it properly) is that I can see the Sp Data Modification Query Subject I set up in Framework Manager, but when I publish the package, I cannot see it through Reporting.

I tried also just using SQL against a Query Subject, but it errors out there.  >:(  I'm out of ideas!

[MFGF]

Hi All-

I have a situation where I need to allow users to enter text into a prompt and allow that information to write back to my database table. The Sp works fine against SQL Server direct and also works fine in Framework Manager. I cannot- for the life of me- get it to write a value from a text prompt in Reporting.

Any help at all is greatly appreciated! I'm on CA 11.0.9 and using SQL Server 2008R2.

Thanks!!

Hi,

If you have defined the stored procedure as a Data Modification stored procedure in Framework Manager, you can only call it from Event Studio. If you defined it as a Data Query stored procedure in FM, then you can call it, but it will need to return a contiguous result set as well as updating your database.

Cheers!

MF.

[Birdie]

Thanks!

So I'll create it as Data Query and when I call it in Reporting from the prompt, it will write back per the SP as long as I also return a result set. So I can simple choose all the other columns from the same table.

I'm trying it out now. Thank you so much for the help!

[MFGF]

Thanks!

So I'll create it as Data Query and when I call it in Reporting from the prompt, it will write back per the SP as long as I also return a result set. So I can simple choose all the other columns from the same table.

I'm trying it out now. Thank you so much for the help!

I suspect the only tripwire might be if the connection is opened as read-only? If you're lucky it will work.

Notwithstanding whether or not it works, I have to advise against this as an approach, though. There is no consistency checking going on - what happens if two users both try to update the same row at the same time? What happens if one updates first but after the other has read the original row, then the other updates - do the original user's changes get lost?

It's not a robust solution - Cognos is a reporting tool not a database update tool. Be very careful what you end up building - especially with General Data Protection Register rules coming into force this year!

Cheers!

MF.