Stored Procedures Problem

[ajmcgarry]

I have a problem when accessing Stored Procedures on an SQL Server 2000 database. When it pulls up the parameters box to specify the parameters that you will be passing to the PROC, it always has one extra at the start name RETURN_VALUE.

I can't complete until I specify a parameter for this but if I do specify anything it gives an error that too many parameters were passed. This problem has only started since using Business Objects 5.1, it was fine in BO 4.1.5

Any ideas?

 

[Maiden]

*****CONFIGURATION******
BusinessObjects version 5.1.2
MS SQL Server 2000

***********SYMPTOMS*******
When launching stored procedures with BusinessObjects version 5.1.2 and Microsoft SQL Server 2000, the RETURN_VALUE parameter always appears in the stored procedure editor's parameter list.

**********CAUSE**************
This is not a BusinessObjects issue. This is a known Microsoft bug. There is a problem with the RETURN_VALUE parameter in SQL Server 2000.

SQL Server 2000 returns @RETURN_VALUE instead of RETURN_VALUE.
BusinessObjects' stored procedures engine executes calls to the procedures via Remote Procedure Call (RPC) and understands @RETURN_VALUE as a variable to pass through the procedure, although it is not a variable. This additional parameter causes argument problems such as in the example below:

"[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure Proc_TEST has no parameters and arguments were supplied.8146"

SQL Server 2000 incorrectly returns this parameter to BusinessObjects and the query fails.
The "at" ("@&quot character is used to deal with variables in SQL Server's stored procedures engine. This is why BusinessObjects interprets the @RETURN_VALUE parameter as a variable and causes the problem.

**********RESOLUTION******
Stored procedures under SQL Server 2000 are not supported by BusinessObjects version 5.1.2.

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, launch stored procedures from FreeHand SQL (with the EXECUTE command).

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

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

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

 

[ajmcgarry]

Thanks a lot Maiden. I had forgotten about this thread as I have no profile for e-mail notication set up. Trust Microsoft to put the bug in the ointment.
I guess I'll go the FreeHand route.

Does that mean that BO 5.1.2 has full SQL support in the FreeHand pane?

 

[Maiden]

Freehand SQL should be able to launch the procedure without problems.

The most recent news is that this issue should be in the next major release of BO.