Stored Procedure with SQLServer 2000

[oguime]

When I try to run a stored procedure in SQL Server 2000, BO asks for a RETURN_VALUE that shouldn't exist.

Is there any patch or workaround, besides using a hand-made SQL like:

exec (STORED_PROCEDURE_NAME)

Thanks in advance!

Gilson

 

[Guest_imported]

*****CONFIGURATION******
Business Objects 5.1.2
MS SQL server 2000

***********SYMPTOMS*******
Error when launching stored procedures with BO 5.1.2 and SQL SERVER 2000. The RETURN_VALUE
parameter always appears in the stored procedure editor's parameter list.

**********CAUSE**************
It is a known bug from Microsoft. There is a problem with the RETURN_VALUE parameter in SQL SERVER 2000. SQL 2000 returns @RETURN_VALUE instead of RETURN_VALUE.
BusinessObjects 's stored procedures engine relies on this parameter via the RPC (Remote Procedure Call) in order to execute stored procedures. SQL SERVER incorrectly returns this parameter to BusinessObjects and the query fails.
eg. Instead of calling a stored proc like :
{call dbo.myproc}
the RETURN_VALUE adds an unexpected parameter that causes the error :
{call dbo.myproc()}

Moreover the @ sign is used in order to deal with variables in SQL SERVER'S stored procedures engine.
That's why SQLBO interprets the @RETURN_VALUE parameter as a variable and causes the problem.

**********RESOLUTION******
It is not possible to refer to the return value parameter by name in such a way that client application code works with both SQL Server 7.0 and SQL Server 2000
To workaround this issue, you can launch your stored procedures from FreeHand SQL

**********MORE***************

http://support.microsoft.com/support/kb/articles/Q285/2/95.ASP

http://support.microsoft.com/support/kb/articles/Q272/0/74.ASP