Stored Procedure Universe in B.O 3.1

[Former Member]

Hi,

I am working on creating a universe based on a stored procedure. There are around 245 IN parameters in the stored procedure. I have created the universe. When I run a Web Intelligence report on this universe, it keeps running forever and eventually throws an error "Invalid cursor state". I would like to know if there is any limit in BOBJ in terms of number of IN parameters in stored procedur.

It would be great if somebody helps me to resolve this issue. Would greatly appreciate your help.

[Former Member · Accepted solution]

Hello,

From the latest update we have, (OSS logged with us) our NEXUS engineer has already got in touch with the project manager and has explained him that this is not a best practise and correct approach ( 245 IN parameters ) when using stored procedure Universe. Also there will be a follow up in next couples of weeks ( or days ) from the NEXUS engineer, who will be in touch with the project manager again to help him with a better approach and complete redesign of the universe.

Regards

[Former Member · Accepted solution]

Hi Bisham,

We have not heard anything regarding the best practise and correct approach for universe based on procedures yet. Would like to have someone from SAP help us ASAP.

Thank you.

[Henry_Banks]

Hi,

If i'm not mistaken, you or a colleague submitted a ticket to Primary Support about this already?

Please confirm the OSS number if applicable.

Regards,

henry

[Former Member]

Yes we submitted a ticket already.

Thanks.

[Former Member]

Hello Nityagowri,

There is no limit for the number of input parameters, but not sure that it can handle those many parameters

What is your database.
Are you getting the issue with a simple stored procedure(With one or two input parameters.

Please try the below step and test the issue.

1. Open the odbc.sbo file located in the following path:
<install dir>\ Business ObjectsXIR3\Business Objects Enterprise12.0\win32_x86\dataAccess\connectionServer\odbc
2. Add the following parameter in MS SQL Server 2000 and MS SQL 2005 section(or your specific database section):
<Parameter Name="Force Execute">Procedures</Parameter>
3. Save the odbc.sbo file.

Now test the issue.

Hope this will helps you.

Thanks,
Pramod.

[Former Member]

Hi Pramod,

The database is Teradata. I am not facing the issue with fewer parameters.

[Former Member]

Hello,

teradata docs seem to suggest up to 2047, divisible by the number of commands in the procedure.

(although, that was for OLE DB http://www.google.com/url?sa=t&rct=j&q=&esrc=s&frm=1&source=web&cd=2&ved=0CE4QFjAB&url=http%3A%2F%2F...  )

I would recommend that the WPS be traced, including the ConnectionServer,  so see if the logs reveal any interesting details.

It may be the length of time required to answer 250 variables which actually causes a timeout in the architecture somewhere.

Regads,

Laxminarayana

[Former Member]

Hi Nithya,

Please find the best practices SAP Note for creating stored procedures in the below link:

https://bosap-support.wdf.sap.corp/sap(bD1lbiZjPTAwMQ==)/bc/bsp/sno/ui_entry/entry.htm?param=69765F6...

Also please let me know the following details:

1.Business Objects Version:

2.Database Server:

3.Middleware Used:

In case, if you are connecting to SQL Server, I would request you to test the issue by both ODBC and OLEDB drivers.

Also, let me know if there are any temp tables being used in stored procedures.

Although IN parameters are supported with business objects for stored procedures, I am not sure if 245 IN parameters will cause an issue.

Please let me know if the above information helps you.

Thank you

Hyder

[Former Member]

BO Version: 3.1

DB Server: Teradata

Middleware: ODBC

[Former Member]

Thank you for your response!

[Former Member]

The link you have provided doesn't take me there