I'm also getting the "Mismatch between retrieve columns and fetch columns" error when calling a sql server proc with output parm. These are the particulars:

PB 2017 R2

See attached for SQL Server specs

It's important to note that the PB app has other stored proc calls with same format as this call and they work fine.

PB Code:

Var li_update is integer

// Run the stored proc to reprocess if update was successful.
// The proc returns 1 for success and 0 for failure in OUTPUT parm.
// The proc does its own transaction processing.
DECLARE ap_Meter_Ticket_Process PROCEDURE FOR dbo.ap_Meter_Ticket_Process
@IsReprocess = :ls_fl_reprocess,
@Id_user = :ls_id_user,
@rtn = :li_update OUTPUT;

EXECUTE ap_Meter_Ticket_Process;

if sqlca.sqlcode = 0 then
// Retrieve proc return code
FETCH ap_Meter_Ticket_Process INTO :li_update;

if sqlca.sqlcode <> 0 then
// Fetch Failure
li_update = -1
ls_sql_msg = sqlca.sqlerrtext
else
// Fetch Success
if li_update = 0 then
// Reprocess failed
ls_sql_msg = "Refer to email for failure details"
end if
end if
else
li_update = -1
ls_sql_msg = sqlca.sqlerrtext
end if

CLOSE ap_Meter_Ticket_Process;

SQL Server Code:

All parms have a default value as the proc is called from a SQL Agent job without parms

Var @rtn defaults to 1 for success and if the proc encounters any error situations, @rtn gets set to 0 for failure

ALTER proc [dbo].[ap_Meter_Ticket_Process]
(
@IsReprocess varchar(1) = 'N',
@Id_user varchar(30) = 'NONE',
@rtn int = 1 OUTPUT
)

Any help is appreciated

Pete

Ask your DBA to check your SQL Server Login properties, General Tab, at the bottom you will see the default database assignment for the Login:

FYI - In our commercial application, we ALWAYS explicitly preface table names with "dbo." in DataWindow SELECT statements, embedded SQL, triggers, stored procedures, views, etc.

Hi Raymond;

   Yes, Microsoft's MSS and SNC DB Client's are a bit different technically under the hood. I suspect that it's the DW that's performing the update where the "Update Property" setting is conflicting against SS's locking mechanism. I would suggest looking at the table being updated and the column definitions used for the primary key and unique index columns that could be creating the DML error.

Regards ... Chris