Great! At least now we know that error messages on sqlserver are crap sometimes :)

Outch.... I am not sure if the Microsoft SQL Server is the one to blame.

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.

Hi Chris, bug 3481 is either private or doesn't exist.

Its marked as "private" by the ticket user who submitted the issue.

Great news Linda!

Super! Glad you found a solution.

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.

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.

You're welcome, Linda. I appreciate you letting me know!

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'

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.

Hi Miguel, it would have been great if that had been it, but unfortunately this did not change the behavior.

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).