today I was lucky and managed to make stored procedures working in WebI. I’m glad to share this ‘know how’ and say goodby to DeskI
Change parameter Force SQLExecute in odbc.sbo to Always
Add parameter Force SQLExecute = Always into oledb.sbo
Create query in WebI with the same structure your SP returns
Change SQL to following
set nocount on
/*SELECT any_field FROM any_table*/
exec your_stored_procedure @prompt('enter value','A',<other prompt parameters>)
you can even use temporary tables inside your SP as well as dynamic SQL!
This is just amazing!
Enjoy!
P.S. I have tested this solution on 2 environments and it works fine:
XI R2 SP2 fixpack 2.5, Windows Server 2003, MS SQL 2005 SP2 via ODBC
XI R2 SP2 fixpack 2.3, Windows Server 2003, MS SQL 2000 SP4 via OLEDB
Although this is the solution for MS SQL you can try similar tricks with Oracle or other DBMSs following this empiric guideline:
valid select statement is mandatory in custom SQL code. As we don’t wish to execute it you can enclose it in comments
custom SQL code can not start with comments. So you need to issue some valid command for your DBMS
in my company DeskI has only one advantage - it is able to execute stored procedures. and in some cases I had to use SP as it is the only one way I could ensure acceptable performance. while having only one advantage it has a lot of disadvantages (in my particular environment).
I tried to make stored procedures working for a lot of times and lucky moment has happened
So, converted everything to WebI and now BO is only set of advantages
How do you get to Store prodeduce in WEBI via InfoView?
When I click on Webi, it just give me the universe to select, there no option to select store procedure.
can you please help.
Mini
Dmitry,
Couldnt you have called stored procedures through your universe objects in WebI? Also use hints (make hint objects in universe) and place them as the first object in your webI report for performance?
How is what you do in deski different from what I have mentioned above? I am a bit confused…
My stored procedure populates a table with information based on date ranges which are specified by the user in prompts when running the report. The report then uses that table to retrieve the information.
So it cant be done at universe level i suppose? or maybe it can
but as long as it works fine in webi, it doesnt make a difference
If 2 users run the report at sasme time it will always cause a problem, whether it is Universe or Report , as the stored procedure in each report will be writing to the same table at same time.
But as i said I am happy that i could see this running in Webi
Anyone get this to work with XI R2 on a UNIX platform ? I’ve been told by my SA that the ODBC.SBO and OLEDB.SBO files only exist on the Windows platform. We’re using UDB v8 and the SA added the Force SQLExecute = Always parameter to the DB2.SBO file, but that didn’t work; I still can’t successfully call a stored procedure from a WEBi query (custom SQL override)
Be careful with the loop hole you found out. The Business Objects should support this (Calling Stored procedures from Webi).
Example: If you install a new Service pack and they have closed this loop hole, then all your developed reports in Webi with stored procedures will not work.
I guess BO intentionally does not want to give this access and want us to go through Universe route.
(BOB member since 2006-10-15)
(BOB member since 2002-06-25)
nothing similar with personal data provider 
(BOB member since 2006-02-08)
(BOB member since 2002-06-25)
(BOB member since 2006-10-15)
(BOB member since 2006-12-15)
(BOB member since 2006-12-15)
