1. Linda Kelty (Dudzic)
  2. PowerBuilder
  3. Monday, 13 April 2020 23:39 PM UTC

I'm in the process of migrating our PB 12.5.2 applications to PB19. I'm using the SQLNCLI11 provider. Have just run into the following error upon executing a stored procedure.

SQLDBCode 179
SQLErrText:
SQLSTATE = 42000
Microsoft SQL Server Native Client 11.0
Cannot use the OUTPUT option when passing a constant to a stored procedure.

I've reviewed the Community posts but don't see anything helpful. 

SQL Server 2016 Stored Procedure declaration:
CREATE PROCEDURE [dbo].[proc_get_default_tax_cd] (
@country_skey int,
@state_skey int,
@county_skey int,
@city varchar(28),
@zip_cd varchar(9),
@tax_cd_out varchar(4) OUTPUT
) AS

PowerBuilder

Instance variables:
string is_in_zip
string is_in_city
string is_in_tax_cd
long il_in_country_skey
long il_in_state_skey
long il_in_county_skey
long il_in_sales_group_skey

PowerScript function:
long ll_sales_group_skey, ll_county_skey
string ls_tax_cd, ls_city

DECLARE lproc_get_tax_cd PROCEDURE FOR dbo.proc_get_default_tax_cd
@country_skey = :il_in_country_skey,
@state_skey = :il_out_state_skey,
@county_skey = :ll_county_skey,
@city = :ls_city,
@zip_cd = :is_in_zip,
@tax_cd_out = :ls_tax_cd OUTPUT
USING SQLCA;
EXECUTE lproc_get_tax_cd;

 

 

 

Miguel Leeuwe Accepted Answer Pending Moderation
  1. Tuesday, 14 April 2020 04:53 AM UTC
  2. PowerBuilder
  3. # 1

Hi Linda,

I don't know how to solve your problem, but I think you have to initialize the string "ls_tax_cd" with 4 spaces or any other characters, since it's a string and my guess is that the OUTPUT value of a varchar(4) will be assigned by reference. If you don't initialize it, the assignment might overwrite some memory causing problems.

regards

Comment
  1. Linda Kelty (Dudzic)
  2. Tuesday, 14 April 2020 14:33 PM UTC
Hi Miguel, it would have been great if that had been it, but unfortunately this did not change the behavior.
  1. Helpful
  1. Miguel Leeuwe
  2. Tuesday, 14 April 2020 15:00 PM UTC
Miguel Leeuwe

Sorry about that Linda, but still it's a very good idea to do something like:

ls_tax_cd = space(4)

.. before calling the stored procedure (even though that's not the problem you're trying to solve here).
  1. Helpful
There are no comments made yet.
Markus Eckert Accepted Answer Pending Moderation
  1. Tuesday, 14 April 2020 12:09 PM UTC
  2. PowerBuilder
  3. # 2

Hi Linda,

This might be a problem with parameter binding.

Can you catch the actual SQL statement your program sends to the DB (for example with SQL Server Profiler)?

Regards,
Markus

Comment
  1. Linda Kelty (Dudzic)
  2. Tuesday, 14 April 2020 14:52 PM UTC
Hi Markus,

Below are the results of SQL Server Profiler when running the scenario from PB. I don't see any indication of failure to bind parameters, but do you?



RPC:Completed - exec [cam_prod].[sys].sp_procedure_params_100_rowset N'[',1,N'PCAdmin',NULL

SQL:BatchStarting - execute [ dbo ].[ proc_get_default_tax_cd ] @country_skey =1 , @state_skey =72 , @county_skey =49 , @city ='Santa Rosa' , @zip_cd ='95407' , @tax_cd_out =' ' OUTPUT

SQL:BatchCompleted - execute [ dbo ].[ proc_get_default_tax_cd ] @country_skey =1 , @state_skey =72 , @county_skey =49 , @city ='Santa Rosa' , @zip_cd ='95407' , @tax_cd_out =' ' OUTPUT

Audit Logout

RPC:Completed - exec sp_reset_connection



The above was the result when executing from PB application. Below, I show the trace result when executing from SSMS. Perhaps you'll see something that would elucidate the problem.



exec sp_executesql N'SELECT

param.name AS [Name],

param.parameter_id AS [ID],

param.default_value AS [DefaultValue],

param.has_default_value AS [HasDefaultValue],

usrt.name AS [DataType],

s1param.name AS [DataTypeSchema],

ISNULL(baset.name, N'''') AS [SystemType],

CAST(CASE WHEN baset.name IN (N''nchar'', N''nvarchar'') AND param.max_length <> -1 THEN param.max_length/2 ELSE param.max_length END AS int) AS [Length],

CAST(param.precision AS int) AS [NumericPrecision],

CAST(param.scale AS int) AS [NumericScale],

ISNULL(xscparam.name, N'''') AS [XmlSchemaNamespace],

ISNULL(s2param.name, N'''') AS [XmlSchemaNamespaceSchema],

ISNULL( (case param.is_xml_document when 1 then 2 else 1 end), 0) AS [XmlDocumentConstraint],

CASE WHEN usrt.is_table_type = 1 THEN N''structured'' ELSE N'''' END AS [UserType],

param.is_output AS [IsOutputParameter],

param.is_cursor_ref AS [IsCursorParameter],

param.is_readonly AS [IsReadOnly],

sp.object_id AS [IDText],

db_name() AS [DatabaseName],

param.name AS [ParamName],

CAST(

case

when sp.is_ms_shipped = 1 then 1

when (

select

major_id

from

sys.extended_properties

where

major_id = sp.object_id and

minor_id = 0 and

class = 1 and

name = N''microsoft_database_tools_support'')

is not null then 1

else 0

end

AS bit) AS [ParentSysObj],

1 AS [Number]

FROM

sys.all_objects AS sp

INNER JOIN sys.all_parameters AS param ON param.object_id=sp.object_id

LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = param.user_type_id

LEFT OUTER JOIN sys.schemas AS s1param ON s1param.schema_id = usrt.schema_id

LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = param.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = param.system_type_id) and (baset.user_type_id = param.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1))

LEFT OUTER JOIN sys.xml_schema_collections AS xscparam ON xscparam.xml_collection_id = param.xml_collection_id

LEFT OUTER JOIN sys.schemas AS s2param ON s2param.schema_id = xscparam.schema_id

WHERE

(param.name=@_msparam_0)and((sp.type = @_msparam_1 OR sp.type = @_msparam_2 OR sp.type=@_msparam_3)and(sp.name=@_msparam_4 and SCHEMA_NAME(sp.schema_id)=@_msparam_5))',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000),@_msparam_5 nvarchar(4000)',@_msparam_0=N'@tax_cd_out',@_msparam_1=N'P',@_msparam_2=N'RF',@_msparam_3=N'PC',@_msparam_4=N'proc_get_default_tax_cd',@_msparam_5=N'dbo'
  1. Helpful
  1. Markus Eckert
  2. Wednesday, 15 April 2020 06:15 AM UTC
Yeah, that here was the problem:



execute [ dbo ].[ proc_get_default_tax_cd ] @country_skey =1 , @state_skey =72 , @county_skey =49 , @city ='Santa Rosa' , @zip_cd ='95407' , @tax_cd_out =' ' OUTPUT



For some reason PB decided to pass a constant to the procedure instead of an actual variable. But converting to an RPC seems to have solved that problem so I guess we can spare the pain going through the connection parameters to try and fix it.
  1. Helpful
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Tuesday, 14 April 2020 15:37 PM UTC
  2. PowerBuilder
  3. # 3

Greetings, Linda - 

I created a test procedure on a SS2017 server/database with the same argument names and datatypes. The test proc sets the value of the OUTPUT var to "ABCD" and returns. In a command button in a window in a PB 2017 R2 test app that uses the SNC DB driver is the following code:

Long   ll_country_skey = 1
Long   ll_state_skey   = 2
Long   ll_county_skey  = 3
String ls_city       = 'Foggey Bog'
String ls_zip_cd     = '9876543321'
String ls_tax_cd_out = '????'

DECLARE lproc_test PROCEDURE FOR dbo.ss_test
   @country_skey = :ll_country_skey,
   @state_skey   = :ll_state_skey,
   @county_skey  = :ll_county_skey,
   @city         = :ls_city,
   @zip_cd       = :ls_zip_cd,
   @tax_cd_out   = :ls_tax_cd_out OUTPUT
   USING SQLCA;

EXECUTE lproc_test;

MessageBox('Execute Results', &
   'SQLDBCode = ' + String(SQLCA.SQLDBCode) + &
   '~r~nTax Code = "' + ls_tax_cd_out + '"')

FETCH lproc_test INTO :ls_tax_cd_out;

MessageBox('Fetch Results', &
   'SQLDBCode = ' + String(SQLCA.SQLDBCode) + &
   '~r~nTax Code = "' + ls_tax_cd_out + '"')

The first MessageBox shows SQLDBCode=0 and Tax Code = "????". After the fetch, the second MessageBox shows SQLDBCode=0 and Tax Code = "ABCD", so everything works correctly for me.

I suspect there may be something funky going on with the values in your instance vars due to the error message text you are receiving. Suggest you try testing with local vars that are hard-coded to values that are known to produce a valid tax code. Also, have execute permission for your stored procedure been granted in SQL Server? Just a thought.

Regards, John

Comment
  1. Linda Kelty (Dudzic)
  2. Tuesday, 14 April 2020 19:29 PM UTC
Hi John, thanks so much for your efforts! Based on your suggestion about instance variables, I tested the other application that uses an OUTPUT parameter, but not with instance variables. Lo and behold, it ran without error!

DECLARE proc_check_digit PROCEDURE FOR dbo.proc_check_digit

@web_id = :ll_web_orderId,

@checkdigit = :ll_check_digit OUTPUT

USING SQLCA;

I'm not going back and changing the first application just to confirm, as I'm on a tight budget, but it's definitely worth checking if anyone else has a similar problem.
  1. Helpful
  1. John Fauss
  2. Tuesday, 14 April 2020 19:38 PM UTC
You're welcome, Linda. I appreciate you letting me know!
  1. Helpful
There are no comments made yet.
Linda Kelty (Dudzic) Accepted Answer Pending Moderation
  1. Tuesday, 14 April 2020 18:16 PM UTC
  2. PowerBuilder
  3. # 4

I resolved the problem by converting to a remote procedure call:

function long proc_get_default_tax_cd(long country_skey,long state_skey,long county_skey,string city,string zip_cd,ref string tax_cd_out) RPCFUNC ALIAS FOR "dbo.proc_get_default_tax_cd"

It was possible to do because there is no result set.

This is the only instance of an OUTPUT parameter in the entire application that has to be fixed (there are 7 other cases but they are in scenarios no longer being used).

There is another PB application in this system that has one currently-used procedure with an OUTPUT parameter. Will convert it as well and post if any problem.

Comment
  1. Miguel Leeuwe
  2. Tuesday, 14 April 2020 18:35 PM UTC
Great news Linda!
  1. Helpful
  1. John Fauss
  2. Tuesday, 14 April 2020 18:46 PM UTC
Super! Glad you found a solution.
  1. Helpful
  1. Linda Kelty (Dudzic)
  2. Wednesday, 15 April 2020 20:18 PM UTC
Converting to a RPC got rid of that original SQL Error message, but now the RPC call returns a different SQL error: SQLDBCode = 999 and SQLerrtext = 'Can not get parameters of procedure dbo.proc_get_default_tax_cd.'.



(My apologies for not catching this earlier. I wasn't checking SQLCA.SQLCode as I should have been. When I began testing again, I noticed the tax_cd value wasn't being displayed, then traced in debugger and saw SQL error.)



I tried the RPC with all local variables, but still got the same (new) error. I'm going back to my original code with local variables to see if I can get that to work.



I will start a new thread for the new message, as it might get overlooked buried in this discussion.
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 14 April 2020 21:52 PM UTC
  2. PowerBuilder
  3. # 5

Hi Linda;

  FYI:  This is the same problem as reported in ticket #3481.

Regards ... Chris

Comment
  1. Linda Kelty (Dudzic)
  2. Wednesday, 15 April 2020 02:40 AM UTC
Hi Chris, thanks for that information. I tried to look it up using Search Bugs, but it says, "No results were found or access is denied." Is this something I should be able to see and could check before posting to the Community? I'm new to Appeon and would like to understand how things work.
  1. Helpful
  1. Miguel Leeuwe
  2. Wednesday, 15 April 2020 07:00 AM UTC
Hi Chris, bug 3481 is either private or doesn't exist.
  1. Helpful
  1. Chris Pollach @Appeon
  2. Thursday, 16 April 2020 16:14 PM UTC
Its marked as "private" by the ticket user who submitted the issue.
  1. Helpful
There are no comments made yet.
Linda Kelty (Dudzic) Accepted Answer Pending Moderation
  1. Thursday, 16 April 2020 14:43 PM UTC
  2. PowerBuilder
  3. # 6

Thanks everyone for your efforts on this. 

The problem was that the user didn't have permission to execute the stored procedure. This became apparent when the OUTPUT parameter (which is required) was absent. It seems that the presence of the OUTPUT keyword caused the PB application (or SQLNCLI11) to overlook the error that should have been returned by the database regarding permissions. Having overlooked the permissions error, the PB application returned a misleading error message. 

Comment
  1. Miguel Leeuwe
  2. Thursday, 16 April 2020 14:59 PM UTC
Great! At least now we know that error messages on sqlserver are crap sometimes :)
  1. Helpful
  1. Arnd Schmidt
  2. Thursday, 16 April 2020 21:24 PM UTC
Outch.... I am not sure if the Microsoft SQL Server is the one to blame.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.