Problem calling procedure

[Former Member]

PB Version: 12.5.1

Database: SNC ( SQL Server 2008 )

Source code:

DECLARE sp_proc PROCEDURE FOR procedure_name

   @param1 = :ls_var1, @param2 = :ldec_param2 OUTPUT;

EXECUTE sp_proc;

On a specific customer: "Cannot use the OUTPUT option when passing a constant to a stored procedure"

On a local backup, and other 10 customers: Works Fine.

Someone knows why and how to solve it?

Thanks in advance.

[mike_dehart]

Hi Joao,

This is an odd one since it seems to be happening only for one user. As the error states, SQL Server is viewing the output parameter ldec_param2 as a constant rather than a variable. I found a few articles regarding this issue:

Cannot use the OUTPUT option when passing a constant to a stored procedure. - SQL Server Performance

Cannot use the OUTPUT option when passing a constant Stored Proced SQL Server 07-Mar-08 05:13 PM

SQL Server Buddy: Cannot use the OUTPUT option when passing a constant to a stored procedure.

It seems you are correctly using variables rather than constants for the output value, but for some reason it seems for the affected customer the memory reference is either not being created, is released before the output value is returned or cannot be altered by the procedure and thus is seen as a constant.

It seems a lot of users hit this issue when upgrading SQL server to 2008 R2 as well. I am not a SQL Server expert by any means, but I would start by determining how these variables are being stored in the affected user's system. I'd be curious to know if the correct value is being returned by omitting the OUTPUT in the return variable as well.

Hope this helps a little bit. It might also be helpful to ask this question on a SQL Server forum

Mike

[former_member190719]

If you have not done so already, you might want to post this over at the PowerBuilder Developer Center, as that is where the techies hang out.