Bug inside sp_scriptdynamicupdproc ?

Question

I have a transactional replication and I wanted to use a custom MCALL type of update on a table.
I called the stored procedure sp_scriptdynamicupdproc with argument artid and I got the custom script.

Custom script started with declaration of all arguments:

create procedure [__dbo_tablename_upd]
@c1 varchar(50),@c2 int,@c3 varchar(10),@c4 datetime,@c5 varchar(100),
@c6 varchar(500),@c7 varchar(500),@c8 datetime,@c9 varchar(100),
@c10 varchar(500),@c11 varchar(500),@c12 bit,@Curtis Asare datetime,
@c14 bit,@c15 bit, @c16 varchar(50), @c17 smallint,@c18 int,
@c19 varchar(400),@c20 datetime,@c21 bit,
@pkc1 bigint
,@bitmap binary(3)
as ...

after that, in each line it's using a bitmask to check if value was passed and adds a code snippet for each column:

......
if substring(@bitmap,2,1) & 64 = 64
begin
select @stmt = @stmt + @spacervar + N'[IsIndividualFileDownloadRequired]' + N'=@15'
select @spacervar = N','
end
if substring(@bitmap,2,1) & 128 = 128
begin
select @stmt = @stmt + @spacervar + N'[StatementType]' + N'=@16'
select @spacervar = N','
end
if substring(@bitmap,3,1) & 1 = 1
begin
select @stmt = @stmt + @spacervar + N'[DocumentTypeID]' + N'=@17'
select @spacervar = N','
end
.....

So variable @16 in my case is 16th column called StatementType, and then in the bottom in the where clause it's
(wrongly) using this same variable as primary key for update

if substring(@bitmap,3,1) & 16 = 16
begin
select @stmt = @stmt + @spacervar + N'[IsValid]' + N'=@21'
select @spacervar = N','
end
select @stmt = @stmt + N'
where [AccessionNumberBI] = @16
'

and in the part used for sp_executesql the declaration is duplicated:

exec sp_executesql @stmt, N'
....
,@13 datetime
,@14 bit
,@15 bit
,@16 bigint
,@16 varchar(50)
,@17 smallint
,@18 int

and below, in the place where actual parameters are passed I have:

,@Curtis Asare
,@c14
,@c15
,@pkc1
,@c16
,@c17
,@c18

The custom script has a wrongly populated argument. How is this even possible?

Answer 1

I have a strange feeling that this usage of computed and persisted primary key was never tested and this is a bug from long time ago...

... and in combination with the dropped columns. I'm not really holding my breath that that apply the CU will resolve the issue. But you should do that nevertheless.

Anyway, if this is a non-blocking issue for you, that is, you have a workaround, you can file a bug on https://feedback.azure.com/d365community/forum/04fe6ee0-3b25-ec11-b6e6-000d3a4f0da0 to let Microsoft be aware of the issue. I would encourage you to spend the time to produce a script that reproduces the issue. Without such a repro Microsoft cannot do much about it.

If this is a blocking issue for you, you will need to open a support case. And if you do, the first thing the support engineer will tell you is to apply the most recent CU...

Answer 2

Test Case, your file upload form didn't work...

https://www78.zippyshare.com/v/Xd8Qy43v/file.html