Subscription Synchronization Bug throws a 339 error for a valid operating stored procedure containing a MERGE statement

Question

ERROR THROWN during subscription synchronization: Agent message code 339. DEFAULT or NULL are not allowed as explicit identity values.

Statement that caused the error:

MERGE INTO Individuals i
USING (
SELECT ht.F1 NewSample,ht.F2 DatabaseIndex,ht.F4 CommonDbDate,ht.F5 SpecimenDescription
FROM @HitTable ht left join Samples s on ht.F1=s.SampleName
WHERE s.Sample is null
union SELECT ht.F7 NewSample,ht.F8 DatabaseIndex,ht.F10 CommonDbDate,ht.F11 SpecimenDescription
FROM @HitTable ht left join Samples s on ht.F7=s.SampleName
WHERE s.Sample is null
) h ON 1=0 --always generates "not matched by target"
WHEN NOT MATCHED BY TARGET THEN
INSERT DEFAULT VALUES -- ########## NOTE THAT THIS LINE THROWS THE ERROR BUT WORKS IN PRODUCTION ##########
OUTPUT inserted.Individual,h.NewSample,h.DatabaseIndex,h.SpecimenDescription,h.CommonDbDate
INTO @Samples (Individual,SampleName,DatabaseIndex,SpecimenDescription,CommonDbDate);

Answer 1

I guess the problem is that the subscriber issues SET IDENTITY_INSERT ON. That is certainly the gist of the error message. When replicating a table with IDENTITY columns, you want the IDENTITY values from the published to appear at the subscriber. You certainly do not want new values.

I think the solution is to change the table so that the IDENTITY column is created as

CREATE TABLE tbl (id int IDENTITY NOT FOR REPLICATION NOT NULL,
                        othercol int NOT NULL)

That is, on the subscriber, the table should not have the IDENTITY property.