create procedure sys.sp_MSget_effective_pub_compat_level (
    @pubid uniqueidentifier,
    @publication_compatibility_level int output,
    @feature_name nvarchar(4000) output,
    @buildnumber nvarchar(128)= null output
) AS
    declare @retcode int
    declare @requires_90 bit
    declare @requires_80 bit
    declare @sync_mode int
    declare @buildnumber80 int
    declare @buildnumber90 int

    set @buildnumber80= 0
    set @buildnumber90= 0
    select @requires_80 = 0
    select @requires_90 = 0
    select @feature_name = N''

    if exists (select * from dbo.sysmergearticles where pubid = @pubid and (upload_options = 1 or upload_options = 2))
    begin
        select @requires_90 = 1
        select @feature_name = N' ' + formatmessage(22563) + N' ' --' contains one or more articles that do not upload '
        goto DONE
    end

    if exists (select * from dbo.sysmergepublications where pubid = @pubid and replicate_ddl <> 0)
    begin
        select @requires_90 = 1
        select @feature_name = N' ' + formatmessage(22564) + N' ' --' uses ddl replication '
        goto DONE
    end

    if exists (select * from dbo.sysmergepublications where pubid = @pubid and retention_period_unit <> 0)
    begin
        select @requires_90 = 1
        select @feature_name = N' ' + formatmessage(22565) + N' ' --' uses other than day as unit of retention period '
        goto DONE
    end

    if exists (select * from dbo.sysmergepartitioninfo where pubid=@pubid and
                (logical_record_level_conflict_detection=1 or logical_record_level_conflict_resolution=1))
        or exists (select * from dbo.sysmergesubsetfilters where pubid = @pubid
                and filter_type & 2 = 2)
    begin
        select @requires_90 = 1
        select @feature_name = N' ' + formatmessage(22566) + N' ' --' uses logical records '
        goto DONE
    end

    if exists (select * from dbo.sysmergepartitioninfo where pubid=@pubid and partition_options>1)
    begin
        select @requires_90 = 1
        select @feature_name = N' ' + formatmessage(22567) + N' ' --' contains one or more well partitioned articles '
        goto DONE
    end

    if exists (select * from dbo.sysmergearticles where pubid = @pubid and compensate_for_errors=0)
    begin
        select @requires_80 = 1
        if @requires_90 = 0 and @buildnumber80 < 858
        begin
            set @buildnumber80= 858
            set @buildnumber= N'8.00.0858'
        end
        select @feature_name = N' ' + formatmessage(22568) + N' ' --' contains one or more articles which will not compensate for errors '
        goto DONE
    end

    if exists (select * from dbo.sysmergeschemaarticles where pubid = @pubid)
    begin
        select @requires_80 = 1
        select @feature_name = N' ' + formatmessage(22569) + N' ' --' contains one or more schema only articles '
        goto DONE
    end

    if exists (select * from dbo.sysmergearticles where pubid = @pubid and identity_support=1)
    begin
        select @requires_80 = 1
        select @feature_name = N' ' + formatmessage(22570) + N' ' --' contains one or more articles that use automatic identity range management '
        goto DONE
    end

    select @sync_mode = sync_mode from dbo.sysmergepublications where pubid = @pubid

    if @sync_mode=0 and
        exists (select c.name from sys.columns c, dbo.sysmergearticles a
                where a.pubid = @pubid and c.object_id = a.sync_objid and
                      (c.system_type_id = type_id('bigint') or c.system_type_id = type_id('sql_variant')))
    begin
        select @requires_80 = 1
        select @feature_name = N' ' + formatmessage(22571) + N' ' --' contains one or more articles that use datatypes new in SQL server 2000 '
        goto DONE
    end

    if exists (select c.name from sys.columns c, dbo.sysmergearticles a
                where a.pubid = @pubid and c.object_id = a.sync_objid and
                      c.system_type_id = type_id('timestamp'))
    begin
        select @requires_80 = 1
        select @feature_name = N' ' + formatmessage(22572) + N' ' --' contains one or more articles with a timestamp column '
        goto DONE
    end

    -- TODO: need to add schema option checking. Skipping for now since it is too complicated.

    if exists (select * from dbo.sysmergepublications where pubid=@pubid and snapshot_in_defaultfolder = 0 and compress_snapshot = 1)
    begin
        select @requires_80 = 1
        select @feature_name = N' ' + formatmessage(22573) + N' ' --' uses snapshot compression with snapshot_in_defaultfolder set to false '
        goto DONE
    end

    -- the following implies that we have one or more articles that use vertical partitioning
    if exists (select c.name from sys.columns c, dbo.sysmergearticles a where a.pubid = @pubid and c.object_id = a.objid and
                c.is_computed<>1 and c.system_type_id<> type_id('timestamp') and
                c.name not in (select c.name from sys.columns c, dbo.sysmergearticles a
                              where a.pubid = @pubid and c.object_id=a.sync_objid))
    begin
        select @requires_80 = 1
        select @feature_name = N' ' + formatmessage(22574) + N' ' --' contains one or more articles that use vertical partitioning '
        goto DONE
    end

    -- TODO: how do we take care of reinit. If we have to use reinit the pub compat level has to be 30


DONE:
    if @requires_90 = 1
        select @publication_compatibility_level = 90
    else if @requires_80 = 1
        select @publication_compatibility_level = 40
    else
        select @publication_compatibility_level = 30