Stored Procedure Universe Issues

[Former Member]

Working in version 3.1 SP1 Fix pack 1.8 and created some simple stored procedures to use in the universe and everything worked fine but then due to changing requirements added additional logic to the stored procedures (SQL 2005 database) for the report to include additional prompts and mulitple selects within the code. With these changes, have encountered multiple errors when trying to insert them in the universe or when we are able to insert them in to the universe then no data ever returns from the new stored procedures even though correct execute statements are being created on the SQL Server and they work on SQL Server and in other BI tools. Has anyone else encountered this or know of any limitations using stored procedures in universes?

[Former Member · Accepted solution]

We did resolve our initial issue by changing the universe to use and OLEDB connection rather than using ODBC connection as the ODBC connection didn't seem to support temp tables that were added to the stored procedures. We have run in to other issues with stored procedures but haven't seen the issue that you have with the multiple values.

[Former Member · Accepted solution]

Hi all,

we had similar problem, we had created stored procedures to use in the universe and everything worked fine but then we added additional logic to the stored procedures, after that the universe did not extract objects and relative values any more even though correct execute statements are being created on the SQL Server and they work on SQL Server.

The solution in our case was to remove from MS sqlserver s.p. the SET NOCOUNT ON after that the Universe began to work again.

Hope this helps

Regards,

Giuseppe Q

[Former Member · Accepted solution]

Hi,
following Syntax is using in Custom SQL of WEBI to call a Procedure  in SAP BO3.1

SET NOCOUNT ON
/*SELECT NULL*/
EXEC PROCEDURE_NAME PARAMETER1,PARAMETER2

the same above is working fine in SAPBO 3.1 and in DATABASE SQL SERVER 2008.
but Same Syntax is Not Supporting in BI LAUNCH Pad of SAPBI4.0. Kindly Help us by providing correct Syntax that which supports SAPBI4.0 to call a Procedure

[Henry_Banks · Accepted solution]

Holy thread-resurrection Batman!

Please open a new dicussion after reading the bi4.0 data access guide: http://help.sap.com/businessobject/product_guides/boexir4/en/xi4sp5_data_acs_en.pdf

Also, please specify which SP and Patch you are using.

Regards,

H

[tracyn]

I am encountering the same issue. We're using XI 3.1 SP2 and it doesn't seem to allow multiple values within a stored procedure parameter.