hI PEOPLE
Just a quick and simple question.
How to run store procedures, SQL stored procedure from a BO doc.
I have to write a procedure that ask the user some parameters and on the basis of this parameteres I have to save some data to a db table.
Does anyone know how to do it.
I can see thata the store I wrote returns the write that but it does not save ti to a tabel, it doe not “commit”.
it is in sql server
please help
bomax (BOB member since 2005-01-14)
Welcome to B:bob:B!
First of all, you might want to edit the subject line in your initial post, so that it is not all in caps. Anything written all in caps is considered yelling, and can be interpreted as rude. 
Are you trying to understand how to run a stored procedure from BusinessObjects? It’s an option on the Data Access screen in the Wizard.
MichaelWelter 
(BOB member since 2002-08-08)
sorry for the mistake for the capital letters.
Thanks, i know of the option, but veen if you have a commit command it does not commit the sotre launched in that way.
I mean the store return the correct data. But it does not commit, the sotre does not save the data to a sql table. My idea si that Bo uses temporary tables
nay idea?
thanks
max
bomax (BOB member since 2005-01-14)
I see what you mean now. I’ve never used stored procedures. Did you search BOB for stored procedures to see if any ideas have been presented before?
MichaelWelter 
(BOB member since 2002-08-08)
I have searched but no useful links.
So I would like some help please on how to launch store that save records to a table.
bye for now and thanks
max
bomax (BOB member since 2005-01-14)
Hi max,
Create a temporary table inside the stored procedure and store all your data inside that. Finally before exiting the store procedure do a
select * from the temporary table.
You should be able to get the data in temp table as data cube in business object report.
Important note: Drop the temporery table after you do the select other wise Databse will give an error (as temp table is already existing in the database).
example for what you are looking:
https://bobj-board.org/t/37420
Faas 
(BOB member since 2004-02-24)
Guys you got to be careful when using temp tables for corporate docs… if multiple users refresh the same report at overlaping time… its going to mess up the results… be sure to use sid… to avoid this.
Jasmin Raj (BOB member since 2005-01-09)
Raj,
That is not going to happen. All the Database software which I know of create the temporery table with session id (And if any stored procedure is creating a temporery table then other stored can’t even access it) which is taken care by the database software. So there is no way that results are going to me mixed or mess up.
One more thing to optimize your stored procedure you can even remove the drop table(temporery table) statement from the end of the stored procedure ( as drop statement do take time to look up the system objects). BUT change the connection parameters
Connections -> Advance -> Disconnect after each transactions
Faas (BOB member since 2004-02-24)
Well depends on how you use temp tables and what type do u intent to create… we have a concurrency of 2000 users estimated at 10%. If our developers decides to create a private temp table at session level… I would imagine the situation for DBA’s… managing temp tablespaces for these many sessions… if the proc drops and recreates as you suggested… it going to be great in terms of performance… So our standard is to use a global or permenant table to store temp results and to handle multiple sesions we use sid… in nut shell… instead of 2000 + temp tables …we use one and share accross user population… and do not drop and recreate the temp table for each and every session…
Jasmin Raj (BOB member since 2005-01-09)
by the way… just wanted to add … we have conducted performance tests on each approach before deciding upon this. I would appreciate if you could let me know if you have a seen better results with any approach that would help us in great extend as we are still striving to achieve better performance.
Jasmin Raj (BOB member since 2005-01-09)
but just one question,
I would need to commit data to this table and “leave” data there without removing them.
Those solutions you sugget allow me to leave data there ?
regards
bomax (BOB member since 2005-01-14)
Raj,
We have lot of reports which use temporary tables. And we have 3000 plus users and cannot avoid or ask user not to refresh the reports. So we have to use temporary tables for each session.
We do not drop the temp table but we drop the connection to the database which drops the temporary tables created for the session. So even if the BO report is open it is not connected to the database. We use the option of disconnect after each transaction, in connection parameters.
Faas (BOB member since 2004-02-24)