Could not find stored procedure dbo.sp_MScheckIsPubOfSub
Published: 3rd May 2013
I was working on one site when we got a very strange replication error appear. I’ve still no idea what caused it, but that won’t stop me posting the error message itself along with the simple solution…

This was the error…

Here’s the code for the proc. Run it in the database that’s throwing the error…

create procedure dbo.sp_MScheckIsPubOfSub
    @pubid uniqueidentifier,
    @subid uniqueidentifier,
    @pubOfSub bit output
as
    declare @retcode int
    -- Security check
    exec @retcode = sys.sp_MSrepl_PAL_rolecheck
    if (@retcode <> 0) or (@@error <> 0)
        return 1

    if exists
    (
        select sms_a.subid
        from sysmergesubscriptions as sms_a
        where pubid in
        (
            select smp.pubid
            from sysmergesubscriptions as sms
            join sysmergepublications as smp
            on sms.subscriber_server = smp.publisher
            and sms.db_name = smp.publisher_db
            where sms.subid = @pubid
        )
        and sms_a.subid <> sms_a.pubid
        and sms_a.subid = @subid
    )
    begin
        select @pubOfSub = 1
    end
    else
    begin
        select @pubOfSub = 0
    end
    return 0
go
Comments:
NB: Comments will only appear once they have been moderated.