Log Reader Error On Adding Subscription

Ever tried to add a new subscription to an existing publication, using the "replication support only" or "initialize from backup" methods and go the following error?

The process could not execute 'sp_MSadd_replcmds' on <'Distribution Server>'. (Source: MSSQLServer, Error number: 1007)
Conversion failed when converting the nvarchar value '.' to data type int. (Source: MSSQLServer, Error number: 1007)
Batches were not committed to the Distributor. (Source: MSSQL_REPL, Error number: MSSQL_REPL22020)
The last step did not log any message! (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)

If so, you will know the internet is absolutely useless, filled with worthless workarounds or advice to just "rebuild replication"

Well, if you want an actual solution, just read on. Between SQL 2012 RTM and SQL 2012 SP4 there was a change made to the Distributor proc sp_MSadd_replcmds. This change was to facilitate a change in the format of log buffer, probably related to the number of publications you can have.

In the RTM version, the code that actually fails is as follows

select @originator_publication_id = CAST(CAST(substring(@cmdText, 1, 4) AS nvarchar) AS int),
    @next_valid_lsn_from_log = CAST(substring(@cmdText, 5, 10) AS binary(10))

In 2012 SP4 this becomes

-- if most high bit is set to 1 then interprete buffer as a new format 99 <= 0xFFFFFF9D (supports 2^32 ids), otherwise it's a backward compatibility for 99 <= ca (this is max id)
select @originator_publication_id = CASE WHEN CAST(substring(@cmdText, 1, 4) AS int) <= 0 THEN -CAST(substring(@cmdText, 1, 4) AS int) ELSE CAST(CAST(substring(@cmdText, 1, 4) AS nvarchar) AS int) END,
    @next_valid_lsn_from_log = CAST(substring(@cmdText, 5, 10) AS binary(10))

You can see now why you get the error in the log reader. As far as I can tell, having the publisher ahead of the distributor on point releases is what causes this - this makes sense since it is the log reader that fails. I don't believe the version of the subscriber has any effect here.

Anyway so now you know - always make sure you distributor patches in lock step with your publisher, even if the major version is the same.