/*
** repltran.sql            1997/02/12 22:03
**
**
** Copyright Microsoft, Inc. 1998-2000
** All Rights Reserved.
*/


use master
go

dump tran master with no_log
go

exec dbo.sp_configure 'update',1
go
reconfigure with override
go

set ANSI_NULLS off
go

-- sp_MS_upd_sysobj_category is obsolete, use sp_MS_marksystemobject instead
-- exec dbo.sp_MS_upd_sysobj_category 1 --Capture time for use at the end
-- go

if exists (select * from sysobjects
    where type = 'P '
            and name = 'sp_MSdrop_repltran')
begin
    drop procedure sp_MSdrop_repltran
end

/*
** Create stored procedures to drop the stored procedures
** created by this script
*/
 

print ''
print 'Creating procedure sp_MSdrop_repl_tran.'
go
create procedure sp_MSdrop_repltran
as

    if exists( select * from sysobjects 
        where type = 'P '
            and name = 'sp_MSsetfilterparent' )
        drop procedure sp_MSsetfilterparent

    if exists( select * from sysobjects 
        where type = 'P '
            and name = 'sp_MSdoesfilterhaveparent' )
        drop procedure sp_MSdoesfilterhaveparent

    if exists( select * from sysobjects 
        where type = 'P '
            and name = 'sp_MSsetfilteredstatus' )
        drop procedure sp_MSsetfilteredstatus

    if exists ( select * from sysobjects 
        where type = 'P '
            and name = 'sp_MSreplsup_table_has_pk' )
        DROP PROC sp_MSreplsup_table_has_pk

    if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MScreate_pub_tables')
        drop procedure sp_MScreate_pub_tables

    if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSdrop_expired_subscription')
        drop procedure sp_MSdrop_expired_subscription
    
    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_replsync')
        drop procedure sp_replsync

    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_enumfullsubscribers')
        drop procedure sp_enumfullsubscribers
    
    if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSaddexecarticle')
        drop procedure sp_MSaddexecarticle
    

    dump tran master with no_log
    

    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_addarticle')
        drop procedure sp_addarticle
    
    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_MSaddschemaarticle')
        drop procedure sp_MSaddschemaarticle

    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_MSchangeschemaarticle')
        drop procedure sp_MSchangeschemaarticle

    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_MSgettranconflictname')
        drop procedure sp_MSgettranconflictname    

    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_MSretrieve_publication')
        drop procedure sp_MSretrieve_publication    

    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_MSmaketrancftproc')
        drop procedure sp_MSmaketrancftproc    

    if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_articlecolumn')
        drop procedure sp_articlecolumn
    
    dump tran master with no_log
    

    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_articlefilter')
        drop procedure sp_articlefilter
       


    if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSarticlecol')
        drop procedure sp_MSarticlecol
    

    dump tran master with no_log
    

    if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSarticlecolstatus')
        drop procedure sp_MSarticlecolstatus
    
    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_MSscript_article_view')
        drop procedure sp_MSscript_article_view 

    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_articleview')
        drop procedure sp_articleview
    

    dump tran master with no_log
    

    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_addpublication')
        drop procedure sp_addpublication
    

    dump tran master with no_log
    

    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_addsubscription')
        drop procedure sp_addsubscription
    

    IF EXISTS (SELECT * FROM sysobjects
            WHERE type = 'P '
                AND name = 'sp_changearticle')
        DROP PROCEDURE sp_changearticle
    

    dump tran master with no_log
    

    IF EXISTS (SELECT * FROM sysobjects
            WHERE type = 'P '
                AND name = 'sp_changepublication')
        DROP PROCEDURE sp_changepublication
    


    IF EXISTS (SELECT * FROM sysobjects
            WHERE type = 'P '
                AND name = 'sp_changesubscription')
        DROP PROCEDURE sp_changesubscription
    

    dump tran master with no_log
    

    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_MShcchangesubstatus1')
        drop procedure sp_MShcchangesubstatus1
    

    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_MShcchangesubstatus2')
        drop procedure sp_MShcchangesubstatus2
    

    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_MShcchangesubstatus3')
        drop procedure sp_MShcchangesubstatus3
    

    dump tran master with no_log
    

    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_changesubstatus')
        drop procedure sp_changesubstatus
    

    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_droparticle')
        drop procedure sp_droparticle
    

    dump tran master with no_log
    

    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_droppublication')
        drop procedure sp_droppublication
    

    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_dropsubscription')
        drop procedure sp_dropsubscription
    

    dump tran master with no_log
    

    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_helparticle')
        drop procedure sp_helparticle
    

    dump tran master with no_log
    

    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_helparticlecolumns')
        drop procedure sp_helparticlecolumns
    

    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_helppublication')
        drop procedure sp_helppublication
    
	    
    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_MSaddpub_snapshot')
        drop procedure sp_MSaddpub_snapshot

    dump tran master with no_log
    

    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_helpsubscription')
        drop procedure sp_helpsubscription
    

    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_subscribe')
        drop procedure sp_subscribe
    

    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_unsubscribe')
        drop procedure sp_unsubscribe
    

    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_refreshsubscriptions')
        drop procedure sp_refreshsubscriptions
    

    dump tran master with no_log
    

    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_MSpublishdb')
        drop procedure sp_MSpublishdb
    

    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_MSactivate_auto_sub')
        drop procedure sp_MSactivate_auto_sub
    

    if exists (select * from sysobjects
            where type = 'P '
                and name = 'sp_MSget_synctran_commands')
        drop procedure sp_MSget_synctran_commands


    if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSdrop_pub_tables')
    begin
        -- Don't drop the system tables here. repltran.sql should not
        -- delete any data in the master database.
        -- exec dbo.sp_MSdrop_pub_tables
        drop procedure sp_MSdrop_pub_tables
    end

    -- SyncTran
    if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSis_col_replicated')
        drop procedure  sp_MSis_col_replicated

    if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSis_pk_col')
        drop procedure  sp_MSis_pk_col

   if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSscript_compensating_send')
        drop procedure sp_MSscript_compensating_send 

   if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSscript_insert_statement')
        drop procedure sp_MSscript_insert_statement 

	if exists (select * from sysobjects
	    where type = 'P' and name = 'sp_script_insertforcftresolution')
	drop procedure sp_script_insertforcftresolution

   if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSscript_insert_subwins')
        drop procedure sp_MSscript_insert_subwins 

   if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSscript_insert_pubwins')
        drop procedure sp_MSscript_insert_pubwins 

   if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSscript_validate_subscription')
        drop procedure sp_MSscript_validate_subscription 

   if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSvalidate_subscription')
        drop procedure sp_MSvalidate_subscription 

   if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSscript_update_statement')
        drop procedure sp_MSscript_update_statement

   if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSscript_update_subwins')
        drop procedure sp_MSscript_update_subwins 

   if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSscript_update_pubwins')
        drop procedure sp_MSscript_update_pubwins 

   if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSscriptinsertconflictfinder')                
        drop procedure sp_MSscriptinsertconflictfinder
        
   if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSscriptupdateconflictfinder')
        drop procedure sp_MSscriptupdateconflictfinder

   if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSscriptdelconflictfinder')                
        drop procedure sp_MSscriptdelconflictfinder

   if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_scriptpubwinsrefreshcursorvars')                
        drop procedure sp_scriptpubwinsrefreshcursorvars
        
   if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSscript_delete_statement')
        drop procedure sp_MSscript_delete_statement

   if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSscript_delete_subwins')
        drop procedure sp_MSscript_delete_subwins 

   if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSscript_compensating_insert')
        drop procedure sp_MSscript_compensating_insert 

   if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSscript_delete_pubwins')
        drop procedure sp_MSscript_delete_pubwins 

   if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSscript_beginproc')
        drop procedure sp_MSscript_beginproc

   if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSscript_security')
        drop procedure sp_MSscript_security

   if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSscript_endproc')
        drop procedure sp_MSscript_endproc

  if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MStable_not_modifiable')
        drop procedure sp_MStable_not_modifiable

	if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSscript_ExecutionMode_stmt')
        drop procedure sp_MSscript_ExecutionMode_stmt

	if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSscript_sync_ins_proc')
        drop procedure sp_MSscript_sync_ins_proc

   if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSscript_sync_upd_proc')
        drop procedure sp_MSscript_sync_upd_proc 

   if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSscript_sync_del_proc')
        drop procedure sp_MSscript_sync_del_proc

   if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSscript_pub_upd_trig')
        drop procedure sp_MSscript_pub_upd_trig

   if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSmakeconflicttable')
        drop procedure sp_MSmakeconflicttable 

   if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_scriptsubconflicttable')
        drop procedure sp_scriptsubconflicttable 

   if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSgen_sync_tran_procs')
        drop procedure sp_MSgen_sync_tran_procs 

   if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSis_identity_insert')
        drop procedure sp_MSis_identity_insert 

    if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_MSmark_proc_norepl')
        drop procedure sp_MSmark_proc_norepl 

    if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_articlesynctranprocs')
        drop procedure sp_articlesynctranprocs

    if exists (select * from sysobjects
        where type = 'P '
                and name = 'sp_reinitsubscription')
        drop procedure sp_reinitsubscription

    dump tran master with no_log

    if exists( select * from sysobjects 
        where type = 'P ' and name = N'sp_MSareallcolumnscomputed')
    begin
        drop procedure sp_MSareallcolumnscomputed
    end

    if exists( select * from sysobjects 
        where type = 'P ' and name = N'sp_gettypestring' )
    begin
        drop procedure sp_gettypestring
    end

    if exists( select * from sysobjects 
        where type = 'P ' and name = N'sp_MSgettypestringudt' )
    begin
        drop procedure sp_MSgettypestringudt
    end

    if exists( select * from sysobjects 
        where type = 'P ' and name = N'sp_scriptpkwhereclause' )
    begin
        drop procedure sp_scriptpkwhereclause
    end

    if exists( select * from sysobjects 
        where type = 'P ' and name = N'sp_MSscript_missing_row_check' )
    begin
        drop procedure sp_MSscript_missing_row_check
    end

    if exists( select * from sysobjects 
        where type = 'P ' and name = N'sp_scriptupdateparams' )
    begin
        drop procedure sp_scriptupdateparams
    end

    if exists( select * from sysobjects 
        where type = 'P ' and name = N'sp_scriptreconwhereclause' )
    begin
        drop procedure sp_scriptreconwhereclause
    end

    if exists( select * from sysobjects 
        where type = 'P ' and name = N'sp_script_reconciliation_insproc' )
    begin
        drop procedure sp_script_reconciliation_insproc
    end

    if exists( select * from sysobjects 
        where type = 'P ' and name = N'sp_script_reconciliation_delproc' )
    begin
        drop procedure sp_script_reconciliation_delproc
    end

    if exists( select * from sysobjects 
        where type = 'P ' and name = N'sp_script_reconciliation_xdelproc' )
    begin
        drop procedure sp_script_reconciliation_xdelproc
    end

    if exists( select * from sysobjects 
        where type = 'P ' and name = N'sp_scriptinsproc' )
    begin
        drop procedure sp_scriptinsproc
    end

    if exists( select * from sysobjects 
        where type = 'P ' and name = N'sp_scriptdelproc' )
    begin
        drop procedure sp_scriptdelproc
    end

    if exists( select * from sysobjects 
        where type = 'P ' and name = N'sp_scriptxdelproc' )
    begin
        drop procedure sp_scriptxdelproc
    end

    if exists( select * from sysobjects 
        where type = 'P ' and name = N'sp_scriptupdproc' )
    begin
        drop procedure sp_scriptupdproc
    end

    if exists( select * from sysobjects 
        where type = 'P ' and name = N'sp_scriptxupdproc' )
    begin
        drop procedure sp_scriptxupdproc
    end

    if exists ( select * from sysobjects 
        where type = 'P ' and name = 'sp_scriptmappedupdproc' )
    begin
        drop procedure sp_scriptmappedupdproc
    end

    if exists ( select * from sysobjects 
        where type = 'P ' and name = 'sp_scriptdynamicupdproc' )
    begin
        drop procedure sp_scriptdynamicupdproc
    end

    if exists ( select * from sysobjects 
        where type = 'P ' and name = 'sp_MSscriptmvastablenci' )
    begin
        drop procedure sp_MSscriptmvastablenci
    end

    if exists ( select * from sysobjects 
        where type = 'P ' and name = 'sp_MSscriptmvastablepkc' )
    begin
        drop procedure sp_MSscriptmvastablepkc
    end

    if exists ( select * from sysobjects 
        where type = 'P ' and name = 'sp_MSscriptmvastableidx' )
    begin
        drop procedure sp_MSscriptmvastableidx
    end

    if exists ( select * from sysobjects 
        where type = 'P ' and name = 'sp_MSscriptmvastable' )
    begin
        drop procedure sp_MSscriptmvastable
    end

    if exists ( select * from sysobjects 
        where type = 'P ' and name = 'sp_fetchshowcmdsinput' )
    begin
        drop procedure sp_fetchshowcmdsinput
    end

    if exists ( select * from sysobjects 
        where type = 'P ' and name = 'sp_replshowcmds' )
    begin
        drop procedure sp_replshowcmds
    end

    if exists ( select * from sysobjects 
        where type = 'P ' and name = 'sp_publication_validation' )
    begin
        drop procedure sp_publication_validation
    end

    if exists ( select * from sysobjects 
        where type = 'P ' and name = 'sp_marksubscriptionvalidation' )
    begin
        drop procedure sp_marksubscriptionvalidation
    end

    if exists ( select * from sysobjects 
        where type = 'P ' and name = 'sp_article_validation' )
    begin
        drop procedure sp_article_validation
    end

    if exists ( select * from sysobjects 
        where type = 'P ' and name = 'sp_MSdrop_6x_replication_agent' )
    begin
        drop procedure sp_MSdrop_6x_replication_agent
    end

    if exists ( select * from sysobjects 
        where type = 'P ' and name = 'sp_MSget_article_column_list' )
    begin
        drop procedure sp_MSget_article_column_list
    end

    if exists ( select * from sysobjects 
        where type = 'P ' and name = 'sp_MSpub_adjust_identity' )
    begin
        drop procedure sp_MSpub_adjust_identity
    end

    if exists ( select * from sysobjects 
        where type = 'P ' and name = 'sp_helparticledts' )
    begin
        drop procedure sp_helparticledts
    end

    if exists ( select * from sysobjects 
        where type = 'P ' and name = 'sp_changesubscriptiondtsinfo' )
    begin
        drop procedure sp_changesubscriptiondtsinfo
    end

    if exists ( select * from sysobjects 
        where type = 'P ' and name = 'sp_script_synctran_commands' )
    begin
        drop procedure sp_script_synctran_commands
    end

    if exists ( select * from sysobjects 
        where type = 'P ' and name = 'sp_MScomputearticlescreationorder' )
    begin
        drop procedure sp_MScomputearticlescreationorder
    end

    if exists ( select * from sysobjects 
        where type = 'P ' and name = 'sp_MScomputeunresolvedrefs' )
    begin
        drop procedure sp_MScomputeunresolvedrefs
    end

    if exists ( select * from sysobjects 
	        where type = 'P ' and name = 'sp_MShelptranconflictpublications' )
        drop procedure sp_MShelptranconflictpublications

    if exists ( select * from sysobjects 
	        where type = 'P ' and name = 'sp_MShelptranconflictcounts' )
        drop procedure sp_MShelptranconflictcounts

    if exists ( select * from sysobjects 
	        where type = 'P ' and name = 'sp_MSgettranconflictrow' )
        drop procedure sp_MSgettranconflictrow

    if exists ( select * from sysobjects 
	        where type = 'P ' and name = 'sp_MSgettrancftsrcrow' )
        drop procedure sp_MSgettrancftsrcrow

    if exists ( select * from sysobjects 
	        where type = 'P ' and name = 'sp_MSdeletetranconflictrow' )
        drop procedure sp_MSdeletetranconflictrow

    if exists ( select * from sysobjects
            where type = 'P ' and name = 'sp_MSexternalfkreferences' )
        drop procedure sp_MSexternalfkreferences    

	if exists ( select * from sysobjects
            where type = 'P ' and name = 'sp_MSgetarticlereinitvalue' )
        drop procedure sp_MSgetarticlereinitvalue    

	if exists ( select * from sysobjects
            where type = 'P ' and name = 'sp_MSispkupdateinconflict' )
        drop procedure sp_MSispkupdateinconflict    

	if exists ( select * from sysobjects
            where type = 'P ' and name = 'sp_MSisnonpkukupdateinconflict' )
        drop procedure sp_MSisnonpkukupdateinconflict    

	if exists ( select * from sysobjects
            where type = 'P ' and name = 'sp_dropanonymousagent' )
        drop procedure sp_dropanonymousagent    

	if exists ( select * from sysobjects
            where type = 'P ' and name = 'sp_ivindexhasnullcols' )
        drop procedure sp_ivindexhasnullcols    

	if exists (select * from sysobjects
			where type = 'FN' and name = 'fn_sqlvarbasetostr')
	     drop function fn_sqlvarbasetostr

	if exists ( select * from sysobjects
            where type = 'P ' and name = 'sp_replrestart' )
        drop procedure sp_replrestart    

	if exists ( select * from sysobjects
            where type = 'P ' and name = 'sp_MSreinit_article' )
        drop procedure sp_MSreinit_article    

    if exists ( select * from sysobjects
            where type = 'P ' and name = 'sp_replqueuemonitor' )
        drop procedure sp_replqueuemonitor    

    if exists ( select * from sysobjects
            where type = 'P ' and name = 'sp_replsqlqgetrows' )
        drop procedure sp_replsqlqgetrows

	if exists (select * from sysobjects
	    where type = 'P' and name = 'sp_repldeletequeuedtran')
	drop procedure sp_repldeletequeuedtran

	if exists ( select * from sysobjects
            where type = 'P ' and name = 'sp_MSrepl_schema' )
        drop procedure sp_MSrepl_schema    

	if exists ( select * from sysobjects
            where type = 'P ' and name = 'sp_MSpost_auto_proc' )
        drop procedure sp_MSpost_auto_proc    

	if exists ( select * from sysobjects
            where type = 'P ' and name = 'sp_MSreplupdateschema' )
        drop procedure sp_MSreplupdateschema    

	if exists ( select * from sysobjects
            where type = 'P ' and name = 'sp_MSdefer_check' )
        drop procedure sp_MSdefer_check    

	if exists ( select * from sysobjects
            where type = 'P ' and name = 'sp_MSreenable_check' )
        drop procedure sp_MSreenable_check    

	if exists ( select * from sysobjects
            where type = 'P ' and name = 'sp_getqueuedrows' )
        drop procedure sp_getqueuedrows    

	if exists ( select * from sysobjects
            where type = 'P ' and name = 'sp_MSprep_exclusive' )
        drop procedure sp_MSprep_exclusive    

	if exists ( select * from sysobjects
	    where type = 'P ' and name = 'sp_verify_publication' )
	drop procedure sp_verify_publication

    if exists ( select * from sysobjects
        where type = 'P ' and name = 'sp_scriptpublicationcustomprocs')
    drop procedure sp_scriptpublicationcustomprocs

    if exists (select * from sysobjects
        where type = 'P' and name = 'sp_repltablehasnonpkuniquekey')
    drop procedure sp_repltablehasnonpkuniquekey

    if exists (select * from sysobjects
        where type = 'P' and name = 'sp_replscriptuniquekeywhereclause')
    drop procedure sp_replscriptuniquekeywhereclause

    if exists (select * from sysobjects
        where type = 'P' and name = 'sp_getqueuedarticlesynctraninfo')
    drop procedure sp_getqueuedarticlesynctraninfo

    if exists (select * from sysobjects
        where type = 'P' and name = 'sp_getsqlqueueversion')
    drop procedure sp_getsqlqueueversion

	dump tran master with no_log
go
 
EXEC dbo.sp_MS_marksystemobject sp_MSdrop_repltran
GO

EXEC dbo.sp_MSdrop_repltran
GO

/* Create and execute dbo.sp_MSdrop_pub_tables */

print ''
print 'Creating procedure sp_MSdrop_pub_tables'
go
CREATE PROCEDURE sp_MSdrop_pub_tables
AS
        
    begin tran
    save TRAN sp_drop_central_pub_tables
    
    /* drop 'sysarticles' */
    IF exists (select * from sysobjects where name = 'sysarticles')
    BEGIN
        DROP TABLE sysarticles
        IF @@ERROR <> 0
        BEGIN
            if @@trancount > 0
            begin
                ROLLBACK TRAN sp_drop_central_pub_tables
                commit tran
            end
            RETURN(1)
        END
    END

    /* drop 'sysschemaarticles' */
    IF exists (select * from sysobjects where name = 'sysschemaarticles')
    BEGIN
        DROP TABLE sysschemaarticles
        IF @@ERROR <> 0
        BEGIN
            if @@trancount > 0
            begin
                ROLLBACK TRAN sp_drop_central_pub_tables
                commit tran
            end
            RETURN (1)
        END
    END

    /* drop 'sysextendedarticlesview' */
    IF exists (select * from sysobjects where name = 'sysextendedarticlesview')
    BEGIN
        DROP VIEW sysextendedarticlesview
        IF @@ERROR <> 0
        BEGIN
            if @@trancount > 0
            begin
                ROLLBACK TRAN sp_drop_central_pub_tables
                commit tran
            end
            RETURN (1)
        END
    END


    /* drop 'syspublications' */
    IF exists (select * from sysobjects where name = 'syspublications')
    BEGIN
        DROP TABLE syspublications
        IF @@ERROR <> 0
        BEGIN
            if @@trancount > 0
            begin
                ROLLBACK TRAN sp_drop_central_pub_tables
                commit tran
            end
            RETURN(1)
        END
    END

    /* drop 'syssubscriptions' */
    IF exists (select * from sysobjects where name = 'syssubscriptions')
    BEGIN
        DROP TABLE syssubscriptions
        IF @@ERROR <> 0
        BEGIN
            if @@trancount > 0
            begin
                ROLLBACK TRAN sp_drop_central_pub_tables
                commit tran
            end
            RETURN(1)
        END
    END

    -- SyncTran
    /* drop 'sysarticleupdates' */
    IF exists (select * from sysobjects where name = 'sysarticleupdates')
    BEGIN
        DROP TABLE sysarticleupdates
        IF @@ERROR <> 0
        BEGIN
            if @@trancount > 0
            begin
                ROLLBACK TRAN sp_drop_central_pub_tables
                commit tran
            end
            RETURN(1)
        END
    END

    IF exists (select * from sysobjects where name = 'MSpub_identity_range')
    BEGIN
        DROP TABLE MSpub_identity_range
        IF @@ERROR <> 0
        BEGIN
            if @@trancount > 0
            begin
                ROLLBACK TRAN sp_drop_central_pub_tables
                commit tran
            end
            RETURN(1)
        END
    END
    -- end SyncTran

    IF exists (select * from sysobjects where name = 'systranschemas' and uid = 1)
    BEGIN
        DROP TABLE dbo.systranschemas
        IF @@ERROR <> 0
        BEGIN
            if @@trancount > 0
            begin
                ROLLBACK TRAN sp_drop_central_pub_tables
                commit tran
            end
            RETURN(1)
        END
    END

    COMMIT TRAN
GO
 
EXEC dbo.sp_MS_marksystemobject sp_MSdrop_pub_tables
GO

dump tran master with no_log
go

--print ''
--print 'Executing procedure dbo.sp_MSdrop_pub_tables.'
--go
--exec dbo.sp_MSdrop_pub_tables
--go

--dump tran master with no_log
--go

/* Create and execute dbo.sp_MScreate_pub_tables */

dump tran master with no_log
go

print ''
print 'Creating procedure sp_MSarticlecolstatus'
go
CREATE PROCEDURE sp_MSarticlecolstatus (
    @artid int,
    @tabid int,
    @colid int,
    @type nvarchar (10),      /* 'publish', 'nonsqlsub' */
    @status bit OUTPUT)
    AS

    SELECT @status = 0

    IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = N'nonsqlsub'
    BEGIN

		----------------------------------------------------------
		-- Check all active or subscribed articles for the column.
        ----------------------------------------------------------

		IF EXISTS( SELECT sub.artid FROM sysarticles art, syssubscriptions sub, syspublications sp, master.dbo.sysservers ss
				   WHERE art.objid = @tabid
				   AND art.artid <> @artid
				   AND sub.artid = art.artid
				   AND ss.srvid = sub.srvid
				   AND sp.pubid = art.pubid
				   AND ( ( sub.status in(1,2) AND (ss.srvproduct = N'MSREPL-NONSQL' OR sp.allow_anonymous = 1) ) 
				         OR sub.status = 3 )
				   AND CONVERT(bit, CONVERT( varbinary, SUBSTRING( CONVERT( nvarchar, art.columns ), 16 - FLOOR((@colid-1)/16),1 )) & POWER(2, ((@colid-1)%16))) = 1 )
		BEGIN
			SELECT @status = 1
		END
    END
    ELSE
    BEGIN
    
		-----------------------------------------------------------
		-- Check all articles for the column.
        -----------------------------------------------------------

		IF EXISTS( SELECT artid FROM sysarticles 
                   WHERE objid = @tabid
                   AND artid <> @artid
				   AND CONVERT(bit, CONVERT( varbinary, SUBSTRING( CONVERT( nvarchar, columns ), 16 - FLOOR((@colid-1)/16),1 )) & POWER(2, ((@colid-1)%16))) = 1 )
		BEGIN
			SELECT @status = 1
		END
    END

    RETURN (0)
GO
 
EXEC dbo.sp_MS_marksystemobject sp_MSarticlecolstatus
GO


print ''
print 'Creating procedure sp_MSarticlecol'
go
CREATE PROCEDURE sp_MSarticlecol (
    @artid int,
    @colid smallint = NULL,
    @type nvarchar(10),      /* 'publish', 'nonsqlsub' */
    @operation nvarchar(5))  /* 'add', 'drop' */
    AS

    /*
    ** Declarations.
    */
    DECLARE @cmd nvarchar(255)
    DECLARE @cmd1 nvarchar(255)
    DECLARE @columns binary(32)    /* Temporary storage for the converted column */
    DECLARE @tabid int        /* Article base table id */
    DECLARE @retcode int
    DECLARE @status bit
    DECLARE @publish smallint        /* Constant: 0x1000 */
    DECLARE @nonsqlsub smallint        /* Constant: 0x2000 */
	DECLARE @initiated smallint			/* Constant: 0x4000 */
	DECLARE @repldts smallint			/* Constant: 0x8000 */
    DECLARE @tinycolid tinyint, @allow_dts bit, @pubid int  /* hydra compatible colid */

    if @colid >= 256
    begin
	raiserror(21395, 16, -1)
        RETURN (1)
    end

    SELECT @tinycolid = @colid

    SELECT @tabid = objid, @pubid = pubid FROM sysarticles WHERE artid = @artid

	select @allow_dts = allow_dts from syspublications where pubid = @pubid


    SELECT @publish = 0x1000
    SELECT @nonsqlsub = 0x2000
	SELECT @initiated = 0x4000
	if @allow_dts = 1
		select @repldts = 0x8000
	else
		select @repldts = 0x0000

	-- loop through all columns in the article

    IF @colid IS NULL
    BEGIN
       DECLARE hCartcol CURSOR LOCAL FAST_FORWARD FOR
            SELECT colid FROM syscolumns, sysarticles
                WHERE artid = @artid
                AND id = @tabid
                AND convert(bit, convert( varbinary, substring( convert( nvarchar, columns ), 16 - floor((colid-1)/16),1 )) & power( 2, ((colid-1)%16))) = 1
    END
    ELSE
    BEGIN
       DECLARE hCartcol CURSOR LOCAL FAST_FORWARD FOR 
            SELECT colid FROM syscolumns WHERE id = @tabid
                AND colid = @colid
    END


    OPEN hCartcol

    FETCH hCartcol INTO @colid

    WHILE (@@fetch_status <> -1)
    BEGIN

       IF LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS) = N'add'
       BEGIN
          IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = N'publish'
          BEGIN
             UPDATE syscolumns
             SET colstat = colstat | @publish
             WHERE id = @tabid
             AND colid = @colid
          END
          ELSE
          BEGIN
             UPDATE syscolumns
             SET colstat = colstat | @nonsqlsub | @repldts
             WHERE id = @tabid
             AND colid = @colid
          END
       END
       ELSE /* drop */
       BEGIN
          /*
          ** Is there another non-sql server subscription on the column?
          ** Or another article publishing the column?
          */
          EXEC @retcode = dbo.sp_MSarticlecolstatus @artid, @tabid, @colid,
          @type, @status OUTPUT

          IF @@error <> 0 OR @retcode <> 0
          BEGIN
             CLOSE hCartcol
            DEALLOCATE hCartcol
            RETURN (1)
          END

          IF (@status = 0)
          BEGIN
             IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = N'publish'
             BEGIN
                /* Clear 'publish' bit */
                UPDATE syscolumns
                SET colstat = colstat & ~@publish
                WHERE id = @tabid
                AND colid = @colid
             END
             ELSE
             BEGIN
                /* Clear 'non-sql server subscription' bit */
                UPDATE syscolumns
                SET colstat = colstat & ~@nonsqlsub & ~@repldts
                WHERE id = @tabid
                AND colid = @colid
             END
          END
       END

       FETCH hCartcol INTO @colid

    END


    CLOSE hCartcol
    DEALLOCATE hCartcol

GO

EXEC dbo.sp_MS_marksystemobject sp_MSarticlecol
GO

print ''
print 'Creating procedure sp_MScreate_pub_tables'
go
CREATE PROCEDURE sp_MScreate_pub_tables
AS
    DECLARE @fError int
    SELECT @fError = 0

    -- enable 'create tables as pseudo system tables

    -- sp_MS_upd_sysobj_category is obsolete, use sp_MS_marksystemobject instead
    -- exec dbo.sp_MS_upd_sysobj_category 1


    /* 
    ** Msg 226, Level 16, State 9
    ** CREATE TABLE system-table command not allowed within multi-statement transaction.
    */
    /*
    BEGIN TRAN sp_create_central_pub_tables
    */
    
    /* Creating 'sysarticles' */
    IF not exists (select * from sysobjects where name = 'sysarticles')
    BEGIN
        create table dbo.sysarticles
        (
        artid               int                 identity NOT NULL,
        columns             varbinary(32)       NULL,
        creation_script     nvarchar(255)       NULL,
        del_cmd             nvarchar(255)       NULL,
        description         nvarchar(255)       NULL,
        dest_table          sysname             NOT NULL,
        filter              int                 NOT NULL,
        filter_clause       ntext               NULL,
        ins_cmd             nvarchar(255)       NULL,
        name                sysname             NOT NULL,
        objid               int                 NOT NULL,
        pubid               int                 NOT NULL,
        pre_creation_cmd    tinyint             NOT NULL,
        status              tinyint             NOT NULL,
        sync_objid          int                 NOT NULL,
        type                tinyint             NOT NULL,
        upd_cmd             nvarchar(255)       NULL,
        schema_option       binary(8)           NULL,
        dest_owner          sysname             NULL
        -- Note: Please update sysextendedarticlesview whenever
        -- there is a schema change in sysarticles
        )

        EXEC dbo.sp_MS_marksystemobject 'sysarticles'

        IF @@error<>0
        BEGIN
            GOTO ERROR
        END

        create unique nonclustered index unc1sysarticles
            on sysarticles(artid, pubid) 
        
        IF @@error<>0
        BEGIN
            GOTO ERROR
        END

    END

    /* Creating 'sysschemaarticles' */
    IF not exists (select * from sysobjects where name = 'sysschemaarticles')
    BEGIN
        create table dbo.sysschemaarticles
        (
        artid               int                 NOT NULL,
        creation_script     nvarchar(255)       NULL,
        description         nvarchar(255)       NULL,
        dest_object         sysname             NOT NULL,
        name                sysname             NOT NULL,
        objid               int                 NOT NULL,
        pubid               int                 NOT NULL,
        pre_creation_cmd    tinyint             NOT NULL,
        status              int                 NOT NULL,
        type                tinyint             NOT NULL,
        schema_option       binary(8)           NULL,
        dest_owner          sysname             NULL
        )
        
        IF @@error<>0
        BEGIN
            GOTO ERROR
        END

        EXEC dbo.sp_MS_marksystemobject 'sysschemaarticles'

        IF @@error<>0
        BEGIN
            GOTO ERROR
        END

        create unique nonclustered index unc1sysschemaarticles
            on sysschemaarticles(artid, pubid)

        IF @@error<>0
        BEGIN
            GOTO ERROR
        END
        
    END

    /* Creating 'sysextendedarticlesview' */
    IF not exists (select * from sysobjects where name = 'sysextendedarticlesview')
    BEGIN
        exec ('create view dbo.sysextendedarticlesview
               as
               select * from sysarticles
               union all
               select artid, NULL, creation_script, NULL, description,
               dest_object, NULL, NULL, NULL, name, objid, pubid, 
               pre_creation_cmd, status, NULL, type, NULL, 
               schema_option, dest_owner from sysschemaarticles
               go')

        IF @@error<>0
        BEGIN
            GOTO ERROR
        END 

        EXEC dbo.sp_MS_marksystemobject 'sysextendedarticlesview'

        IF @@error<>0
        BEGIN
            GOTO ERROR
        END

    END

    /* Creating 'syspublications' */
    IF NOT EXISTS (select * from sysobjects where name = 'syspublications')
    BEGIN
        CREATE TABLE dbo.syspublications (
        description                 nvarchar(255)   NULL,
        name                        sysname         NOT NULL,
        pubid                       int    identity NOT NULL,
        repl_freq                   tinyint         NOT NULL,
        status                      tinyint         NOT NULL,
        sync_method                 tinyint         NOT NULL,
        snapshot_jobid              binary(16)		NULL,
        independent_agent           bit             NOT NULL,
        immediate_sync              bit             NOT NULL,
        enabled_for_internet        bit             NOT NULL,
        allow_push                  bit             NOT NULL,
        allow_pull                  bit             NOT NULL,
        allow_anonymous             bit             NOT NULL,
        immediate_sync_ready        bit             NOT NULL,
        -- SyncTran
		allow_sync_tran             bit             NOT NULL,
        autogen_sync_procs          bit             NOT NULL,
        retention                   int             NULL,
		-- The following are post 7.0
        allow_queued_tran           bit   default 0	not null, 
        -- portable snapshot support
        snapshot_in_defaultfolder           bit   default 1 NOT NULL,         
        alt_snapshot_folder         nvarchar(255)   NULL,
        -- snapshot pre/post- command
        pre_snapshot_script         nvarchar(255)   NULL,
        post_snapshot_script        nvarchar(255)   NULL,
        -- Snapshot compression
        compress_snapshot           bit   default 0 NOT NULL,          
        -- Post 7.0 Ftp support
        ftp_address                 sysname         NULL,
        ftp_port                    int   default 21 NOT NULL,
        ftp_subdirectory            nvarchar(255)   NULL,
        ftp_login                   sysname         NULL default N'anonymous',
        ftp_password                nvarchar(524)   NULL,
		allow_dts		bit default 0 not null,
		allow_subscription_copy		bit default 0 not null,
		centralized_conflicts		bit				NULL, -- 0 False, 1 True
		conflict_retention			int				NULL, -- 60
		conflict_policy				int 			NULL, -- 1 = PubWins, 2 = SubWins, 3 = Reinit
		queue_type					int				NULL,  -- 1 = MSMQ, 2 = SQL
		ad_guidname					sysname			NULL,
		backward_comp_level	int default 10 not NULL -- default is sphinx
        )

        EXEC dbo.sp_MS_marksystemobject 'syspublications'

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END

        create unique clustered index uc1syspublications
            on syspublications (pubid) 
        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END

        create unique nonclustered index unc2syspublications
            on syspublications (name)

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END

    END

       /* Creating 'syssubscriptions' */
    IF not exists (select * from sysobjects where name = 'syssubscriptions')
    BEGIN

        CREATE TABLE dbo.syssubscriptions
        (
        artid                    int                NOT NULL,
        srvid                    smallint        NOT NULL,
        dest_db                    sysname        NOT NULL,
        status                    tinyint            NOT NULL,
        sync_type                tinyint            NOT NULL,
        login_name                sysname        NOT NULL,
        subscription_type        int                NOT NULL,
        distribution_jobid      binary(16)          NULL,
        timestamp NOT NULL,
        -- SyncTran
        update_mode             tinyint            NOT NULL, -- 0 (read only), 1 (Sync Tran), 
		                                                     -- Queued Tran
															 -- 2 (Queued Tran) , 3 (Failover), 4(sqlqueued), 5(sqlqueued failover)
        loopback_detection      bit NOT NULL,
  		queued_reinit		   bit DEFAULT 0 NOT NULL 
      )

        EXEC dbo.sp_MS_marksystemobject 'syssubscriptions'

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END

        create unique nonclustered index unc1syssubscriptions
            on syssubscriptions (artid, srvid, dest_db)

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END

    END

    -- SyncTran
    /* Creating 'sysarticleupdates' */

    IF not exists (select * from sysobjects where name = 'sysarticleupdates')
    BEGIN

        CREATE TABLE dbo.sysarticleupdates
        (
        artid                  int       NOT NULL,
        pubid                  int       NOT NULL,
        sync_ins_proc          int       NOT NULL,     -- ID of sproc handling Insert Sync Transactions
        sync_upd_proc          int       NOT NULL,     -- ID of sproc handling Update Sync Transactions
        sync_del_proc          int       NOT NULL,     -- ID of sproc handling Delete Sync Transactions
        autogen                bit       NOT NULL,
		sync_upd_trig          int       NOT NULL,     -- Note 7.0 upgrade issue
		conflict_tableid	   int		 NULL,		   -- ID of conflict table for this article
		ins_conflict_proc	   int		 NULL,		   -- ID of sproc to log conflicts
		identity_support	   bit default 0 not null  -- Whether or not do auto identity range 
		)     

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END


        -- mark the index as a system object
        exec dbo.sp_MS_marksystemobject 'sysarticleupdates'

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END

        create unique nonclustered index unc1sysarticleupdates
            on sysarticleupdates (artid, pubid)

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END
    END
    -- end SyncTran


    IF not exists (select * from sysobjects where name = 'MSpub_identity_range')
    BEGIN
        CREATE TABLE dbo.MSpub_identity_range
        (
			objid int not null,
			range bigint not null,
			pub_range bigint not null,
            current_pub_range bigint not null,
			threshold int not null,
			last_seed bigint null -- It will be not when uninitialized.
		)     

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END


        -- mark the index as a system object
        exec dbo.sp_MS_marksystemobject 'MSpub_identity_range'

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END

        create unique nonclustered index unc1MSpub_identity_range
            on MSpub_identity_range (objid)

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END
    END

    IF not exists (select * from sysobjects where name = 'systranschemas' and uid = 1)
    BEGIN
        CREATE TABLE dbo.systranschemas
        (
			tabid int not null,
			startlsn binary(10) not null,
			endlsn binary(10) not null
	)     

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END

        -- mark the index as a system object
        exec dbo.sp_MS_marksystemobject 'systranschemas'

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END

        create unique clustered index uncsystranschemas
            on systranschemas (startlsn)

        IF @@ERROR <> 0
        BEGIN
            GOTO ERROR
        END
    END

CLEANUP:

    -- disable 'create tables as pseudo system tables
    -- sp_MS_upd_sysobj_category is obsolete, use sp_MS_marksystemobject instead
    -- exec dbo.sp_MS_upd_sysobj_category 2
    RETURN( @fError )

ERROR:

    select @fError = 1
    GOTO CLEANUP

GO
 
EXEC dbo.sp_MS_marksystemobject sp_MScreate_pub_tables
GO

dump tran master with no_log
go

/*
** Create replication stored procedures.
** Part 2:  create all other stored procedures.
*/

create proc sp_MSsetfilterparent @filter_name nvarchar(386), @parent_id int
as
   -- SQL SERVER 7.0 ONLY update sysobjects, set parent id = underlying
   -- object id

    declare @retcode int
    exec @retcode = dbo.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    -- Don't bother to do anything if filter name is null or empty
    if @filter_name is null or @filter_name = N''
        return 0


   BEGIN TRAN

   exec dbo.sp_replupdateschema @filter_name 
   update sysobjects set parent_obj = @parent_id where id = object_id( @filter_name )
   exec dbo.sp_replupdateschema @filter_name 

   COMMIT TRAN

go


EXEC dbo.sp_MS_marksystemobject sp_MSsetfilterparent
GO

create proc sp_MSdoesfilterhaveparent @filter_id int
as
    declare @retcode int
    exec @retcode = dbo.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    if exists ( select * from sysobjects
                where id = @filter_id and
                parent_obj <> 0 )
    BEGIN
        return 1
    END
    ELSE
    BEGIN
        return 0
    END
go

EXEC dbo.sp_MS_marksystemobject sp_MSdoesfilterhaveparent
GO


create proc sp_MSsetfilteredstatus @object_id int
as
    declare @qualified_name nvarchar(512)

    declare @retcode int
    exec @retcode = dbo.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    exec dbo.sp_MSget_qualified_name @object_id, @qualified_name output

   BEGIN TRANSACTION
   
   exec dbo.sp_replupdateschema @qualified_name
        
   if exists( select * from sysobjects where type = 'RF' and parent_obj = @object_id )
      or exists( select * from sysarticles where objid = @object_id and ( upd_cmd like 'CALL%' OR upd_cmd like 'XCALL%' ) 
	  or exists( select * from sysarticles sa, syssubscriptions ss where sa.artid = ss.artid and ss.status = 3) )
   begin
       update sysobjects set replinfo = replinfo | 32 where id = @object_id
   end
   else
   begin
       update sysobjects set replinfo = replinfo & ~32 where id = @object_id
   end

   exec dbo.sp_replupdateschema @qualified_name

   COMMIT TRANSACTION
   
go

EXEC dbo.sp_MS_marksystemobject sp_MSsetfilteredstatus
GO

CREATE PROCEDURE sp_MSretrieve_publication 
@publication sysname
AS
declare @retcode int
/*
** Security Check
*/
exec @retcode = dbo.sp_MSreplcheck_publish
if @@ERROR <> 0 or @retcode <> 0
	return(1)

select 'Name' = name, 
		'database ' = db_name(), 
		'publisher' = @@SERVERNAME, 
		'type' = case when LOWER(repl_freq)=1 then 'Snapshot' else 'Transactional' end, 
		'description ' = description, 
		'status ' = status,  
		'allow known pull subscription' = allow_pull, 
		'allow immediate-updating subscription ' = allow_sync_tran,
		'allow anonymous ' = allow_anonymous,  
		'allow queued-updating subscription ' = allow_queued_tran, 
		'allow snapshot files FTP downloading' = enabled_for_internet, 
		'third party' = sync_method 
	from syspublications
	where name = @publication
go

EXEC dbo.sp_MS_marksystemobject sp_MSretrieve_publication
GO



CREATE PROCEDURE sp_MSreplsup_table_has_pk @tabid INT
as

-- if it's a table, check that it has a PK
-- if it's a view, see if it has an index ( MVs must have a unique CI )

    IF EXISTS (SELECT so1.* FROM sysobjects so1, sysobjects so2
                   WHERE so1.parent_obj = @tabid
                   AND so1.xtype = 'PK'
				   AND so2.id = so1.parent_obj
				   AND so2.xtype = 'U')
	BEGIN
		RETURN 1
	END

	IF EXISTS (SELECT * from sysobjects so, sysindexes si
					WHERE so.id = @tabid
					AND so.xtype = 'V'
					AND si.id = so.id )
	BEGIN
		-- evaluate keys, make sure none are nullable
		DECLARE @indkey int
		DECLARE @objname nvarchar(256)

		SELECT @indkey = 1
		select @objname = QUOTENAME(user_name(OBJECTPROPERTY(@tabid, 'OwnerId'))) collate database_default + N'.' + QUOTENAME(object_name( @tabid )) collate database_default 

		WHILE @indkey <= 16 and index_col( @objname, 1, @indkey ) is not null
		BEGIN
			IF NOT EXISTS( SELECT * FROM syscolumns 
						WHERE id = @tabid
						AND name = index_col( @objname, 1, @indkey )
						AND isnullable = 0 )
			BEGIN
				RETURN 0
			END

			SELECT @indkey = @indkey + 1
		END
		RETURN 1
	END
	ELSE
	BEGIN
        RETURN 0
	END
go

EXEC dbo.sp_MS_marksystemobject sp_MSreplsup_table_has_pk
GO

CREATE PROCEDURE sp_replsync (
    @publisher sysname,    
    @publisher_db sysname,        
    @publication sysname,    
    @article sysname = '%' 
    ) AS

    SET NOCOUNT ON
    RAISERROR (21023, 16, -1,'sp_replsync')
    RETURN(1)
GO

EXEC dbo.sp_MS_marksystemobject sp_replsync
GO


print ''
print 'Creating procedure sp_enumfullsubscriber'
go

CREATE PROCEDURE sp_enumfullsubscribers (
        @publication sysname = '%'      /* The publication name */
    ) AS

    /*
    ** Declarations.
    */

    DECLARE @retcode int

    /*
    ** Security Check
    */
    exec @retcode = dbo.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    /*
    ** Parameter Check: @publication.
    ** Check to make sure that the publication exists and that it conforms
    ** to the rules for identifiers.
    */

    IF @publication IS NOT NULL
        BEGIN

            IF @publication <> '%'
                BEGIN
                    EXECUTE @retcode = dbo.sp_validname @publication

                    IF @retcode <> 0
                    RETURN (1)
                END

            IF NOT EXISTS (SELECT * FROM syspublications WHERE name LIKE @publication)
                BEGIN
                    RAISERROR (20026, 11, -1, @publication)
                    RETURN (1)
                END

        END

    ELSE
        BEGIN
            RAISERROR (14043, 16, -1, '@publication')
            RETURN (1)
        END

    /*
    ** Select all subscribers who subscribe to all articles in the desired
    ** publication.
    */

    SELECT DISTINCT 'subscriber' = sv.srvname
      FROM syspublications p,
           sysextendedarticlesview s,
           syssubscriptions ss,
           master..sysservers sv
     WHERE p.name LIKE @publication collate database_default
       AND p.pubid = s.pubid
       AND s.artid = ss.artid
       AND ss.srvid = sv.srvid
       AND NOT EXISTS (SELECT *
                         FROM sysextendedarticlesview s2
                        WHERE s2.pubid = p.pubid
                          AND NOT EXISTS (SELECT *
                                            FROM syssubscriptions ss2,
                                                 master..sysservers sv2
                                           WHERE s2.artid = ss2.artid
                                             AND ss2.srvid = sv2.srvid
                                             AND sv2.srvid = sv.srvid))
go
 

EXEC dbo.sp_MS_marksystemobject sp_enumfullsubscribers
GO

print ''
print 'Creating procedure sp_addpublication.'
go


CREATE PROCEDURE sp_addpublication (
    @publication			sysname,                          /* publication name */
    @taskid					int = 0,                          /* backward compatible */
    @restricted				nvarchar (10) = 'false',          /* publication security */
    @sync_method			nvarchar(13) = 'native',          /* (bcp) native, (bcp) character */
    @repl_freq				nvarchar(10) = 'continuous',      /* continuous, snapshot */
    @description			nvarchar (255) = NULL,            /* publication description */
    @status					nvarchar(8) = 'inactive',         /* publication status; 0=inactive, 1=active */
    @independent_agent		nvarchar(5) = 'false',            /* true or false */
    @immediate_sync			nvarchar(5) = 'false',            /* true or false */
    @enabled_for_internet	nvarchar(5) = 'false',            /* true or false */
    @allow_push				nvarchar(5) = 'true',             /* true or false */
    @allow_pull				nvarchar(5) = 'false',            /* true or false */
    @allow_anonymous		nvarchar(5) = 'false',            /* true or false */
    -- SyncTran
    @allow_sync_tran		nvarchar(5) = 'false',            /* true or false */
    @autogen_sync_procs		nvarchar(5) = 'true',             /* auto gen sync tran procs per article */
    @retention				int = 336,                        /* 14  days */
    @allow_queued_tran      nvarchar(5) = 'false',
    -- Portable Snapshot
    @snapshot_in_defaultfolder      nvarchar(5) = 'true',
    @alt_snapshot_folder    nvarchar(255) = NULL,
    -- Snapshot pre/post- commands
    @pre_snapshot_script    nvarchar(255) = NULL,
    @post_snapshot_script   nvarchar(255) = NULL,
    -- Snapshot compression
    @compress_snapshot      nvarchar(5) = 'false',
    -- Post 7.0 FTP
    @ftp_address            sysname = NULL,
    @ftp_port                   int = 21,
    @ftp_subdirectory       nvarchar(255) = NULL,
    @ftp_login              sysname = N'anonymous',
    @ftp_password           sysname = NULL,
    @allow_dts				nvarchar(5)	= 'false',
	@allow_subscription_copy nvarchar(5) = 'false',
	@conflict_policy		nvarchar(100) = NULL, 	-- NULL, 'pub wins', 'sub reinit', 'sub wins'
	@centralized_conflicts	nvarchar(5) = NULL, 	-- NULL, 'true', 'false'
	@conflict_retention		int = 14,
	@queue_type				nvarchar(10) = NULL,	-- NULL, 'msmq', 'sql'
	@add_to_active_directory	nvarchar(10) = 'false',
	@logreader_job_name sysname = NULL,
	@qreader_job_name sysname = NULL
	) 
AS
BEGIN

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @retcode    int         /* return code value for procedure execution */
    DECLARE @rfid tinyint           /* identifier for replication frequency */
    DECLARE @publish_bit smallint   /* publication bit (flag) in sysobjects */
    DECLARE @smid tinyint           /* identifier for sync method */
    DECLARE @statid tinyint         /* status id based on @status */
    DECLARE @subs_type_id tinyint   /* subscription type id based on @subscription_type */
    DECLARE @distributor sysname
    DECLARE @distribdb sysname
    DECLARE @distproc nvarchar (255)
    DECLARE @agentname nvarchar (40)
    DECLARE @dbname sysname
    DECLARE @mergepublish_bit smallint
    DECLARE @found int
    DECLARE @independent_agent_id bit
    DECLARE @immediate_sync_id bit
    DECLARE @enabled_for_internet_id bit
    DECLARE @allow_push_id bit
    DECLARE @allow_pull_id bit
    DECLARE @allow_anonymous_id bit
    DECLARE @pubid int
    declare @distgroup sysname
    DECLARE @enc_ftp_password nvarchar(524)
	DECLARE @ad_guidname	sysname
	
    -- SyncTran
    DECLARE @allow_sync_tran_id bit
    DECLARE @allow_queued_tran_id bit
    DECLARE @autogen_sync_procs_id bit
	DECLARE @conflict_policy_id int
	DECLARE @centralized_conflicts_bit bit
			, @queue_type_val int
	
    -- Portable snapshot
    DECLARE @snapshot_in_defaultfolder_bit int 

    -- Snapshot compression
    DECLARE @compress_snapshot_bit bit

    declare @qv_replication varchar(10)
	declare @qv_replication_unlimited integer
	declare @qv_value_replication integer

	declare @backward_comp_level int
	select @backward_comp_level = 10 -- default to sphinx
	select @allow_sync_tran_id = 0
			,@autogen_sync_procs_id = 0
			,@allow_queued_tran_id = 0
			,@qv_replication = '2745196162'
			,@qv_replication_unlimited = 0
	
	/*
	** The default value for TRAN publication is always 72 hours
	*/
	if @retention is NULL
	BEGIN
		RAISERROR(20081, 16, -1, 'retention')
		RETURN (1)
	END
	
    /*
    **  A @retention value of zero means an infinite retention period
    */
	if @retention < 0
	BEGIN
		RAISERROR (20050, 16, -1, 1)
        RETURN(1)
	END

    SELECT @publish_bit         = 32
    SELECT @mergepublish_bit    = 4

    /*
    ** Security Check
    */

    exec @retcode = dbo.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    /*
    ** Check to see if the database has been activated for publication.
    */

    IF (SELECT category & 1
          FROM master..sysdatabases
         WHERE name = DB_NAME() collate database_default) = 0

    BEGIN
        RAISERROR (14013, 16, -1)
        RETURN (1)
    END

    IF @taskid <> 0
    BEGIN
        -- No longer supported
        RAISERROR (21023, 16, -1,'@taskid')
        RETURN(1)
    END

    /*
    ** Parameter Check: @publication.
    ** The @publication name must conform to the rules for identifiers,
    ** and must not be the keyword 'all'.
    */

    IF @publication IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@publication')
            RETURN (1)
        END

	exec @retcode = dbo.sp_MSreplcheck_name @publication
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    IF LOWER (@publication) = 'all'
        BEGIN
            RAISERROR (14034, 16, -1)
            RETURN (1)
        END

    /*
    **  Check if the publication already exists.
    **  1. Check transaction-level publications
    **  2. Check merge publications
    */

    IF EXISTS (SELECT * FROM syspublications WHERE name = @publication)
        BEGIN
            RAISERROR (14016, 16, -1, @publication)
            RETURN (1)
        END
    
    if (select category & @mergepublish_bit from master..sysdatabases where name = DB_NAME() collate database_default) <> 0
        begin
            EXEC @retcode = dbo.sp_helpmergepublication @publication, @found output

            IF @@ERROR <> 0 OR @retcode <> 0
            BEGIN
                RETURN (1)
            END

            IF @found <> 0 
            BEGIN
                RAISERROR (20025, 16, -1, @publication)
                RETURN (1)
            END
        end

    /*
    ** Get distribution server information for remote RPC
    ** agent verification.
    */
    EXEC @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT,
                 @distribdb = @distribdb OUTPUT

    IF @@error <> 0 OR @retcode <> 0
        BEGIN
        RAISERROR (14071, 16, -1)
            RETURN (1)
    END


    /*
    ** Parameter Check: @sync_method
    ** The synchronization method must be one of the following:
    **
    **      0  [bcp] native
    **      1  [bcp] character
	**		2  dump database
	**		3  concurrent
	**		4  concurrent character
    */

    SELECT @sync_method = LOWER(@sync_method collate SQL_Latin1_General_CP1_CS_AS)										

    IF @sync_method IS NULL OR @sync_method NOT IN ('native', 'character', 'bcp native', 'bcp character', 'dump database', 'concurrent', 'concurrent_c')
        BEGIN
            RAISERROR (14014, 16, -1)
            RETURN (1)
        END

	IF @sync_method = 'concurrent_c'
		SELECT @smid = 4
	ELSE IF @sync_method = 'concurrent'
		SELECT @smid = 3
	ELSE IF @sync_method = 'dump database'
	begin
		SELECT @smid = 2				   
		select @backward_comp_level = 40 -- not sure if we are using this, but has to be shiloh feature
	end
    ELSE IF @sync_method IN ('character', 'bcp character')
        SELECT @smid = 1
    ELSE 
        SELECT @smid = 0

    /*
    ** Parameter Check: @repl_freq.
    ** Make sure that the replication frequency is one of the following:
    **
    **  id  frequency
    **  ==  ==========
    **   0  continuous
    **   1  snapshot
    */

    SELECT @repl_freq = LOWER(@repl_freq collate SQL_Latin1_General_CP1_CS_AS)
    IF @repl_freq IS NULL OR @repl_freq NOT IN ('continuous', 'snapshot')
        BEGIN
            RAISERROR (14015, 16, -1)
            RETURN (1)
        END

    IF @repl_freq = 'snapshot' SELECT @rfid = 1
    ELSE SELECT @rfid = 0

    -- disable tran publishing on REPLICATION_LIMITED sku
	exec @qv_value_replication = master.dbo.sp_MSinstance_qv @qv_replication 	
	if (@rfid = 0) and ( @qv_value_replication != @qv_replication_unlimited ) 
    begin
        raiserror(21108, 16, -1)
        return (1)
    end

    /*
    ** Parameter Check:  @restricted.
    */

    IF (@restricted IS NULL) OR (LOWER(@restricted collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false'))
        BEGIN
            RAISERROR (14017, 16, -1)
            RETURN (1)
        END

    /*
    ** Restricted publications are no longer supported
    */
    IF LOWER(@restricted collate SQL_Latin1_General_CP1_CS_AS) = 'true'
    BEGIN
        RAISERROR (14147, 16, -1)
        RETURN(1)
    END

    /*
    ** Parameter Check:  @status.
    ** The @status value can be:
    **
    **      statid  status
    **      ======  ========
    **           0  inactive
    **           1  active
    */

    IF @status IS NULL OR LOWER(@status collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('inactive', 'active')
        BEGIN
            RAISERROR (14012, 16, -1)
            RETURN (1)
        END

    IF LOWER(@status collate SQL_Latin1_General_CP1_CS_AS) = 'active' 
	begin
		SELECT @statid = 1
	end
    ELSE 
	begin
		SELECT @statid = 0
	end

    /*
    ** Parameter Check:  @independent_agent.
    */

    IF @independent_agent IS NULL OR LOWER(@independent_agent collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@independent_agent')
            RETURN (1)
        END

    IF LOWER(@independent_agent collate SQL_Latin1_General_CP1_CS_AS) = 'true' SELECT @independent_agent_id = 1
    ELSE SELECT @independent_agent_id = 0

    /*
    ** Parameter Check:  @immediate_sync.
    */

    IF @immediate_sync IS NULL OR LOWER(@immediate_sync collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@immediate_sync')
            RETURN (1)
        END

    IF LOWER(@immediate_sync collate SQL_Latin1_General_CP1_CS_AS) = 'true' SELECT @immediate_sync_id = 1
    ELSE SELECT @immediate_sync_id = 0

    /*
    ** Parameter Check:  @enabled_for_internet.
    */

    IF @enabled_for_internet IS NULL OR LOWER(@enabled_for_internet collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@enabled_for_internet')
            RETURN (1)
        END

    IF LOWER(@enabled_for_internet collate SQL_Latin1_General_CP1_CS_AS) = 'true' SELECT @enabled_for_internet_id = 1
    ELSE SELECT @enabled_for_internet_id = 0

    IF @enabled_for_internet_id = 1 AND (@alt_snapshot_folder IS NULL OR
        @alt_snapshot_folder = N'')
        BEGIN
            RAISERROR (21159, 16, -1)
            RETURN (1) 
        END

    /*
    ** Parameter Check:  @allow_push.
    */

    IF @allow_push IS NULL OR LOWER(@allow_push collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@allow_push')
            RETURN (1)
        END
    IF LOWER(@allow_push collate SQL_Latin1_General_CP1_CS_AS) = 'true' SELECT @allow_push_id = 1
    ELSE SELECT @allow_push_id = 0

    /*
    ** Parameter Check:  @allow_pull.
    */

    IF @allow_pull IS NULL OR LOWER(@allow_pull collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@allow_pull')
            RETURN (1)
        END
    IF LOWER(@allow_pull collate SQL_Latin1_General_CP1_CS_AS) = 'true' SELECT @allow_pull_id = 1
    ELSE SELECT @allow_pull_id = 0

    /*
    ** Parameter Check:  @allow_anonymous.
    */

    IF @allow_anonymous IS NULL OR LOWER(@allow_anonymous collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@allow_anonymous')
            RETURN (1)
        END
    IF LOWER(@allow_anonymous collate SQL_Latin1_General_CP1_CS_AS) = 'true' SELECT @allow_anonymous_id = 1
    ELSE SELECT @allow_anonymous_id = 0

    /* Immediate_sync publications have to be independent_agent */
    IF @immediate_sync_id = 1 AND @independent_agent_id = 0
    BEGIN
            RAISERROR (21022, 16, -1)
            RETURN (1)
    END

    /*
    ** Parameter Check:  @add_to_active_directory.
    */

    if LOWER(@add_to_active_directory collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14148, 16, -1, '@add_to_active_directory')
            RETURN (1)
        END

   /* Is AD supported? */
   DECLARE @retval  INT
   EXECUTE @retval = master.dbo.xp_MSADEnabled
   if (@retval <> 0) and LOWER(@add_to_active_directory collate SQL_Latin1_General_CP1_CS_AS)='true'
   begin
		RAISERROR(21253, 16, -1)
		RETURN (1)
   end

    /* 
    ** Non-immediate sync do not support anonymous subscriptions.
    */
    IF @immediate_sync_id = 0 AND @allow_anonymous_id = 1
    BEGIN
            RAISERROR (20011, 16, -1)
            RETURN (1)
    END

    -- SyncTran
    /*
    ** Parameter Check:  @allow_sync_tran
    */

    IF @allow_sync_tran IS NULL OR LOWER(@allow_sync_tran collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
    BEGIN
        RAISERROR (14148, 16, -1, '@allow_sync_tran')
        RETURN (1)
    END

    /*
    ** Parameter Check:  @allow_queued_tran
    */

    IF @allow_queued_tran IS NULL OR LOWER(@allow_queued_tran collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
    BEGIN
        RAISERROR (14148, 16, -1, '@allow_queued_tran')
        RETURN (1)
    END

    IF LOWER(@allow_sync_tran collate SQL_Latin1_General_CP1_CS_AS) = 'true' 
    BEGIN
		-- If we are doing sync tran, we need independent agents
		-- override the user input
        SELECT @allow_sync_tran_id = 1
			,@independent_agent = 'true'
			,@independent_agent_id = 1
			,@backward_comp_level = 40 -- immediate update needs to have the new sp_addsynctriggers stored proc
    END
    ELSE 
    BEGIN
        SELECT @allow_sync_tran_id = 0
    END

    IF LOWER(@allow_queued_tran collate SQL_Latin1_General_CP1_CS_AS) = 'true' 
    BEGIN
		-- If we are doing queued tran, we need independent agents
		-- override the user input
        SELECT @allow_queued_tran_id = 1
			,@independent_agent = 'true'
			,@independent_agent_id = 1
			,@backward_comp_level = 40 -- queued compenents not avaliable prior to shiloh
    END
    ELSE 
    BEGIN
        SELECT @allow_queued_tran_id = 0
    END

    --Parameter Check:  @autogen_sync_procs
    IF @autogen_sync_procs IS NULL OR LOWER(@autogen_sync_procs collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
    BEGIN								 
        RAISERROR (14148, 16, -1, '@autogen_sync_procs')
        RETURN (1)
    END

	--
	-- For publications that allow updating subscribers (immediate/queued)
	-- this option has to be true at all times, for others
	-- it should be false. This flag is not of any value currently
	-- as we do not have any provision for accepting custom generated
	-- synctan proc names for an article. For now, we will override
	-- the user supplied value
	--
	select @autogen_sync_procs_id = case 
		when (@allow_sync_tran_id = 0 and @allow_queued_tran_id = 0) then 0
		else 1
	end
	
    -- Portable snapshot
    IF @snapshot_in_defaultfolder IS NULL OR LOWER(@snapshot_in_defaultfolder collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
    BEGIN
        RAISERROR (14148, 16, -1, '@snapshot_in_defaultfolder')
        RETURN (1)
    END
    
    IF LOWER(@snapshot_in_defaultfolder collate SQL_Latin1_General_CP1_CS_AS) = 'true'
    BEGIN
        SELECT @snapshot_in_defaultfolder_bit = 1
    END
    ELSE
    BEGIN
        SELECT @snapshot_in_defaultfolder_bit = 0
    END

    -- Pre/Post snapshot commands
    -- If @sync_method is character mode bcp, this would indicate that
    -- this publication may support non-SQL Server subscribers. In this 
    -- case, pre- and post- snapshot commands are not allowed.
    IF @smid = 1 AND 
        ((@pre_snapshot_script IS NOT NULL AND @pre_snapshot_script <> '') OR
         (@post_snapshot_script IS NOT NULL AND @post_snapshot_script <> ''))
    BEGIN
        RAISERROR (21151, 16, -1)
        RETURN (1)
    END
    
    -- Parameter check - @compress_snapshot
    -- @compress_snapshot can be 1 if @alt_snapshot_folder is non-null
    IF LOWER(@compress_snapshot collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
    BEGIN
        RAISERROR (14148, 16, -1, '@compress_snapshot')
        RETURN (1)
    END
    
    IF LOWER(@compress_snapshot collate SQL_Latin1_General_CP1_CS_AS) = 'true'
    BEGIN
        SELECT @compress_snapshot_bit = 1
    END
    ELSE
    BEGIN
        SELECT @compress_snapshot_bit = 0
    END

    -- Snapshot compression can only be enabled if an alternate 
    -- snapshot generation folder exists or the publication
    -- is enabled for internet.
    IF (@compress_snapshot_bit = 1 AND 
        (@alt_snapshot_folder IS NULL OR @alt_snapshot_folder = N'') AND
        (LOWER(@enabled_for_internet collate SQL_Latin1_General_CP1_CS_AS) = N'false'))
    BEGIN
        RAISERROR (21157, 16, -1)
        RETURN (1)
    END    

    -- Parameter check: ftp_address
    -- If the publication is enabled for internet, ftp_address cannot be null
    IF @enabled_for_internet_id = 1 AND (@ftp_address IS NULL OR @ftp_address = N'')
    BEGIN
        RAISERROR (21158, 16, -1)
        RETURN (1)
    END     

    -- Parameter check: ftp_port
    IF @ftp_port IS NULL
    BEGIN
        RAISERROR (21160, 16, -1)
    END

	-- Parameter check : DTS
	declare @allow_dts_id bit
    IF @allow_dts IS NULL OR LOWER(@allow_dts collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
    BEGIN
        RAISERROR (14148, 16, -1, '@allow_dts')
        RETURN (1)
    END

    -- Encrypt ftp password before putting it into the syspublications
    -- table if one is provided
    SELECT @enc_ftp_password = NULL
    IF @ftp_password IS NOT NULL
    BEGIN
        SELECT @enc_ftp_password = @ftp_password 
        EXEC @retcode = master.dbo.xp_repl_encrypt @enc_ftp_password OUTPUT
        IF @retcode <> 0
        BEGIN
            RETURN (1)
        END
    END    

    IF LOWER(@allow_dts collate SQL_Latin1_General_CP1_CS_AS) = 'true' SELECT @allow_dts_id = 1
    ELSE SELECT @allow_dts_id = 0

	-- To allow DTS, the publication has to be
	-- 1. independent agent
	-- 2. char bcp

	if @allow_dts_id = 1
	begin
		if @smid NOT IN ( 1, 4 )
		begin
			-- 'The publication has to be in char bcp mode.'
			raiserror(21172, 16, -1)
			return(1)
		end
		if @independent_agent_id = 0
		begin
			-- 'The publication has to be independent agent.'
			raiserror(21173, 16, -1)
			return(1)		
		end
		if @allow_sync_tran_id = 1 or @allow_queued_tran_id = 1
		begin
			raiserror(21180, 16, -1)
			return(1)
		end
	end

	declare @allow_subscription_copy_id bit
    IF LOWER(@allow_subscription_copy collate SQL_Latin1_General_CP1_CS_AS) = 'true' SELECT @allow_subscription_copy_id = 1
    ELSE SELECT @allow_subscription_copy_id = 0

	-- To allow subscription copy, the publication has to be
	-- 1. immediate_sync
	if @allow_subscription_copy_id = 1
	begin
		if @immediate_sync_id <> 1
		begin
			raiserror(21210, 16, -1)
			return(1)
		end
	end

	--
	-- Check parameters for @conflict_policy, @centralized_conflicts, @conflict_retention
	-- for queued publications
	--
	if (@allow_queued_tran_id = 1)
	begin
		--
		-- set default conflict_policy if required
		--
		if (@conflict_policy IS NULL)
		begin
			--
			-- if it is snapshot based, then the default policy is 'sub reinit'
			-- else the default policy is 'pub wins'
			--
			select @conflict_policy = case when (@rfid = 1) then 'sub reinit' 
										else 'pub wins' end
		end
		
		if (LOWER(@conflict_policy collate SQL_Latin1_General_CP1_CS_AS) = 'sub reinit')
			select @conflict_policy_id = 3
		else if (LOWER(@conflict_policy collate SQL_Latin1_General_CP1_CS_AS) = 'pub wins')
			select @conflict_policy_id = 1
		else if (LOWER(@conflict_policy collate SQL_Latin1_General_CP1_CS_AS) = 'sub wins')
			select @conflict_policy_id = 2
		else
		begin
			raiserror (21184, 16, 2, '@conflict_policy', 'sub reinit', 'pub wins', 'sub wins')
			return (1)
		end

		--
		-- Check snapshot permissible values
		--
		if ((@rfid = 1) and (@conflict_policy_id = 1))
		begin
			raiserror (21270, 16, 1, '@conflict_policy', @conflict_policy)
			return (1)
		end
		
		--
		-- set default centralized_conflicts if required
		--
		if (@centralized_conflicts IS NULL)
			select @centralized_conflicts = 'true'
		
		if (LOWER(@centralized_conflicts collate SQL_Latin1_General_CP1_CS_AS) = 'true')
			select @centralized_conflicts_bit = 1
		else if (LOWER(@centralized_conflicts collate SQL_Latin1_General_CP1_CS_AS) = 'false')
			select @centralized_conflicts_bit = 0
		else
		begin
			raiserror (14148, 16, -1, '@centralized_conflicts')
			return (1)
		end

		--
		-- Check snapshot permissible values
		--
		if ((@rfid = 1) and (@centralized_conflicts_bit = 0))
		begin
			raiserror (21270, 16, 1, '@centralized_conflicts', @centralized_conflicts)
			return (1)
		end
		
		if (@conflict_retention IS NULL)
			select @conflict_retention = 14
		else if (@conflict_retention < 0)
		begin
			raiserror(20050, 16, -1, 0)
			return (1)
		end

		if ((@queue_type IS NULL) or (LOWER(@queue_type collate SQL_Latin1_General_CP1_CS_AS) = 'sql'))
			select @queue_type_val = 2
		else if (LOWER(@queue_type collate SQL_Latin1_General_CP1_CS_AS) = 'msmq')
			select @queue_type_val = 1
		else
		begin
			raiserror (21184, 16, 3, '@queue_type', 'msmq', 'sql', 'NULL')
			return (1)
		end
	end

    /*
    ** Get distribution server information for remote RPC call.
    */
    EXECUTE @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT,
       @distribdb   = @distribdb OUTPUT
    IF @@ERROR <> 0 or @retcode <> 0
    BEGIN
        GOTO UNDO
    END

    /*
    **  Add publication to syspublications.
    */
    begin tran
    save TRAN sp_addpublication

    select @dbname = db_name()
    
    /*
    ** Construct Log Reader agent name.
    */

	declare @job_existing bit

    -- Create logreader task for the first log based or allow queued publication.
    -- We need logreader for queued publication to do idenity range management.
    IF  (@rfid = 0 or @allow_queued_tran_id = 1)  and NOT EXISTS 
        (SELECT * FROM syspublications where repl_freq = 0 or allow_queued_tran = 1) 
    BEGIN
		-- Clear repl dbtable fields. This will avoid unnecessary log scan in the 
		-- logreader at the first time it runs.
		--
		-- sp_droppublication will clear the fields when dropping the last publication, 
		-- however, it is after the transaction deleting syspublication table being
		-- committed, thus not guaranteed.
		-- 
		-- We also need this logic for upgraded 7.0 databases where we don't clear
		-- distbackuplsn and distlastlsn fields in unpublishing.
		--
		/* ensure we can get in as logreader */
	    exec @retcode = dbo.sp_replflush
        IF @@ERROR <> 0 or @retcode <> 0
            GOTO UNDO

		/* unmark all xacts marked for replication */
		exec @retcode = dbo.sp_repldone NULL, NULL, 0, 0, 1
        IF @@ERROR <> 0 or @retcode <> 0
            GOTO UNDO
    
	    /* release our hold on the db as logreader */
	    EXEC @retcode = dbo.sp_replflush
        IF @@ERROR <> 0 or @retcode <> 0
            GOTO UNDO

		-- Run checkpoint to make sp_repldone result durable (write repl dbtable fields
		-- into the checkpoint record).
		checkpoint
        IF @@ERROR <> 0
            GOTO UNDO


		if @logreader_job_name is null
			select @job_existing = 0
		else
			select @job_existing = 1

        /*
        ** Schedule Log Reader agent for the database
		** If @logreader_job_name is not null
        */
        SELECT @distproc = RTRIM(@distributor) + '.' + @distribdb +'.dbo.sp_MSadd_logreader_agent'

        EXECUTE @retcode = @distproc
			@name = @logreader_job_name,
            @publisher = @@SERVERNAME,
            @publisher_db = @dbname,
            @publication = 'ALL',  
            @local_job = 1,
			@job_existing = @job_existing 

        IF @@ERROR <> 0 or @retcode <> 0
            GOTO UNDO
    END            

    INSERT syspublications(description, name, repl_freq,
                           status, sync_method, snapshot_jobid, independent_agent,
                           immediate_sync, enabled_for_internet, 
                           allow_push, allow_pull, allow_anonymous, immediate_sync_ready,
                           -- SyncTran
                           allow_sync_tran, autogen_sync_procs, retention, allow_queued_tran,
                           snapshot_in_defaultfolder, alt_snapshot_folder, pre_snapshot_script, 
                           post_snapshot_script, compress_snapshot, ftp_address, ftp_port, 
                           ftp_subdirectory, ftp_login, ftp_password, allow_dts, 
						   allow_subscription_copy, centralized_conflicts, conflict_retention, 
						   conflict_policy, queue_type, backward_comp_level)

    VALUES (@description, @publication, @rfid, @statid, @smid, NULL, 
            @independent_agent_id, 
            @immediate_sync_id, @enabled_for_internet_id, @allow_push_id,
            @allow_pull_id, @allow_anonymous_id, 0,
            -- SyncTran
            @allow_sync_tran_id, @autogen_sync_procs_id, @retention, @allow_queued_tran_id,
            @snapshot_in_defaultfolder_bit, @alt_snapshot_folder, @pre_snapshot_script,
            @post_snapshot_script, @compress_snapshot_bit, @ftp_address, @ftp_port, 
            @ftp_subdirectory, @ftp_login, @enc_ftp_password, 
			@allow_dts_id, @allow_subscription_copy_id, @centralized_conflicts_bit, @conflict_retention, 
			@conflict_policy_id, @queue_type_val, @backward_comp_level)

    IF @@ERROR <> 0
    BEGIN
        RAISERROR (14018, 16, -1)
        GOTO UNDO
    END

    SELECT @pubid = @@IDENTITY

    DECLARE @false bit
    SELECT @false = 0

    DECLARE @null sysname
    SELECT @null = NULL

    /*
    ** Add the publication to the distributor side
    */
    SELECT @distproc = RTRIM(@distributor) + '.' + @distribdb + 
        '.dbo.sp_MSadd_publication'
    EXECUTE @retcode = @distproc
    @publisher = @@SERVERNAME,
    @publisher_db = @dbname,
    @publication = @publication,
    @publication_type = @rfid,
    @independent_agent = @independent_agent_id,
    @immediate_sync = @immediate_sync_id,
    @allow_push = @allow_push_id,
    @allow_pull = @allow_pull_id,
    @allow_anonymous = @allow_anonymous_id,
    @snapshot_agent = @null,
    @logreader_agent = @agentname,
    @description = @description,
    @retention = @retention,
	@sync_method = @smid,
	@allow_subscription_copy = @allow_subscription_copy_id,
	@allow_queued_tran = @allow_queued_tran_id,
	@queue_type = @queue_type_val

    IF @@ERROR <> 0 or @retcode <> 0
        BEGIN
            GOTO UNDO
        END

	if @qreader_job_name is not null
	begin
		--
		-- Schedule Queue Reader agent for the database
		--
		SELECT @distproc = RTRIM(@distributor) + '.' + @distribdb +'.dbo.sp_MSadd_qreader_agent'
		EXECUTE @retcode = @distproc @name = @qreader_job_name
		IF @@ERROR <> 0 or @retcode <> 0
		GOTO UNDO
	end	

    -- Populate the initial list.
	exec @retcode = dbo.sp_grant_publication_access 
		@publication = @publication,
		@login = null,
		@reserved = 'init'
	IF @@error <> 0 OR @retcode <> 0
		GOTO UNDO

    COMMIT TRAN

	declare @returnstring nvarchar(512) 
	set @returnstring = N''
	if LOWER(@add_to_active_directory collate SQL_Latin1_General_CP1_CS_AS)='true'
    begin
    	--no error checking needed here.    
		create table #guid_name_for_active_directory(ad_guidname sysname collate database_default null)
		if @@ERROR<>0
		begin
    		goto FAILURE 					
		end
		insert into #guid_name_for_active_directory exec @retcode=master.dbo.sp_ActiveDirectory_Obj 'CREATE', 'PUBLICATION', @publication, @dbname
		if @retcode <> 0 or @@ERROR<>0
    	begin
			set @returnstring = (select TOP 1 ad_guidname from #guid_name_for_active_directory)
    		goto FAILURE 					
	   	end
    	select TOP 1 @ad_guidname = ad_guidname from #guid_name_for_active_directory
    	if @ad_guidname is not NULL
    	begin
    		update syspublications set ad_guidname=@ad_guidname where pubid=@pubid
    		if @@ERROR<>0
    			goto FAILURE
    	end
		drop table #guid_name_for_active_directory
    end
	RETURN(0)
FAILURE:
	drop table #guid_name_for_active_directory
	if @returnstring is NULL
		select @returnstring = N''
	raiserror(21363, 16, -1, @publication, @returnstring)
    return (1)   
UNDO:
    IF @@TRANCOUNT > 0
    begin
        ROLLBACK TRAN sp_addpublication
        COMMIT TRAN   
    end
    return 1
END
go
 
EXEC dbo.sp_MS_marksystemobject sp_addpublication
GO

print ''
print 'Creating procedure sp_changepublication'
go

CREATE PROCEDURE sp_changepublication (
    @publication sysname = NULL,        /* Publication name */
    @property nvarchar(50) = NULL,           /* The property to change */
    @value nvarchar(255) = NULL,              /* The new property value */
	@force_invalidate_snapshot bit = 0,	/* Force invalidate existing snapshot */
	@force_reinit_subscription bit = 0	/* Force reinit subscription */
) 
AS
BEGIN
    SET NOCOUNT ON

    /*
    ** Declarations.
    */

	DECLARE @cmd nvarchar(255)
			,@cmd2 nvarchar(255)
			,@pubid int
			,@replfreqid tinyint
			,@retcode int
			,@statusid tinyint
			,@syncmethodid tinyint
			,@db_name	sysname
			,@distributor sysname
			,@distproc nvarchar (255)
			,@subscribed int    
			,@virtual_id smallint
			,@prev_value_bit bit
			,@value_bit bit
			,@allow_anonymous bit
			,@push int
			,@pull int
			,@independent_agent bit
			,@immediate_sync bit
			,@distribdb sysname
			,@dbname sysname
			,@taskid int
			,@add_virtual_back bit
			,@alt_snapshot_folder nvarchar(255)
			,@enabled_for_internet bit
			,@ftp_address sysname
			,@snapshot_in_defaultfolder bit
			,@allow_dts bit
			,@in_ActiveD	bit
			,@ad_guidname	sysname
			,@enc_ftp_password nvarchar(524)
			,@conflict_policy_id int
			,@centralized_conflicts_bit bit
			,@conflict_retention int
			,@queue_type int
			,@allow_sync_tran bit
			,@allow_queued_tran bit
			
	SELECT @add_virtual_back = 0
			,@push = 0
			,@pull = 1
			,@alt_snapshot_folder = NULL
			,@subscribed = 1
			,@virtual_id = -1

    /*
    ** Security Check
    */

    exec @retcode = dbo.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    /*
    ** Check to see if the database has been activated for publication.
    */

    IF (SELECT category & 1
          FROM master..sysdatabases
         WHERE name = DB_NAME() collate database_default) = 0

    BEGIN
        RAISERROR (14013, 16, -1)
        RETURN (1)
    END

    select @db_name=db_name()
    
    /*
    ** Parameter Check:  @property.
    ** If the @property parameter is NULL, print the options.
    */

    IF @property IS NULL
        BEGIN
            CREATE TABLE #tab1 (properties sysname collate database_default not null)
            INSERT INTO #tab1 VALUES ('description')
            --INSERT INTO #tab1 VALUES ('taskid')
            INSERT INTO #tab1 VALUES ('sync_method')
            INSERT INTO #tab1 VALUES ('status')
            INSERT INTO #tab1 VALUES ('repl_freq')
            INSERT INTO #tab1 VALUES ('independent_agent')
            INSERT INTO #tab1 VALUES ('immediate_sync')
            INSERT INTO #tab1 VALUES ('enabled_for_internet')
            INSERT INTO #tab1 VALUES ('allow_push')
            INSERT INTO #tab1 VALUES ('allow_pull')
            INSERT INTO #tab1 VALUES ('allow_anonymous')
            INSERT INTO #tab1 VALUES ('retention')
            INSERT INTO #tab1 VALUES ('snapshot_in_defaultfolder')
            INSERT INTO #tab1 VALUES ('alt_snapshot_folder')
            INSERT INTO #tab1 VALUES ('pre_snapshot_script')
            INSERT INTO #tab1 VALUES ('post_snapshot_script')
            INSERT INTO #tab1 VALUES ('compress_snapshot')
            INSERT INTO #tab1 VALUES ('ftp_address')
            INSERT INTO #tab1 VALUES ('ftp_port')
            INSERT INTO #tab1 VALUES ('ftp_subdirectory')
            INSERT INTO #tab1 VALUES ('ftp_login')
            INSERT INTO #tab1 VALUES ('ftp_password')
            INSERT INTO #tab1 VALUES ('allow_subscription_copy')
            INSERT INTO #tab1 VALUES ('conflict_policy')
            INSERT INTO #tab1 VALUES ('centralized_conflicts')
            INSERT INTO #tab1 VALUES ('conflict_retention')
            INSERT INTO #tab1 VALUES ('queue_type')
            INSERT INTO #tab1 VALUES ('publish_to_ActiveDirectory')
            PRINT ''
            SELECT * FROM #tab1
            RETURN (0)
        END

    /*
    ** Parameter Check:  @publication.
    ** Make sure that the publication exists.
    */

    IF @publication IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@publication')
            RETURN (1)
        END

    EXECUTE @retcode = dbo.sp_validname @publication

    IF @@ERROR <> 0 OR @retcode <> 0
    RETURN (1)

    SELECT @allow_anonymous = allow_anonymous, @pubid = pubid, 
    	@ad_guidname=ad_guidname, --with NULL value if this publication is not in AD
    	@replfreqid = repl_freq,
        @immediate_sync = immediate_sync,
        @independent_agent = independent_agent,
        @syncmethodid = sync_method,
        @alt_snapshot_folder = alt_snapshot_folder,
        @enabled_for_internet = enabled_for_internet,
        @ftp_address = ftp_address,
		@allow_dts = allow_dts
		,@queue_type = queue_type
        ,@snapshot_in_defaultfolder = snapshot_in_defaultfolder
		,@in_ActiveD = case when ad_guidname is NULL then 0 else 1 end 
		,@allow_sync_tran = allow_sync_tran
		,@allow_queued_tran = allow_queued_tran
        FROM syspublications 
        WHERE name = @publication

    IF @pubid IS NULL
        BEGIN
            RAISERROR (20026, 11, -1, @publication)
            RETURN (1)
        END
    ELSE

    /*
    ** Parameter Check:  @property.
    ** Check to make sure that @property is a valid property in
    ** syspublications.
    */

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) NOT IN ( 'taskid','description', 'sync_method',
     'status', 'repl_freq','immediate_sync', 'independent_agent', 
     'enabled_for_internet', 'allow_push', 'allow_pull', 'allow_anonymous', 'retention',
     'snapshot_in_defaultfolder', 'alt_snapshot_folder', 'pre_snapshot_script', 'post_snapshot_script', 
     'compress_snapshot', 'ftp_address', 'ftp_port', 'ftp_subdirectory',
     'ftp_login', 'ftp_password','allow_subscription_copy','conflict_policy',
     'centralized_conflicts','conflict_retention','queue_type','publish_to_activedirectory')
        BEGIN
            RAISERROR (21183, 16, -1, @property)
            RETURN (1)
        END

    /*
    ** Parameter Check:
    ** If sync_method of the publication is character mode (an indication that it supports
    ** third party Subscribers), pre/post-snapshot setting must be null   
    **
    */
    IF @syncmethodid = 1 
    BEGIN
        IF (LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'pre_snapshot_script' OR
            LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'post_snapshot_script') AND
            @value IS NOT NULL AND @value <> ''
        BEGIN
            RAISERROR (21151, 16, -1)
            RETURN (1)
        END   
    END

    /*
    ** Parameter Check:
    ** If the Publication's alt_snapshot_folder setting is null, 
    ** snapshot compression cannot be enabled
    */
    IF (@alt_snapshot_folder IS NULL OR @alt_snapshot_folder = '') 
        AND LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'compress_snapshot'
        AND LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'   
    BEGIN
        RAISERROR (21157, 16, -1)        
        RETURN(1)
    END

    /* 
    ** Parameter Check:
    ** If enabled_for_internet is set to true, the publication must have a non-null
    ** ftp_address.
    */
    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'enabled_for_internet' AND
       LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = N'true' AND 
       (@ftp_address IS NULL OR @ftp_address = N'')
    BEGIN
        RAISERROR(21158, 16, -1)
        RETURN (1)
    END

    /*
    ** .. and ftp_address cannot be null if the publication is enabled for 
    ** internet.
    */
/*
    IF @enabled_for_internet = 1 AND
      (LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'ftp_address'
        AND (@value IS NULL OR @value = N''))
    BEGIN
        RAISERROR(21158, 16, -1)
        RETURN (1)
    END
*/
    /*
    ** .. and 'alternate snapshot folder' is not null
    **
    */
    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'enabled_for_internet' AND
        LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = N'true' AND
       (@alt_snapshot_folder IS NULL OR @alt_snapshot_folder = N'')
    BEGIN
        RAISERROR(21159, 16, -1)
        RETURN (1)
    END 

    /* 
    ** Parameter Check:
    ** If the publication is enabled for internet, the publication must
    ** have a non-null alternate snapshot folder and the 
    ** snapshot_in_defaultfolder property must be 0.
    */
/*
    IF @enabled_for_internet = 1 AND 
       ((LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'alt_snapshot_folder'
        AND (@value IS NULL OR @value = N'')))
    BEGIN
        RAISERROR(21159, 16, -1)
        RETURN (1)
    END  
*/

    /*
    ** Parameter Check:
    ** 'ftp_port' cannot be null
    */
    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'ftp_port' AND @value IS NULL
    BEGIN
        RAISERROR (14043, 16, -1, @property)
        RETURN (1)
    END

	-- Check to see if there are snapshot and subscription needs to be reinited.
	declare @need_new_snapshot bit
		,@need_reinit_subscription bit
		,@active tinyint

	select @active = 2
	select @need_new_snapshot = 0
	select @need_reinit_subscription = 0

	if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) in ('snapshot_in_defaultfolder', 'alt_snapshot_folder',
		'pre_snapshot_script', 'post_snapshot_script', 'compress_snapshot', 
		'ftp_address','ftp_port','ftp_subdirectory','ftp_login','ftp_password',
		'enabled_for_internet')
	begin
		select @need_new_snapshot = 1
	end
	else if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'sync_method'
	begin
		-- If changing to or from concurrent, must reinit subscription.
		if EXISTS( select * from syspublications sp
			where sp.pubid = @pubid and 
			(sp.sync_method in (3,4) or LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) in ('concurrent', 'concurrent_c'))) 
		BEGIN
			select @need_new_snapshot = 1
			select @need_reinit_subscription = 1			
		END
		else
			select @need_new_snapshot = 1
	end
	else if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'repl_freq'
	BEGIN
		select @need_new_snapshot = 1
		select @need_reinit_subscription = 1			
	END
	
	-- Have to call this stored procedure to invalidate existing snapshot or reint
	-- subscriptions if needed
	EXECUTE @retcode  = dbo.sp_MSreinit_article
		@publication = @publication, 
		@need_new_snapshot = @need_new_snapshot,
		@need_reinit_subscription = @need_reinit_subscription
		,@force_invalidate_snapshot = @force_invalidate_snapshot	/* Force invalidate existing snapshot */
		,@force_reinit_subscription = @force_reinit_subscription	/* Force reinit subscription */
		,@check_only = 1
	IF @@ERROR <> 0 OR @retcode <> 0
		return (1)

    /*
    ** Change the property.
    */
    begin tran
    save TRAN sp_changepublication

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) ='description'
        BEGIN
            UPDATE syspublications SET description = @value
                WHERE pubid = @pubid
            IF @@ERROR <> 0 GOTO UNDO
        END
        
    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) ='retention'
        BEGIN
        	if @value is NULL 
        		BEGIN
        			RAISERROR(20081, 16, -1, @property)
        			GOTO UNDO
        		END
        		
            UPDATE syspublications SET retention = convert(int, @value)
                WHERE pubid = @pubid
            IF @@ERROR <> 0 GOTO UNDO
        END
   

   if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'publish_to_activedirectory'
   		BEGIN
        if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        	BEGIN
            	RAISERROR (14137, 16, -1)
            	GOTO UNDO
        	END

	    /* Is AD supported? */
	    DECLARE @retval  INT
	    EXECUTE @retval = master.dbo.xp_MSADEnabled
	    if (@retval <> 0)
	    begin
			RAISERROR(21254, 16, -1, @publication)
			RETURN (1)
	    end

   		if @in_ActiveD=0 and LOWER(@value collate SQL_Latin1_General_CP1_CS_AS)='true'
	   		BEGIN
				create table #guid_name_for_active_directory(ad_guidname sysname collate database_default null)
				if @@ERROR<>0
				begin
		    		raiserror(21363, 16, -1, @publication, N'')
    				GOTO UNDO									
				end
				insert into #guid_name_for_active_directory exec @retcode=master.dbo.sp_ActiveDirectory_Obj 'CREATE', 'PUBLICATION', @publication, @db_name
				if @retcode <> 0 or @@ERROR<>0
		    	begin
					declare @errorstring nvarchar(512)
					select @errorstring = (select TOP 1 ad_guidname from #guid_name_for_active_directory) 
					drop table #guid_name_for_active_directory
					if @errorstring is NULL
						select @errorstring=N''
		    		raiserror(21363, 16, -1, @publication, @errorstring)
    				GOTO UNDO					
	   			end
	    		select TOP 1 @ad_guidname = ad_guidname from #guid_name_for_active_directory

    			if @ad_guidname is not NULL
	    		begin
    				update syspublications set ad_guidname=@ad_guidname where pubid=@pubid
    				if @@ERROR<>0
    				begin
						drop table #guid_name_for_active_directory
			    		raiserror(21363, 16, -1, @publication, N'')
    					GOTO UNDO					
    				end
	    		end
	    		drop table #guid_name_for_active_directory
   			END
   		else if @in_ActiveD=1 and LOWER(@value collate SQL_Latin1_General_CP1_CS_AS)='false'
   			BEGIN
				exec @retcode=master.dbo.sp_ActiveDirectory_Obj 'DELETE', 'PUBLICATION', @publication, @db_name, @ad_guidname
				if @@ERROR<>0 or @retcode<>0
				begin
					raiserror(21369, 16, -1, @publication)	
					goto UNDO
				end
				update syspublications set ad_guidname=NULL where pubid=@pubid
				if @@ERROR<>0
				begin
					raiserror(21369, 16, -1, @publication)	
					goto UNDO
				end
   			END
	   END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'taskid'
       BEGIN
            -- No longer supported
            RAISERROR (21023, 16, -1,'@taskid')
            goto UNDO
       END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'sync_method'
        BEGIN

            /*
            ** Check for a valid synchronization method.
            */

            IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('native', 'character', 'bcp native', 'bcp character', 'concurrent', 'concurrent_c')
                BEGIN
                    RAISERROR (14014, 16, -1)
                    GOTO UNDO
                END

	        /*
            ** Determine the integer value for the sync_method.
            */

            IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) IN ('native', 'bcp native')
                SELECT @syncmethodid = 0
            ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) IN ('character', 'bcp character')
                SELECT @syncmethodid = 1
			ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) IN ( 'concurrent' )
				SELECT @syncmethodid = 3
			ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) IN ( 'concurrent_c' )
				SELECT @syncmethodid = 4

			if @syncmethodid NOT IN (1,4) and @allow_dts = 1
			begin
				raiserror(21172, 16, -1)
				GOTO UNDO
			end

			-- Non sql subscribers can only use char bcp (not concurrent)
			if @syncmethodid <> 1
			begin
				IF EXISTS( select * from syspublications sp, syssubscriptions ss, 
							sysarticles sa, master..sysservers srv
						   where sp.pubid = @pubid 
						   and sp.pubid = sa.pubid
						   and sa.artid = ss.artid
						   and srv.srvid = ss.srvid
						   and srv.srvproduct = N'MSREPL-NONSQL' collate database_default)
				BEGIN
					RAISERROR(20593, 16, -1, @publication )
					GOTO UNDO
				END			 
			end

			if exists (select * from syspublications where
				pubid = @pubid and
				sync_method <> @syncmethodid)
			begin
				/*
				** Update the publication with the new synchronization method.
				*/

				/*
				** If we switch to character mode bcp (an indication that this 
				** publication may support non-SQL Server subscribers) for this 
				** publication, the pre/post snapshot commands settings should be
				** nullified  
				*/    
				IF @syncmethodid = 1
				BEGIN

					UPDATE syspublications 
					SET sync_method = @syncmethodid, pre_snapshot_script = NULL,
						post_snapshot_script = NULL
					WHERE pubid = @pubid

				END    
				ELSE
				BEGIN
					UPDATE syspublications
					   SET sync_method = @syncmethodid
					 WHERE pubid = @pubid
				END

	            IF @@ERROR <> 0 GOTO UNDO
			end            
        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'status'
        BEGIN
            
            /*
            ** Check to make sure that we have a valid status.
            */

            IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('active', 'inactive')
                BEGIN
                    RAISERROR (14012, 16, -1)
                    GOTO UNDO
                END

            /*
            ** Determine the integer value for the status.
            */

            IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'active'
                SELECT @statusid = 1
            ELSE
                SELECT @statusid = 0

            /* If status changed */
            IF EXISTS (SELECT * FROM syspublications
                WHERE  pubid = @pubid  AND
                 status <> @statusid)
            BEGIN
    
                /* 
                ** If change the status of the publication,
                ** virtual anonymous subscription have to be recreated.
                **
                */
                IF @allow_anonymous = 1
                BEGIN
                    /* Drop virtual subscriptions */
                    EXEC @retcode = dbo.sp_dropsubscription 
                        @publication = @publication, 
                        @article = 'all', 
                        @subscriber = NULL,
                        @reserved = 'internal'
                    IF @@ERROR <> 0 OR @retcode <> 0
                    BEGIN
                        GOTO UNDO
                    END
                END

                /*
                ** Update the publication with the new status.
                */

                UPDATE syspublications
                   SET status = @statusid
                 WHERE pubid = @pubid

                IF @@ERROR <> 0 
                BEGIN
                    GOTO UNDO                
                END
                
                IF @allow_anonymous = 1
                    SELECT @add_virtual_back = 1
            END
        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'repl_freq'
        BEGIN

            /*
            ** Check for a valid replication frequency value.
            */

            IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('continuous', 'snapshot')
                BEGIN
                    RAISERROR (14015, 16, -1)
                    GOTO UNDO
                END


            /*
            ** Determine the integer value for the replication frequency.
            */

            IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'continuous'
                SELECT @replfreqid = 0
            ELSE
                SELECT @replfreqid = 1

            /*
            ** Only unsubscribed publications may have this modified.
            */
            IF EXISTS (SELECT * FROM syssubscriptions s
                       INNER JOIN sysextendedarticlesview a on s.artid = a.artid
                        WHERE s.status <> @subscribed
                          AND s.srvid >= 0 
                          AND a.pubid = @pubid)
            BEGIN
                RAISERROR (14033, 11, -1)
                GOTO UNDO
            END



/*

            IF EXISTS (SELECT * FROM syssubscriptions
            WHERE 
                status <> @subscribed AND
                srvid >= 0 AND
                artid IN (SELECT artid FROM sysextendedarticlesview where pubid
               = @pubid))
            BEGIN
                RAISERROR (14033, 11, -1)
                GOTO UNDO
            END

*/
            IF @immediate_sync = 1
            BEGIN
                /* Drop virtual subscriptions */
                EXEC @retcode = dbo.sp_dropsubscription 
                    @publication = @publication, 
                    @article = 'all', 
                    @subscriber = NULL,
                    @reserved = 'internal'
                IF @@ERROR <> 0 OR @retcode <> 0
                BEGIN
                    GOTO UNDO                
                END
            END
            /*
            ** Update the publication with the new replication frequency.
            */

            UPDATE syspublications
               SET repl_freq = @replfreqid
             WHERE pubid = @pubid

            IF @@ERROR <> 0 
            BEGIN
                GOTO UNDO
            END

            IF @immediate_sync = 1
                SELECT @add_virtual_back = 1
        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'alt_snapshot_folder'
        BEGIN
            -- If the alt_snapshot_folder is set to '' or NULL,
            -- set the compress_snapshot bit to 0 and disable
            -- internet support  
            IF @value IS NULL OR @value = N''
            BEGIN
                UPDATE syspublications
                   SET alt_snapshot_folder = @value,
                       compress_snapshot = 0,
                       enabled_for_internet = 0
                 WHERE pubid = @pubid
            END
            ELSE
            BEGIN
                UPDATE syspublications
                   SET alt_snapshot_folder = @value
                 WHERE pubid = @pubid

            END
            IF @@error <> 0
            BEGIN
               GOTO UNDO
            END

        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'pre_snapshot_script'
        BEGIN
            UPDATE syspublications
               SET pre_snapshot_script = @value
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'post_snapshot_script'
        BEGIN
            UPDATE syspublications
               SET post_snapshot_script = @value
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'ftp_address'
        BEGIN
    
            IF @value IS NULL OR @value = N''
            BEGIN
                UPDATE syspublications
                   SET ftp_address = @value,
                       enabled_for_internet = 0
                 WHERE pubid = @pubid
                IF @@error <> 0
                BEGIN
                    GOTO UNDO
                END
            END
            ELSE
            BEGIN
                UPDATE syspublications
                   SET ftp_address = @value
                 WHERE pubid = @pubid
                IF @@error <> 0
                BEGIN
                    GOTO UNDO
                END
            END
        END
            
    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'ftp_port'
        BEGIN
            UPDATE syspublications
               SET ftp_port = CONVERT(int, @value)
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'ftp_subdirectory'
        BEGIN
            UPDATE syspublications
               SET ftp_subdirectory = @value
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'ftp_login'
        BEGIN
            UPDATE syspublications
               SET ftp_login = @value
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'ftp_password'
        BEGIN
            SELECT @enc_ftp_password = NULL
            IF @value IS NOT NULL
            BEGIN
                SELECT @enc_ftp_password = @value
                EXEC @retcode = master.dbo.xp_repl_encrypt @enc_ftp_password OUTPUT
                IF @retcode <> 0
                BEGIN
                    GOTO UNDO
                END
            END
            UPDATE syspublications
               SET ftp_password = @enc_ftp_password
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END

    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) IN ('independent_agent', 'immediate_sync','enabled_for_internet',
            'allow_push', 'allow_pull', 'allow_anonymous', 'snapshot_in_defaultfolder', 
			'compress_snapshot', 'allow_subscription_copy')
    BEGIN

    
        /*
        ** Check for a valid  value.
        */

        IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
        BEGIN
            RAISERROR (14137, 16, -1)
            GOTO UNDO
        END

        /*
        ** set value bit
        */
        IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
            SELECT @value_bit = 1
        ELSE 
            SELECT @value_bit = 0


		IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'independent_agent'
		BEGIN
			SELECT @prev_value_bit = independent_agent
			FROM syspublications 
			WHERE name = @publication

			IF @prev_value_bit <> @value_bit
			BEGIN

				IF @immediate_sync = 1 AND @value_bit = 0
				BEGIN
					RAISERROR (21022, 16, -1)
					GOTO UNDO
				END    

			/* 
			** no subscriptions are allowed
			*/
			IF EXISTS (SELECT * FROM syssubscriptions ss, sysextendedarticlesview sa
					WHERE ss.artid = sa.artid
						AND   sa.pubid = @pubid
						AND   ss.srvid <> @virtual_id )
			BEGIN
				RAISERROR (20013, 16, -1, @property)
				GOTO UNDO
			END

			--
			-- No share agents for DTS/Updating publications
			--
			if (@value_bit = 0 and 
				(@allow_dts = 1 or @allow_sync_tran = 1 or @allow_queued_tran = 1))			
			begin
				raiserror(21173, 16, -1)
				return(1)
			end

			/* Update the publication type */
			UPDATE syspublications 
			SET independent_agent = @value_bit
			WHERE pubid = @pubid
			IF @@error <> 0
			BEGIN
			GOTO UNDO
			END
			END
		END

        IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'immediate_sync'
        BEGIN


            SELECT @prev_value_bit = immediate_sync
              FROM syspublications 
             WHERE name = @publication

            IF @prev_value_bit <> @value_bit
            BEGIN

               IF @independent_agent = 0 AND @value_bit = 1
               BEGIN
                    RAISERROR (21022, 16, -1)
                    GOTO UNDO
               END    

               /* 
               ** The publication has to be immediate_sync type to
               ** allow anonymous subscriptions
               */
                IF @value_bit = 0 AND
                    EXISTS (SELECT * FROM syspublications
                        WHERE pubid = @pubid
                        AND   allow_anonymous = 1 )
                BEGIN
                    RAISERROR (20011, 16, -1, @property)
                    GOTO UNDO
                END

            
                /* 
                ** If turn on immediate_sync, we need to add virtual subscriptions,
                ** Otherwise, we need to drop them
                ** When adding, we need to change publication bit first
                ** When dropping, we need to change publication bit second
                */
                IF @value_bit = 0
                BEGIN
                    -- Drop virtual subscriptions 
                    EXEC @retcode = dbo.sp_dropsubscription 
                        @publication = @publication, 
                        @article = 'all', 
                        @subscriber = NULL,
                        @reserved = 'internal'
                    IF @@ERROR <> 0 OR @retcode <> 0
                    BEGIN
                        GOTO UNDO
                    END

                    -- Reset the immediate_sync ready bit
                    UPDATE syspublications 
                        SET immediate_sync_ready = 0
                        WHERE pubid = @pubid

                END

                /* Update the publication type */
                UPDATE syspublications 
                    SET immediate_sync = @value_bit
                    WHERE pubid = @pubid
                IF @@error <> 0
                BEGIN
                    GOTO UNDO
                END


                IF @value_bit = 1
                    SELECT @add_virtual_back = 1
            END
        END

        IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'allow_anonymous'
        BEGIN

            SELECT @prev_value_bit = allow_anonymous
              FROM syspublications 
             WHERE name = @publication

            IF @prev_value_bit <> @value_bit
            BEGIN
                /* 
                ** The publication has to be immediate_sync type to
                ** allow anonymous subscriptions
                */
                IF @value_bit = 1 AND
                    NOT EXISTS (SELECT * FROM syspublications
                        WHERE pubid = @pubid
                        AND   immediate_sync = 1 )
                BEGIN
                    RAISERROR (20011, 16, -1, @property)
                    GOTO UNDO
                END
                
                

                /* Drop virtual subscriptions */
                EXEC @retcode = dbo.sp_dropsubscription 
                    @publication = @publication, 
                    @article = 'all', 
                    @subscriber = NULL,
                    @reserved = 'internal'
                IF @@ERROR <> 0 OR @retcode <> 0
                BEGIN
                    GOTO UNDO
                END

                /* Update the publication type */
                UPDATE syspublications 
                    SET allow_anonymous = @value_bit
                    WHERE pubid = @pubid
                IF @@error <> 0
                BEGIN
                   GOTO UNDO
                END

                /* 
                ** add virtual subscriptions back again to enable 
                ** anonymous subscription.
                */
                SELECT @add_virtual_back = 1

            END

        END

        IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'enabled_for_internet'
        BEGIN

            UPDATE syspublications 
               SET enabled_for_internet = @value_bit
             WHERE pubid = @pubid

            IF @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END

        IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'allow_push'
        BEGIN

           /* 
           ** If turn it off, make sure there's no push subscriptions left
           */
           IF @value_bit = 0 AND
            EXISTS (SELECT * FROM syssubscriptions ss, sysextendedarticlesview sa
                    WHERE ss.artid = sa.artid
                    AND   sa.pubid = @pubid
                    AND      ss.subscription_type = @push
                    AND   ss.srvid <> @virtual_id )
            BEGIN
                RAISERROR (20012, 16, -1)
                GOTO UNDO
            END

            
            /* Update the publication type */
            UPDATE syspublications 
                SET allow_push = @value_bit
                WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END

        IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'allow_pull'
        BEGIN
           /* 
           ** If turn it off, make sure there's no pull subscriptions left
           */
           IF @value_bit = 0 AND
            EXISTS (SELECT * FROM syssubscriptions ss, sysextendedarticlesview sa
                    WHERE ss.artid = sa.artid
                    AND   sa.pubid = @pubid
                    AND      ss.subscription_type = @pull
                    AND   ss.srvid <> @virtual_id )
            BEGIN
                RAISERROR (20013, 16, -1, @property)
                GOTO UNDO
            END
            /* Update the publication type */
            UPDATE syspublications 
                SET allow_pull = @value_bit
                WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
               GOTO UNDO
            END
        END

        IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'snapshot_in_defaultfolder'
        BEGIN
            -- snapshot_in_defaultfolder = 1 is only meaningful when
            -- alt_snapshot_folder is non-null, otherwise 
            -- a copy of the snapshot files is always kept
            -- at the publisher's working directory 
    
            UPDATE syspublications 
               SET snapshot_in_defaultfolder = @value_bit
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
                GOTO UNDO
            END
        END

        IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'compress_snapshot'
        BEGIN

            UPDATE syspublications
               SET compress_snapshot = @value_bit
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
                GOTO UNDO
            END
        END

        IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'allow_subscription_copy'
        BEGIN
			if @value_bit = 1 and @immediate_sync = 0
			begin
				raiserror(21210, 16, -1)
				goto UNDO
			end

            UPDATE syspublications
               SET allow_subscription_copy = @value_bit
             WHERE pubid = @pubid
            IF @@error <> 0
            BEGIN
                GOTO UNDO
            END
        END
    END

    /* Update publication property at the distributor side if necessary */
    IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) IN ('description', 'repl_freq', 'independent_agent',
        'immediate_sync', 'allow_push',
        'allow_pull', 'allow_anonymous','retention', 'allow_subscription_copy')
    BEGIN
        /* Translate the property names and values  */
        IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'repl_freq'
        BEGIN
            SELECT @property = 'publication_type'
            SELECT @value = STR(@replfreqid)
        END

        /* Translate values */
        IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
            SELECT @value = '1'
        ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'false'
            SELECT @value = '0'

        /*
        ** Get distribution server information for remote RPC call.
        */
        EXECUTE @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT,
           @distribdb   = @distribdb OUTPUT
        IF @@ERROR <> 0 or @retcode <> 0
            BEGIN
                GOTO UNDO
            END

        SELECT @dbname =  DB_NAME()
        
        SELECT @distproc = RTRIM(@distributor) + '.' + @distribdb + 
            '.dbo.sp_MSchange_publication'
    
        EXECUTE @retcode = @distproc
            @publisher = @@SERVERNAME,
            @publisher_db = @dbname,
            @publication = @publication,
            @property = @property,
            @value = @value

        IF @@ERROR <> 0 OR @retcode <> 0
        BEGIN
            GOTO UNDO
        END
    END
    
    IF @add_virtual_back = 1    
    BEGIN
        /* Add virtual subscriptions back*/
        EXEC @retcode = dbo.sp_addsubscription 
            @publication = @publication, 
            @article = 'all',
            @subscriber = NULL,
            @destination_db = 'virtual',
            @sync_type = 'automatic',
            @status = NULL, 
            @reserved = 'internal'
        IF @@ERROR <> 0 OR @retcode <> 0
        BEGIN
            GOTO UNDO                    
        END
    END

	--
	-- Queued properties
	--
	IF (LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) IN ('conflict_policy', 'centralized_conflicts', 'conflict_retention', 'queue_type'))
	BEGIN
		--
		-- we will consider changes only if the publication supports queued operations
		--
		if exists (select * from syspublications 
				where pubid = @pubid and allow_queued_tran = 1)
		BEGIN
			IF (LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'conflict_policy')
			BEGIN
				if (LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'sub reinit')
					select @conflict_policy_id = 3
				else if (LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'pub wins')
					select @conflict_policy_id = 1
				else if (LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'sub wins')
					select @conflict_policy_id = 2
				else
				BEGIN
					raiserror (21184, 16, 3, 'conflict_policy', 'sub reinit', 'pub wins', 'sub wins')
					GOTO UNDO                    
				END

				--
				-- cannot change this parameter once we have subscriptions
				--
				IF EXISTS (SELECT * FROM syssubscriptions ss, sysextendedarticlesview sa
								WHERE ss.artid = sa.artid
								AND   sa.pubid = @pubid)
				BEGIN
					RAISERROR (21268, 16, 1)
					GOTO UNDO
				END

				--
				-- Check snapshot permissible values
				--
				if ((@replfreqid = 1) and (@conflict_policy_id = 1))
				begin
					raiserror (21270, 16, 1, '@conflict_policy', @value)
					GOTO UNDO                    
				end
				
				UPDATE syspublications
				SET conflict_policy = @conflict_policy_id
				WHERE pubid = @pubid
				IF @@error <> 0
				BEGIN
					GOTO UNDO
				END
			END

			IF (LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'centralized_conflicts')
			BEGIN
				if (LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true')
					select @centralized_conflicts_bit = 1
				else if (LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'false')
					select @centralized_conflicts_bit = 0
				else
				begin
					raiserror (14148, 16, 3, 'centralized_conflicts')
					GOTO UNDO                    
				end

				--
				-- Check snapshot permissible values
				--
				if ((@replfreqid = 1) and (@centralized_conflicts_bit = 0))
				begin
					raiserror (21270, 16, 1, '@centralized_conflicts', @value)
					GOTO UNDO                    
				end

				--
				-- cannot change this parameter once we have subscriptions
				--
				IF EXISTS (SELECT * FROM syssubscriptions ss, sysextendedarticlesview sa
								WHERE ss.artid = sa.artid
								AND   sa.pubid = @pubid)
				BEGIN
					RAISERROR (21268, 16, 2)
					GOTO UNDO
				END

				UPDATE syspublications
				SET centralized_conflicts = @centralized_conflicts_bit
				WHERE pubid = @pubid
				IF @@error <> 0
				BEGIN
					GOTO UNDO
				END
			END

			IF (LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'conflict_retention')
			BEGIN
				select @conflict_retention = CAST(@value as integer)
				if (@@error != 0) or (@conflict_retention < 0)
				BEGIN
					raiserror(20050, 16, -1, 0)
					GOTO UNDO                    
				END
				if (@conflict_retention IS NULL)
					select @conflict_retention = 60

				UPDATE syspublications
				SET conflict_retention = @conflict_retention
				WHERE pubid = @pubid
				IF @@error <> 0
				BEGIN
					GOTO UNDO
				END
			END
			
			IF (LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'queue_type')
			BEGIN				
				IF (CAST(@value as integer) NOT IN (1,2))
				BEGIN
					RAISERROR(21267, 16, 1, '1,2')
					GOTO UNDO
				end

				IF (CAST(@value as integer) != @queue_type)
				BEGIN
					select @queue_type = CAST(@value as integer)
					IF EXISTS (SELECT * FROM syssubscriptions ss, sysextendedarticlesview sa
									WHERE ss.artid = sa.artid
									AND   sa.pubid = @pubid)
					BEGIN
						RAISERROR (21268, 16, 3)
						GOTO UNDO
					END

					UPDATE syspublications
					SET queue_type = @queue_type
					WHERE pubid = @pubid
					IF @@error <> 0
					BEGIN
						GOTO UNDO
					END

					--
					-- For MSMQ queue_type - Check if the distributor supports it
					--
					if (@queue_type = 1)
					begin
						EXECUTE @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT,
												@distribdb   = @distribdb OUTPUT
						IF @@ERROR <> 0 or @retcode <> 0
						BEGIN
							GOTO UNDO
						END

						SELECT @dbname =  DB_NAME()
								,@distproc = RTRIM(@distributor) + '.' + @distribdb + 
											N'.dbo.sp_MSchange_publication'

						EXECUTE @retcode = @distproc
										@publisher = @@SERVERNAME,
										@publisher_db = @dbname,
										@publication = @publication,
										@property = @property,
										@value = @value

						IF @@ERROR <> 0 OR @retcode <> 0
						BEGIN
							GOTO UNDO
						END
					end
				END
			END			
		END
	END

	
	-- Have to call this stored procedure to invalidate existing snapshot or reint
	-- subscriptions if needed
	EXECUTE @retcode  = dbo.sp_MSreinit_article
		@publication = @publication, 
		@need_new_snapshot = @need_new_snapshot,
		@need_reinit_subscription = @need_reinit_subscription
		,@force_invalidate_snapshot = @force_invalidate_snapshot	/* Force invalidate existing snapshot */
		,@force_reinit_subscription = @force_reinit_subscription	/* Force reinit subscription */
	IF @@ERROR <> 0 OR @retcode <> 0
		GOTO UNDO

	COMMIT TRAN sp_changepublication

	--update its registration in active directory
	if @in_ActiveD=1 and LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) IN ('description','allow_pull', 'allow_anonymous')
	begin
		create table #guid_name_for_ADupdate(ad_guidname sysname collate database_default null)
		if @@ERROR<>0
		begin
            goto FAILURE
		end
		insert into #guid_name_for_ADupdate exec @retcode = master.dbo.sp_ActiveDirectory_Obj N'UPDATE', N'PUBLICATION', @publication, @db_name, @ad_guidname
		if @@ERROR<>0 or @retcode<>0
    	begin
    		goto FAILURE 					
	   	end
    	select TOP 1 @ad_guidname = ad_guidname from #guid_name_for_ADupdate
    	if @ad_guidname is not NULL
    	begin
    		update syspublications set ad_guidname=@ad_guidname where pubid=@pubid
    		if @@ERROR<>0
    			goto FAILURE
    	end
	    drop table #guid_name_for_ADupdate
    end

    /*
    ** Return succeed.
    */

    RAISERROR (14077, 10, -1)
    RETURN (0)
FAILURE:
	drop table #guid_name_for_ADupdate
    raiserror(21371, 10, -1, @publication)
    return (1)   

UNDO:
    IF @@TRANCOUNT > 0
    begin 
        ROLLBACK TRAN sp_changepublication
        COMMIT TRAN
    end
END
GO
 
EXEC dbo.sp_MS_marksystemobject sp_changepublication
GO

print ''
print 'Creating procedure sp_changesubscription'
GO
/* This function should be disallowed */
CREATE PROCEDURE sp_changesubscription (
    @publication sysname = NULL,        /* Publication name */
    @article sysname = NULL,            /* Article name */
    @subscriber sysname,              /* Subscriber name */
    @property nvarchar(15) = NULL,           /* The property to change */
    @value nvarchar(255) = NULL              /* The new property value */
    ) AS

    SET NOCOUNT ON
    RAISERROR (21023, 16, -1,'sp_changesubscription')
    RETURN(1)
go
 
dump tran master with no_log
go

EXEC dbo.sp_MS_marksystemobject sp_changesubscription
GO


print ''
print 'Creating procedure sp_helparticle'
go

CREATE PROCEDURE sp_helparticle (
    @publication sysname,         /* The publication name */
    @article sysname = '%',       /* The article name */
    @returnfilter bit = 1         /* Return filter flag */
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @pubid int
    DECLARE @retcode int
    DECLARE @subscriber_bit smallint
    DECLARE @publish_bit int
    DECLARE @source_object  sysname
    DECLARE @source_owner   sysname
    DECLARE @username       sysname
    
    SELECT @publish_bit = 1
    SELECT @username = suser_sname()

    /*
    ** Parameter Check:  @publication.
    ** Check to make sure that publication exists.
    */

    IF @publication IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@publication')
            RETURN (1)
        END

    EXECUTE @retcode = dbo.sp_validname @publication

    IF @retcode <> 0
        RETURN (1)

    IF NOT EXISTS (SELECT * FROM syspublications WHERE name = @publication)
        BEGIN
            RAISERROR (20026, 11, -1, @publication)
            RETURN (1)
        END

    /*
    ** Security Check. Restrict to 'sysadmin', DBO of publishing database, PAL
    */
    IF is_member(N'db_owner') <> 1
    BEGIN
        exec @retcode = sp_MSreplcheck_pull
            @publication = @publication,
            @given_login = @username
        IF @retcode <> 0 OR @@error <> 0
            RETURN (1)
    END
    
    /*
    ** Check if the database is published.
    */
    IF NOT EXISTS (SELECT * FROM master..sysdatabases
        WHERE name = db_name() collate database_default
        AND (category & @publish_bit) = @publish_bit)
        RETURN(0)

    /*
    ** Initializations.
    */

    SELECT @subscriber_bit = 4

    IF @publication IS NOT NULL
        SELECT @pubid = pubid FROM syspublications WHERE name = @publication

    /*
    ** Create a temporary table to hold all information.
    */

    CREATE TABLE #tab1 (
        artid               int             NOT NULL,
        columns             varbinary(32)   NULL,
        creation_script     nvarchar(255)   collate database_default null,
        del_cmd             nvarchar(255)   collate database_default null,
        description         nvarchar(255)   collate database_default null,
        dest_table          sysname         collate database_default null,
        old_filter          int             NULL,
        ins_cmd             nvarchar(255)   collate database_default null,
        name                sysname         collate database_default not null,
        objid               int             NOT NULL,
        pubid               int             NOT NULL,
        status              tinyint         NOT NULL,
        sync_objid          int             NULL,
        type                smallint        NOT NULL,
        upd_cmd             nvarchar(255)   collate database_default null,
        source_table        nvarchar(257)   collate database_default null,      /* converted from objid */
        filter              nvarchar(257)   collate database_default null,      /* converted from old_filter */
        sync_object         nvarchar(257)   collate database_default null,      /* converted from sync_objid */
        vpartition          bit             NULL,      /* computed */
        pre_creation_cmd    tinyint     NOT NULL,
        filter_clause       ntext           NULL,
        schema_option       binary(8)       NULL,
        dest_owner          sysname         collate database_default null,
        source_owner        sysname         collate database_default null,   /* these two columns are for 7.0 use only */
        unqua_source_object sysname         collate database_default null,   /* column source_table stays due to backward compatibility */
        sync_object_owner   sysname         collate database_default null,
        unqua_sync_object   sysname         collate database_default null,
        filter_owner        sysname         collate database_default null,
        unqua_filter        sysname         collate database_default null
    )

    CREATE UNIQUE INDEX idx1 ON #tab1 (name, pubid)

    /*
    ** Parameter Check:  @article.
    ** Check to make sure that the article exists, that it conforms
    ** to the rules for identifiers, and that it isn't NULL.
    */

    IF @article IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@article')
            RETURN (1)
        END

    IF @article <> '%'
        BEGIN

            /*
            EXECUTE @retcode = dbo.sp_validname @article

            IF @retcode <> 0
            RETURN (1)
            */

            IF NOT EXISTS (SELECT *
                             FROM sysextendedarticlesview
                            WHERE name = @article
                              AND pubid IN (SELECT pubid
                                              FROM syspublications
                                             WHERE name = @publication))
                BEGIN
                    RAISERROR (20027, 11, -1, @article)
                    RETURN (1)
                END

        END

        
    IF @returnfilter = 1
    BEGIN
        INSERT INTO #tab1 (artid, columns, creation_script, del_cmd,
                           description, dest_table, old_filter,
                           ins_cmd, name, objid, pubid, status,
                           sync_objid, type, upd_cmd, source_table,
                           filter, vpartition, pre_creation_cmd,
               filter_clause, schema_option, dest_owner, source_owner, unqua_source_object, 
               sync_object_owner, unqua_sync_object, filter_owner, unqua_filter)
               
         (SELECT artid, columns, creation_script, del_cmd, a.description,
                 dest_table, filter, ins_cmd, a.name, objid, a.pubid,
                 a.status, sync_objid, a.type, upd_cmd, NULL, NULL, 0,
                a.pre_creation_cmd, a.filter_clause, a.schema_option, a.dest_owner, 
                user_name(o.uid), o.name, 
                user_name(sync.uid), sync.name,
                user_name(fltr.uid), fltr.name
            FROM syspublications b, 
                 sysobjects o, 
                 sysextendedarticlesview a 
          LEFT JOIN sysobjects sync on a.sync_objid = sync.id
          LEFT JOIN sysobjects fltr on a.filter = fltr.id
           WHERE a.name LIKE @article
             AND a.objid = o.id
             AND a.pubid = b.pubid
             AND b.name = @publication)
    END
    ELSE
    BEGIN
        INSERT INTO #tab1 (artid, columns, creation_script, del_cmd,
                           description, dest_table, old_filter,
                           ins_cmd, name, objid, pubid, status,
                           sync_objid, type, upd_cmd, source_table,
                           filter, vpartition, pre_creation_cmd,
               filter_clause, schema_option, dest_owner, source_owner, unqua_source_object, 
               sync_object_owner, unqua_sync_object, filter_owner, unqua_filter)
         (SELECT artid, columns, creation_script, del_cmd, a.description,
                 dest_table, filter, ins_cmd, a.name, objid, a.pubid,
                 a.status, sync_objid, a.type, upd_cmd, NULL, NULL, 0,
                 a.pre_creation_cmd, NULL, schema_option, dest_owner, 
                 user_name(o.uid), o.name,
                 user_name(sync.uid), sync.name,
                 user_name(fltr.uid), fltr.name
           FROM syspublications b, 
                sysobjects o, 
                sysextendedarticlesview a 
           LEFT JOIN sysobjects fltr on a.filter = fltr.id
           LEFT JOIN sysobjects sync on a.sync_objid = sync.id
           WHERE a.name LIKE @article
             AND a.objid = o.id
             AND a.pubid = b.pubid
             AND b.name = @publication)
    END

    UPDATE #tab1
       SET source_table = QUOTENAME(u.name) + '.' + QUOTENAME(o.name)
      FROM #tab1, sysobjects o, sysusers u
     WHERE o.id = #tab1.objid
       AND o.uid = u.uid

    UPDATE #tab1
        SET unqua_sync_object = sysobjects.name
        from sysobjects 
        where sysobjects.id = sync_objid

    UPDATE #tab1
       SET sync_object = QUOTENAME(sysusers.name) + '.' + QUOTENAME(sysobjects.name)
      FROM sysobjects, sysusers
     WHERE sysobjects.id = sync_objid
       AND sysobjects.uid = sysusers.uid

    UPDATE #tab1 SET filter = (SELECT sysusers.name + '.' + sysobjects.name
                                 FROM sysobjects, sysusers
                                WHERE sysobjects.id = #tab1.old_filter
                                  AND sysobjects.uid = sysusers.uid)
      FROM #tab1

    DECLARE hC  CURSOR LOCAL FAST_FORWARD FOR SELECT name, pubid FROM #tab1
    OPEN hC
    FETCH hC INTO @article, @pubid
    WHILE (@@fetch_status <> -1)
        BEGIN
            IF EXISTS (SELECT *
                         FROM sysextendedarticlesview a, syscolumns b
                         WHERE 
                          ( 
                           convert(bit, convert( varbinary, substring( convert( nvarchar, a.columns ), 16 - floor((colid-1)/16),1 )) & power( 2, ((colid-1)%16))) = 0
                           OR convert(bit, convert( varbinary, substring( convert( nvarchar, a.columns ), 16 - floor((colid-1)/16),1 )) & power( 2, ((colid-1)%16))) IS NULL
                          )
                          AND a.objid = b.id
                          AND a.name = @article
                          AND a.pubid = @pubid)

                UPDATE #tab1
                   SET vpartition = 1
                 WHERE name = @article
                   AND pubid = @pubid

            FETCH hC INTO @article, @pubid
        END
    CLOSE hC
    DEALLOCATE hC

    SELECT 'article id'                = art.artid,
       'article name'              = name,
       'base object'                = source_table,
       'destination object'         = dest_table,
       'synchronization object'    = sync_object,
       'type'                      = case 
                                        when objectproperty(art.objid, 'IsSchemaBound') = 1 and type <> 0x80 then 0x0100 | convert(smallint, type)
                                        else type 
                                     end,    
	   -- bit 32 indicates whether or not timestamp is included in partition for queue publications
	   -- It is internal and should be hidden from DMO scripting, since it is not allowed to 
	   -- be set in sp_addarticle
	   -- sp_helparticlecolumns will use that bit to return proper information.
       'status'                    = status & ~32,
       'filter'                    = filter,
       'description'               = description,
       'insert_command'            = ins_cmd,
       'update_command'            = upd_cmd,
       'delete_command'            = del_cmd,
       'creation script path'      = creation_script,
       'vertical partition'        = vpartition,
       'pre_creation_cmd'       = pre_creation_cmd,
	   -- filter_clause is null when @return_filter is 0
       'filter_clause'           = filter_clause,
       'schema_option'            = schema_option,
       'dest_owner'             = dest_owner,
       'source_owner'           = source_owner,
       'unqua_source_object'    = unqua_source_object,
       'sync_object_owner'      = sync_object_owner,
       'unqualified_sync_object' = unqua_sync_object,
       'filter_owner'           = filter_owner,
       'unqua_filter'           = unqua_filter,
	   'auto_identity_range'	= isnull(artupd.identity_support, 0),
	   'publisher_identity_range' = abs(iden.pub_range),
	   'identity_range'	= abs(iden.range),
	   'threshold' = iden.threshold
      FROM #tab1 art
		left join sysarticleupdates artupd on art.artid = artupd.artid
		left join MSpub_identity_range iden on art.objid = iden.objid
      ORDER BY 2

    RETURN (0)
go

dump tran master with no_log
go

EXEC dbo.sp_MS_marksystemobject sp_helparticle
GO


print ''
print 'Creating procedure sp_MSis_col_replicated'
go
create procedure sp_MSis_col_replicated @publication sysname, 
    @article sysname, 
    @coltype nvarchar(10) = 'timestamp',  -- identity or timestamp or rowguid
    @colname sysname = NULL OUTPUT 
as
    declare @word tinyint, 
        @bit tinyint, 
        @mask binary(2), 
        @mval int,
        @colword binary(2), 
        @columns binary(32),
        @firstcol tinyint, 
        @colid smallint,
        @tabid int,
        @pubid int
        

    select @colname = NULL

    select @pubid = pubid from syspublications where name = @publication

    select @tabid = objid from sysarticles 
        where name = @article and pubid = @pubid
    
    if @coltype = 'timestamp'
    begin
        if ObjectProperty(@tabid, 'TableHasTimestamp') = 1 
        begin

            select @colname = name, @colid = colid from syscolumns 
                where id = @tabid and type_name(xtype) = 'timestamp' 
            
            if @colname is not NULL
            begin                       
                -- check if  timestamp is replicated
                --  Obtain the byte offset and the bit offset, then set the
                select @columns=columns from sysarticles where name = @article and pubid = @pubid
                select @word = CONVERT(tinyint, 16 - FLOOR((@colid-1)/16))
                select @bit = (@colid-1) % 16
                select @mval = POWER(2, @bit)
                select @mask = convert( binary(2), substring( convert( nchar(2), convert( binary(4), @mval ) ), 2, 1 ) )                                 
    
                -- Fish out the byte we're interested in and save it in a
                -- a temporary local variable.  
                select @colword = convert( binary(2), SUBSTRING( convert(nchar(16),@columns), @word, 1) )
    
                if convert( smallint, @colword ) & convert( smallint, @mask) = 0 
                begin
                    select @colname = NULL
                    return (0)
                end
                else
                    return (1)
            end
            else
            begin
                select @colname = NULL
                return (0)
            end
        end
        else
        begin
            select @colname = NULL
            return (0)
        end
    end
    else if @coltype = 'identity'
    begin
        if ObjectProperty(@tabid, 'TableHasIdentity') = 1 
        begin
            select @colname = name, @colid = colid from syscolumns 
            where id = @tabid and ColumnProperty(@tabid, name, 'IsIdentity') = 1
            
            if @colname is not NULL
            begin                       
                -- check if column is replicated
                --  Obtain the byte offset and the bit offset, then set the
                select @columns=columns from sysarticles where name = @article and pubid = @pubid
                select @word = CONVERT(tinyint, 16 - FLOOR((@colid-1)/16))
                select @bit = (@colid-1) % 16
                select @mval = POWER(2, @bit)
                select @mask = convert( binary(2), substring( convert( nchar(2), convert( binary(4), @mval ) ), 2, 1 ) )                                 

                -- Fish out the byte we're interested in and save it in a
                -- a temporary local variable.  
                select @colword = convert( binary(2), SUBSTRING( convert(nchar(16),@columns), @word, 1) )
                
                if convert( smallint, @colword ) & convert( smallint, @mask) = 0 
                begin
                    select @colname = NULL
                    return (0)
                end
                else
                    return (1)
            end
            else
            begin
                select @colname = NULL
                return (0)
            end
        end
	end
    else if @coltype = 'rowguid'
    begin
        if ObjectProperty(@tabid, 'TableHasRowGuid') = 1 
        begin
            select @colname = name, @colid = colid from syscolumns 
            where id = @tabid and ColumnProperty(@tabid, name, 'IsRowGuidCol') = 1
            
            if @colname is not NULL
            begin                       
                -- check if column is replicated
                --  Obtain the byte offset and the bit offset, then set the
                select @columns=columns from sysarticles where name = @article and pubid = @pubid
                select @word = CONVERT(tinyint, 16 - FLOOR((@colid-1)/16))
                select @bit = (@colid-1) % 16
                select @mval = POWER(2, @bit)
                select @mask = convert( binary(2), substring( convert( nchar(2), convert( binary(4), @mval ) ), 2, 1 ) )                                 

                -- Fish out the byte we're interested in and save it in a
                -- a temporary local variable.  
                select @colword = convert( binary(2), SUBSTRING( convert(nchar(16),@columns), @word, 1) )
                
                if convert( smallint, @colword ) & convert( smallint, @mask) = 0 
                begin
                    select @colname = NULL
                    return (0)
                end
                else
                    return (1)
            end
            else
            begin
                select @colname = NULL
                return (0)
            end
        end

        else
        begin
            select @colname = NULL
            return (0)
        end
    end
	else
    begin
        select @colname = NULL
        return (0)    
    end
go

EXEC dbo.sp_MS_marksystemobject sp_MSis_col_replicated
GO

print ''
print 'Creating procedure sp_articlecolumn'
go
CREATE PROCEDURE sp_articlecolumn (
        @publication sysname,           /* The publication name */
        @article sysname,               /* The article name */
        @column sysname = NULL,         /* The column name */
        @operation nvarchar(4) = N'add'      /* Add or delete a column */
        -- synctran
        , @refresh_synctran_procs bit = 1      -- refresh synctran procs or not
		, @ignore_distributor bit = 0
		-- DDL
		, @change_active int = 0
		, @force_invalidate_snapshot bit = 0	/* Force invalidate existing snapshot */
		, @force_reinit_subscription bit = 0	/* Force reinit subscription */

        ) 
AS
BEGIN

    /*
    ** Declarations.
    */

	DECLARE @bit tinyint                /* Bit offset */
			,@word tinyint               /* word offset */
			,@cnt tinyint, @idx tinyint  /* Loop counter, index */
			,@columns binary(32)         /* Temporary storage for the converted column */
			,@mask binary(2)              /* Bit mask to set the bit on */
			,@mval int
			,@newword binary(2)
			,@oldword binary(2)
			,@pubid int                  /* Publication identification number */
			,@retcode int                /* Return code for stored procedures */
			,@artid int
			,@active tinyint
			,@objid int            /* Article base table id */    
			,@tablename  sysname
			,@fSynctranColChanged bit
			,@pkkey sysname
			,@indid int
			,@index_cnt int

	select @active = 2
			,@fSynctranColChanged = 0

    /*
    ** Security Check
    */
    exec @retcode = dbo.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    /*
    ** Check to see if the database has been activated for publication.
	** Do not check if @ignore_distributor indicates brute force cleanup.
    */

    IF ( (SELECT category & 1
          FROM master..sysdatabases
         WHERE name = DB_NAME() collate database_default) = 0 )  and ( @ignore_distributor = 0 )

    BEGIN
        RAISERROR (14013, 16, -1)
        RETURN (1)
    END

    /*
    ** Parameter Check:  @publication.
    ** Make sure that the publication exists and that it conforms to the
    ** rules for identifiers.
    */

    IF @publication IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, N'@publication')
            RETURN (1)
        END

    EXECUTE @retcode = dbo.sp_validname @publication

    IF @retcode <> 0
            RETURN (1)

	declare @allow_queued_tran bit
	declare @allow_sync_tran bit

    SELECT @pubid = pubid, 
		@allow_sync_tran = allow_sync_tran,
		@allow_queued_tran = allow_queued_tran
		FROM syspublications WHERE name = @publication

    IF @pubid IS NULL
        BEGIN
            RAISERROR (20026, 11, -1, @publication)
            RETURN (1)
        END
    ELSE

    /*
    ** Parameter Check:  @article.
    ** Check to make sure that the article exists in the publication.
    */

    IF @article IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, N'@article')
            RETURN (1)
        END

    /*
    EXECUTE @retcode = dbo.sp_validname @article

    IF @@ERROR <> 0 OR @retcode <> 0
    RETURN (1)
    */

    /*
    ** Make sure the article exists.
    */
    SELECT @artid = artid FROM sysarticles
       WHERE pubid = @pubid AND name = @article
    IF @artid IS NULL
        BEGIN
            RAISERROR (20027, 11, -1, @article)
            RETURN (1)
        END


    /*
    ** Error out if this is a not a table based article
    */
    IF NOT EXISTS ( SELECT * FROM sysarticles WHERE artid = @artid
                      AND pubid = @pubid
                      AND (type & 1) = 1 )
        BEGIN
            RAISERROR (14112, 11, -1 )
            RETURN (1)
        END

    /*
    ** Parameter Check:  @column.
    ** Check to make sure that the column exists and conforms to the rules
    ** for identifiers.
    */

    /*
    IF @column IS NOT NULL
        BEGIN
            EXECUTE @retcode = dbo.sp_validname @column
            IF @@ERROR <> 0 OR @retcode <> 0
            RETURN (1)
        END
    */

    /*
    ** Parameter Check:  @operation.
    ** The operation can be either 'add' or 'drop'.
    */

    IF LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS) NOT IN (N'add', N'drop')
        BEGIN
            RAISERROR (14019, 16, -1)
            RETURN (1)
        END
        
    SELECT @objid = (SELECT objid FROM sysarticles WHERE artid = @artid)
    SELECT @tablename = OBJECT_NAME(@objid)
   
	if @column is not null
	begin
		declare @colid	smallint
		select @colid=colid from syscolumns where id=@objid and name=@column
		if @colid is null
		begin
            RAISERROR (14020, 16, -1)
            RETURN (1)
		end

	if LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS) = 'drop'
	begin		
		-- Vertical partition is only allowed on table-based article, not IV->table
	    	IF OBJECTPROPERTY(@objid, 'IsTable') <> 1
	        BEGIN
        	    RAISERROR (14112, 11, -1 )
           	    RETURN (1)
	        END
		-- PK column has to be included in vertical partition
		select @indid = indid from sysindexes where id = @objid and (status & 2048) <> 0    /* PK index */
		select @index_cnt = 1
		while (@index_cnt <= 16)
			begin
				select @pkkey = INDEX_COL(@tablename, @indid, @index_cnt)
				if @pkkey is NULL
					break
				if @pkkey=@column
					begin
						raiserror(21250, 16, -1, @column)
						return (1)
					end
				select @index_cnt = @index_cnt + 1
			end
	end

		-- If the publication is allow_sync_tran, we cannot drop the timestamp
		-- column from the partition.
		if (@allow_sync_tran = 1 or @allow_queued_tran = 1) and LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS) = N'drop'
		begin
			if N'msrepl_tran_version' = @column
            BEGIN
                RAISERROR (21080, 16, -1)
                RETURN (1)
            END
		end

		-- Only columns that have default values can be outside the partition
		-- Note: do check error if it is schema change.
		if @change_active = 0 and LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS)=N'drop' and 
			(@allow_queued_tran = 1 or @allow_sync_tran = 1) and
			ColumnProperty(@objid, @column, N'IsIdentity') <> 1 and
			-- 189 is timestamp.
			not exists (select * from syscolumns where id = @objid and 
				name=@column and (isnullable=1 or xtype = 189)) and
			not exists (select * from sysconstraints where id=@objid and 
				colid=@colid and status & 5 = 5)
		BEGIN
			RAISERROR(21165, 16, -1, @column)
			return (1)
		END
	end

	-- @ignore_distributor is set to 1 when removing replication forcefully. In that
	-- case, no need to check or reinit
	if @ignore_distributor = 0
	begin
		-- Check if there are snapshot or subscriptions and raiserror if needed.
		EXECUTE @retcode  = dbo.sp_MSreinit_article
			@publication = @publication, 
			@article = @article,
			@need_new_snapshot = 1,
			@need_reinit_subscription = 1
			,@force_invalidate_snapshot = @force_invalidate_snapshot	/* Force invalidate existing snapshot */
			,@force_reinit_subscription = @force_reinit_subscription	/* Force reinit subscription */
			,@check_only = 1
		IF @@ERROR <> 0 OR @retcode <> 0
			return (1)
	end

	begin tran
    save TRANSACTION articlecolumn

    /*
    ** Make sure that the columns column is not NULL.
    */

--    SELECT @zero = 0x00

    SELECT @columns = columns
      FROM sysarticles
     WHERE artid = @artid

    IF @columns IS NULL
        UPDATE sysarticles
           SET columns = 0x00
         WHERE artid = @artid

    /*
    ** If no columns are specified, or if NULL is specified, set all
    ** the bits in the 'columns' column so all columns will be included.
    */

    IF @column IS NULL
    BEGIN
       DECLARE hCartcolumn CURSOR LOCAL FAST_FORWARD FOR
            SELECT name FROM syscolumns where
				id = @objid
    END
	ELSE
    BEGIN
       DECLARE hCartcolumn CURSOR LOCAL FAST_FORWARD FOR 
            SELECT @column
	END


    OPEN hCartcolumn

    FETCH hCartcolumn INTO @column

	WHILE (@@fetch_status <> -1)
	BEGIN

		DECLARE @columnid smallint   /* Columnid-1 = bit to set */
		/*
		** Get the column id for this column.  We'll use the column id
		** to determine the bit in the 'columns' column.  The bit we want
		** is equal to the columnid - 1.
		*/

		SELECT @columnid = colid
		FROM syscolumns
		WHERE id = @objid AND name = @column

		IF ((@@error <> 0) OR (@columnid IS NULL))
		BEGIN
			if @@trancount > 0
			begin
				ROLLBACK TRANSACTION articlecolumn
				commit tran
			end
			RAISERROR (14020, 16, -1)
			RETURN (1)
		END


		if @allow_queued_tran = 1 and 
		exists (select * from syscolumns WHERE id = @objid and xtype = 189 and name = @column)
		begin
			--
			-- For queued publication, don't mark the timestamp column in the column bitmap
			-- Refer to sp_helparticlecolumns
			--
			IF lower(@operation collate SQL_Latin1_General_CP1_CS_AS) = N'add'
				-- Set bit to indicate the timestamp column should be scripted out
				-- Also need to set to use explicit column name list at the same time.
				update sysarticles set status = (status | 32) | 8  where artid = @artid
			else
				-- Clear the bit
				update sysarticles set status = status & ~32 where artid = @artid

			if @@error <> 0
			BEGIN
				if @@trancount > 0
				begin
					ROLLBACK TRANSACTION articlecolumn
					commit tran
				end
				RETURN (1)
			END

			-- mark for synctran proc refresh
			select @fSynctranColChanged = 1			
		end
		else
		begin

			/*
			** Obtain the byte offset and the bit offset, then set the
			** mask column for the bit we want to turn on.
			*/

			SELECT @word = CONVERT(tinyint, 16 - FLOOR((@columnid-1)/16))
			SELECT @bit = (@columnid-1) % 16

			IF LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS) = N'add'
				SELECT @mval = POWER(2, @bit)
			ELSE
				SELECT @mval = ~POWER(2, @bit)

			select @mask = convert( binary(2), substring( convert( nchar(2), convert( binary(4), @mval ) ), 2, 1 ) )

			/*
			** Save the columns column in a temporary local variable so we
			** can twiddle the bit and then put it back into the table.
			*/

			SELECT @columns = columns
			FROM sysarticles
			WHERE name = @article AND pubid = @pubid
			if(@change_active = 2) -- Only post if it came from sp_repladd(drop)column
			begin
				exec sp_replpostcmd 0, @pubid, @artid, 51, @columns
			end

			/*
			** Fish out the byte we're interested in and save it in a
			** a temporary local variable.  If it's NULL, just set it
			** to 0.  Then apply the bitwise operator OR to twiddle the
			** bit in the old byte and save it in another temporary
			** local variable @newbyte.
			*/
			SELECT @oldword = CONVERT( binary(2), SUBSTRING( CONVERT( nvarchar,@columns), @word, 1) )

			IF @oldword IS NULL SELECT @oldword = 0x0000

			IF LOWER(@operation collate SQL_Latin1_General_CP1_CS_AS) = N'add'
				SELECT @newword = CONVERT(binary(2), convert(smallint, @oldword) | @mask)
			ELSE
				SELECT @newword = CONVERT(binary(2), convert(smallint, @oldword ) & @mask)

			SELECT @columns = CONVERT(binary(32), STUFF( convert(nchar(16),@columns), @word, 1, convert( nchar(1), @newword)))
			SELECT @idx = @idx + 1

			/*
			** Update the sysarticles table.  Set the bit appropriately for the selected column
			*/

			UPDATE sysarticles
			SET columns = @columns
			WHERE name = @article
			AND pubid = @pubid

			IF @@error <> 0
			BEGIN
				if @@trancount > 0
				begin
					ROLLBACK TRANSACTION articlecolumn
					commit tran
				end
				RAISERROR (14021, 16, -1)
				RETURN (1)
			END

			/* 
			** if the status has changed, call sp_MSarticlecol to update the publication
			** status as appropriate.
			*/

			IF @oldword != @newword
			BEGIN
				/* Update column published status */
				EXECUTE @retcode = dbo.sp_MSarticlecol @artid, @columnid,
										N'publish', @operation
				IF (@@error <> 0 OR @retcode <> 0)
				BEGIN
					if @@trancount > 0
					begin
						ROLLBACK TRANSACTION articlecolumn
						commit tran
					end
					RAISERROR (14021, 16, -1)
					RETURN (1)
				END
				select @fSynctranColChanged = 1
			END

		end -- end of if else block

		-- fetch the next column
		FETCH hCartcolumn INTO @column

	END -- end of while block

    -- Synctran
    /*
    ** If publication is enabled for Synctran and sprocs are auto-generated - regenerate them
    */
	declare @autogen_sync_procs_id bit
		,@ins_proc_id int, @upd_proc_id int, @del_proc_id int, @upd_trig_id int
		,@ins_proc sysname, @upd_proc sysname, @del_proc sysname, @owner sysname, @objname sysname
		,@upd_trig sysname
		,@sync_pubid int
		,@conflict_table_id int, @ins_conflict_proc int
		,@cmd nvarchar(4000)

    select @autogen_sync_procs_id = autogen_sync_procs, @sync_pubid = pubid
    from syspublications where name = @publication

    if  @autogen_sync_procs_id = 1 and @refresh_synctran_procs = 1 and @fSynctranColChanged = 1
    begin
        -- Drop existing synctran procs
        select @owner = user_name(OBJECTPROPERTY(objid, N'OwnerId')) from sysarticles a, syspublications p
        where a.name = @article and
              p.name = @publication and
              a.pubid = p.pubid

        select 	@ins_proc_id = sync_ins_proc, 
        		@upd_proc_id = sync_upd_proc, 
        		@del_proc_id = sync_del_proc, 
				@upd_trig_id = sync_upd_trig,
				@conflict_table_id = conflict_tableid,
				@ins_conflict_proc = ins_conflict_proc
        from sysarticleupdates
	        where pubid = @pubid and artid = @artid

        if @ins_proc_id is not null
        begin
            select @objname = object_name(@ins_proc_id)     
            exec @retcode = dbo.sp_MSdrop_object
                @object_name = @objname,
                @object_owner = @owner
            if @@error <> 0 or @retcode <> 0
                goto UNDO
        end

        if @upd_proc_id is not null
        begin
            select @objname = object_name(@upd_proc_id)     
            exec @retcode = dbo.sp_MSdrop_object
                @object_name = @objname,
                @object_owner = @owner
            if @@error <> 0 or @retcode <> 0
                goto UNDO
        end

        if @del_proc_id is not null
        begin
            select @objname = object_name(@del_proc_id)     
            exec @retcode = dbo.sp_MSdrop_object
                @object_name = @objname,
                @object_owner = @owner
            if @@error <> 0 or @retcode <> 0
                goto UNDO
        end

		if @upd_trig_id is not null
        begin
            select @objname = object_name(@upd_trig_id)     
            exec @retcode = dbo.sp_MSdrop_object
                @object_name = @objname,
                @object_owner = @owner
            if @@error <> 0 or @retcode <> 0
                goto UNDO
        end

		if (@conflict_table_id is not null)
		begin
			select @objname = object_name(@conflict_table_id)     
			exec @retcode = dbo.sp_MSdrop_object
				@object_name = @objname,
				@object_owner = @owner
			if (@@error != 0 or @retcode != 0)
                goto UNDO
		end

		if (@ins_conflict_proc is not null)
		begin
			select @objname = object_name(@ins_conflict_proc)     
			exec @retcode = dbo.sp_MSdrop_object
				@object_name = @objname,
				@object_owner = @owner
			if (@@error != 0 or @retcode != 0)
                goto UNDO
		end
		
        -- Now generate new ones        
        select @ins_proc = N'sp_MSsync_ins_' + SUBSTRING(RTRIM(@article), 1, 100) + N'_' + rtrim(convert(varchar, @sync_pubid))
        select @upd_proc = N'sp_MSsync_upd_' + SUBSTRING(RTRIM(@article), 1, 100) + N'_' + rtrim(convert(varchar, @sync_pubid))
        select @del_proc = N'sp_MSsync_del_' + SUBSTRING(RTRIM(@article), 1, 100) + N'_' + rtrim(convert(varchar, @sync_pubid))
        select @upd_trig = N'sp_MSsync_upd_trig_' + SUBSTRING(RTRIM(@article), 1, 100) + N'_' + rtrim(convert(varchar, @sync_pubid))

        -- check uniqueness of names and revert to ugly guid-based name if friendly name already exists
        if exists (select name from sysobjects where name in (@ins_proc, @upd_proc, @del_proc))
        begin
            declare @guid_name nvarchar(36)
            select @guid_name =  convert (nvarchar(36), newid())
            -- remove '-' from guid name because rpc can't handle '-'
            select @guid_name = replace (@guid_name,N'-',N'_')
            select @ins_proc = N'sp_MSsync_ins_' + @guid_name
            select @upd_proc = N'sp_MSsync_upd_' + @guid_name
            select @del_proc = N'sp_MSsync_del_' + @guid_name
            select @upd_trig = N'sp_MSsync_upd_trig' + @guid_name
        end

        if @ins_proc IS NULL
        begin
            RAISERROR (14043, 11, -1, N'@ins_proc')
            goto UNDO
        end

        if @upd_proc IS NULL
        begin
            RAISERROR (14043, 11, -1, N'@upd_proc')
            goto UNDO
        end

        if @del_proc IS NULL
        begin
            RAISERROR (14043, 11, -1, N'@del_proc')
            goto UNDO
        end

        if @upd_trig IS NULL
        begin
            RAISERROR (14043, 11, -1, N'@del_proc')
            goto UNDO
        end

        exec @retcode = dbo.sp_MSgen_sync_tran_procs @publication, @article, @ins_proc, @upd_proc, @del_proc, @upd_trig

        IF @@ERROR <> 0 OR @retcode <> 0
            goto UNDO

        --retrieve sproc id's, fail if they don't exist
        SELECT @ins_proc_id = id FROM sysobjects WHERE name = @ins_proc
        SELECT @upd_proc_id = id FROM sysobjects WHERE name = @upd_proc
        SELECT @del_proc_id = id FROM sysobjects WHERE name = @del_proc
        SELECT @upd_trig_id = id FROM sysobjects WHERE name = @upd_trig

        IF (@ins_proc_id IS NULL) OR (@upd_proc_id IS NULL) OR (@del_proc_id IS NULL)
        BEGIN
            if @ins_proc_id IS NULL RAISERROR (20500, 16, 1, @ins_proc)
            if @upd_proc_id IS NULL RAISERROR (20500, 16, 1, @upd_proc)
            if @del_proc_id IS NULL RAISERROR (20500, 16, 1, @del_proc)
            if @upd_trig_id IS NULL RAISERROR (20500, 16, 1, @upd_trig)
            goto UNDO
        END

        -- perform update in sysarticleupdates
        update sysarticleupdates set sync_ins_proc = @ins_proc_id, sync_upd_proc = @upd_proc_id, 
            sync_del_proc = @del_proc_id,
            sync_upd_trig = @upd_trig_id
        where pubid = @pubid and artid = @artid
		if @@error <> 0
			goto UNDO


		--
		-- create the conflict tran table and table if necessary
		--
		if (@allow_queued_tran = 1)
		begin
			exec @retcode = dbo.sp_MSmakeconflicttable @article, @publication, 0
			IF (@@ERROR != 0 OR @retcode != 0)
	            goto UNDO
			exec @retcode = dbo.sp_MSmaketrancftproc @article, @publication
			IF (@@ERROR != 0 OR @retcode != 0)
	            goto UNDO
		end 
		
        IF @@ERROR <> 0
        BEGIN
            RAISERROR (20501, 16, -1)
            goto UNDO
         END
    end
    -- end synctran

	-------------------------------------------------------------------
	-- active article fixups
	-------------------------------------------------------------------

	if @change_active<> 0 and @fSynctranColChanged = 1 or
		-- Besides schema change, we automatically refresh article view if there are 
		-- subscriptions. We don't refresh the article view otherwise to avoid the view
		-- being dropped and recreated when adding columns into the partition during
		-- the creation of the article.
		exists (select * from syssubscriptions where artid = @artid and
			srvid >= 0)
	BEGIN
		-----------------------------------
		-- regenerate the article view  
		-----------------------------------

		declare @view_name nvarchar(386)
		declare @filter_clause nvarchar(4000)
		declare @sync_objid int
		declare @art_type tinyint

		select @sync_objid = sa.sync_objid, @filter_clause = sa.filter_clause, @art_type = sa.type
							 FROM sysarticles sa JOIN syspublications sp ON sa.pubid = sp.pubid
							 WHERE sa.name = @article 
							 AND sp.name = @publication
		-- Only invoke sp_articleview if not manual view and not manual filter
		if ( @art_type & 0x4 <> 4 and @art_type & 0x2 <> 2 )
		begin 
			select @view_name = object_name( @sync_objid )

			exec @retcode = dbo.sp_articleview @publication = @publication, 
											@article = @article,
											@view_name = @view_name,
											@filter_clause = @filter_clause,
											@change_active = @change_active,
											@force_invalidate_snapshot = @force_invalidate_snapshot,
											@force_reinit_subscription = @force_reinit_subscription

			IF @@ERROR <> 0 OR @retcode <> 0
        		    goto UNDO
		end
	END

	-- sp_repldropcolumn used @change_active = 2 to prepare, don't invalidate or reinitialize
	if @change_active <> 2 	and
		-- @ignore_distributor is set to 1 when removing replication forcefully. In that
		-- case, no need to check or reinit
		@ignore_distributor = 0
	begin
		-- Have to call this stored procedure to invalidate existing snapshot or reint
		-- subscriptions if needed
        EXECUTE @retcode  = dbo.sp_MSreinit_article
            @publication = @publication, 
            @article = @article,
			@need_new_snapshot = 1,
			@need_reinit_subscription = 1
			,@force_invalidate_snapshot = @force_invalidate_snapshot	/* Force invalidate existing snapshot */
			,@force_reinit_subscription = @force_reinit_subscription	/* Force reinit subscription */
        IF @@ERROR <> 0 OR @retcode <> 0
			GOTO UNDO
	end

    /*
    ** Force the article cache to be refreshed with the new definition.
	** Nothing to flush if brute force cleanup.
    */
	if ( @ignore_distributor = 0 )
		EXECUTE dbo.sp_replflush


    COMMIT TRANSACTION
END
return (0)
UNDO:
	if @@trancount > 0
    begin
        ROLLBACK TRANSACTION articleview
        commit tran
    end
    RETURN (1)
go
 
EXEC dbo.sp_MS_marksystemobject sp_articlecolumn
GO

print ''
print 'Creating procedure sp_helparticlecolumns'
go
CREATE PROCEDURE sp_helparticlecolumns (
    @publication sysname,            /* The publication name */
    @article    sysname              /* The article name */
    ) AS

    /*
    ** Declarations.
    */

    DECLARE @columns binary(32)
    DECLARE @pubid int
    DECLARE @retcode int
    DECLARE @username sysname

    SELECT @username = suser_sname()

    /*
    ** Parameter Check: @article.
    ** The @article name must conform to the rules for identifiers.
    */

    IF @article IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@article')
            RETURN (1)
        END
    
    /*
    EXECUTE @retcode = dbo.sp_validname @article

    IF @retcode <> 0
    RETURN (1)
    */

    /*
    ** Parameter Check: @publication.
    ** The @publication name must conform to the rules for identifiers.
    */

    IF @publication IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@publication')
            RETURN (1)
        END
    
    EXECUTE @retcode = dbo.sp_validname @publication

    IF @retcode <> 0
        RETURN (1)

    /*
    ** Security Check. Restrict to 'sysadmin', DBO of publishing database, PAL
    */
    IF is_member(N'db_owner') <> 1
    BEGIN
        exec @retcode = sp_MSreplcheck_pull
            @publication = @publication,
            @given_login = @username
        IF @retcode <> 0 OR @@error <> 0
            RETURN (1)
    END
    
    /*
    ** Get the pubid.
    */

    SELECT @pubid = pubid FROM syspublications WHERE name = @publication

    IF @pubid IS NULL
        BEGIN
            RAISERROR (14043, 11, -1, '@pubid')
            RETURN (1)
        END

    /*
    ** Parameter Check:  @article, @publication.
    ** Check to make sure that the article exists in this publication.
    */

    IF NOT EXISTS (SELECT *
                     FROM sysextendedarticlesview
                    WHERE pubid = @pubid
                      AND name = @article)
        BEGIN
            RAISERROR (20027, 11, -1, @article)
            RETURN (1)
        END


    /*
    ** Error out if this is a not a table based article
    */
    IF NOT EXISTS ( SELECT * FROM sysarticles WHERE name = @article
                          AND pubid = @pubid
                          AND (type & 1) = 1 )
        BEGIN
            RAISERROR (14112, 11, -1 )
            RETURN (1)
        END

	declare @status tinyint
	declare @objid int

    SELECT @columns = columns, @status = status, @objid = objid
      FROM sysarticles
     WHERE name = @article
       AND pubid = @pubid

	-- Get the timestamp column id

    SELECT 'column id' = colid,
           'column'    = name,
		   -- 189 is timestamp col
		   -- When the status is set, return timestamp col as published to DMO 
		   -- although it is not in the bitmap used by
		   -- the logreader. Thus, the timestmap will be scripted out and created at the subscriber
		   -- but the value will be generated by subscriber not the logreader.
		   -- 32 is the status bit of keeping timestamp
		   'published' = case xtype when 189
				then convert(bit, @status & 32)
				else convert(bit, 0) end |
				convert(bit, convert( varbinary, substring( convert( nvarchar, @columns ), 16 - floor((colid-1)/16),1 )) & power( 2, ((colid-1)%16)))
	  FROM syscolumns
     WHERE id = @objid
go
 
EXEC dbo.sp_MS_marksystemobject sp_helparticlecolumns
GO

print ''
print 'Creating procedure sp_helppublication'
go


CREATE PROCEDURE sp_helppublication (
        @publication sysname = '%',     /* The publication name */
        @found int = 23456 OUTPUT            /* a flag indicate returning row */
        ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @pubid      int
    DECLARE @has_subscription bit
    DECLARE @retcode int
    DECLARE @no_row bit
    DECLARE @publish_bit int
    DECLARE @pubname sysname
    DECLARE @username sysname
    
    SELECT @publish_bit = 1
    SELECT @username = suser_sname()
    SELECT @retcode = 0
    
    /*
    ** Check if the database is published.
    */
    IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases
        WHERE name = db_name() collate database_default
        AND (category & @publish_bit) = @publish_bit)
        RETURN(0)

    /*
    ** Initializations.
    */
    IF @found = 23456 
    BEGIN
        SELECT @no_row=0
    END
    ELSE
    BEGIN
        SELECT @no_row=1
    END

    /*
    ** Parameter Check:  @publication.
    ** Check to make sure that there are some publications
    ** to display.
    */

    IF @publication IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@publication')
            RETURN (1)
        END
    
    IF @publication <> '%'
        BEGIN
            
            EXECUTE @retcode = dbo.sp_validname @publication

            IF @retcode <> 0
            RETURN (1)
        END

    IF  NOT EXISTS (SELECT * FROM syspublications
        WHERE name like @publication)

    BEGIN
        SELECT @found = 0
        RETURN (0) 
    END
    ELSE
    BEGIN
        SELECT @found = 1
        IF @no_row <>0
            RETURN(0)
    END

    /*
    ** Create a temp table of pubids identifying publications that the current user has access to
    */
    CREATE TABLE #accessiblepubs (pubid int)

    DECLARE hC  CURSOR LOCAL FAST_FORWARD FOR 
        SELECT pubid, name FROM syspublications WHERE name like @publication
    OPEN hC
    FETCH hC INTO @pubid, @pubname
    WHILE (@@fetch_status <> -1)
    BEGIN
        IF is_member(N'db_owner') <> 1
        BEGIN
            exec @retcode = sp_MSreplcheck_pull
                @publication = @pubname,
                @raise_fatal_error = 0,
                @given_login = @username
	END			
        IF (is_member(N'db_owner') = 1) OR (@retcode = 0 AND @@error = 0)
            INSERT INTO #accessiblepubs values(@pubid)

        FETCH hC INTO @pubid, @pubname
    END
    CLOSE hC
    DEALLOCATE hC

    /*
    ** Join the table of accessible pubids to the publication entries retrieved from syspublications
    */

    SELECT 'pubid'                  = outter.pubid,
           'name'                   = name,
           'restricted'             = 0,
           'status'                 = status,
           -- using 'task' is for backward compatibilty
           'task'                   = convert(int, 1),
           'replication frequency'  = repl_freq,
           'synchronization method' = sync_method,
           'description'            = description,
           'immediate_sync'            = immediate_sync,
           'enabled_for_internet'    = enabled_for_internet,
           'allow_push'             = allow_push,
           'allow_pull'             = allow_pull,
           'allow_anonymous'        = allow_anonymous,
           'independent_agent'        = independent_agent,
           'immediate_sync_ready'    = immediate_sync_ready,
           -- SyncTran
           'allow_sync_tran'        = allow_sync_tran,
           'autogen_sync_procs'        = autogen_sync_procs,
           'snapshot_jobid'         = snapshot_jobid,
           'retention'              = retention,
           'has subscription'       = case when EXISTS (select * from syssubscriptions where artid in 
            						  (select artid from sysextendedarticlesview where pubid = outter.pubid ) )
									  then 1 else 0 end,
           'allow_queued_tran'      = allow_queued_tran,
           -- Portable snapshot
           'snapshot_in_defaultfolder'      = snapshot_in_defaultfolder,
           'alt_snapshot_folder'    = alt_snapshot_folder,
           -- Pre/post-snapshot commands
           'pre_snapshot_script'    = pre_snapshot_script,
           'post_snapshot_script'   = post_snapshot_script,
           -- Snapshot compression
           'compress_snapshot'      = compress_snapshot,
           -- Post 7.0 ftp support
           'ftp_address'            = ftp_address,
           'ftp_port'               = ftp_port,
           'ftp_subdirectory'       = ftp_subdirectory,
           'ftp_login'              = ftp_login,
		   'allow_dts'				= allow_dts,
		   'allow_subscription_copy'  = allow_subscription_copy,
		   -- 7.5 Queued updates
		   'centralized_conflicts' 	= centralized_conflicts, 
		   'conflict_retention'		= conflict_retention, 
		   'conflict_policy'		= conflict_policy,
		   'queue_type'				= queue_type,
		'backward_comp_level' = backward_comp_level,
		'publish_to_AD' =		case when ad_guidname is NULL then 0 else 1 end 
      FROM syspublications outter, #accessiblepubs acc
     WHERE name LIKE @publication
        and outter.pubid = acc.pubid
     ORDER BY name

    RETURN (0)
go
 
EXEC dbo.sp_MS_marksystemobject sp_helppublication
GO

-- sp_helppublication_snapshot not used anymore

print ''
print 'Creating procedure sp_helpsubscription'
go

CREATE PROCEDURE sp_helpsubscription
    @publication sysname = '%',    /* The publication name */
    @article sysname = '%',        /* The article name */
    @subscriber sysname = N'%',      /* The subscriber name */
    @destination_db sysname = '%',
    @found int = 23456 OUTPUT
    AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @retcode int
    DECLARE @subscriber_bit smallint
    DECLARE @no_row bit
    DECLARE @srvid smallint
    DECLARE @pubid int
    DECLARE @artid int
    DECLARE @immediate_sync bit
    DECLARE @subscription_type_id int
    DECLARE @sync_typeid int
    DECLARE @publish_bit int
    DECLARE @orig_publication sysname    
    DECLARE @full_subscription bit

    DECLARE @distributor    sysname
    DECLARE @distributiondb sysname
    DECLARE @distproc       NVARCHAR(255)
    DECLARE @dbname         sysname    

    SELECT @publish_bit = 1
    SELECT @distributor = NULL
    SELECT @distributiondb = NULL
    SELECT @distproc = NULL
    SELECT @dbname = NULL
    SELECT @orig_publication = @publication

    /* Security check. To public. */

    /*
    ** Check if the database is published.
    */
    IF NOT EXISTS (SELECT * FROM master..sysdatabases
                    WHERE name = db_name() collate database_default
                      AND (category & @publish_bit) = @publish_bit)
        RETURN(0)

    /*
    ** Initializations of @now_row.
    */
    IF @found = 23456 
    BEGIN
        SELECT @no_row=0
    END
    ELSE
    BEGIN
        SELECT @no_row=1
    END

    /*
    ** Initializations.
    */
    SELECT @subscriber_bit = 4

    /*
    ** Parameter Check:  @subscriber.
    */
    IF @subscriber IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@subscriber')
            RETURN (1)
        END

    /*
    ** Parameter Check:  @subscriber.
    ** Check if remote server is defined as a subscription server, and
    ** that the name conforms to the rules for identifiers.
    */

    IF @subscriber <> '%'
        BEGIN

            EXECUTE @retcode = dbo.sp_validname @subscriber
            select @subscriber = UPPER(@subscriber)

            IF @retcode <> 0
        RETURN (1)

            IF NOT EXISTS (SELECT *
                             FROM master..sysservers
                            WHERE UPPER(srvname) = UPPER(@subscriber) collate database_default
                              AND (srvstatus & @subscriber_bit) <> 0)
                BEGIN
                    RAISERROR (14010, 16, -1)
                    RETURN (1)
                END
        END

    /*
    ** Parameter Check:  @publication.
    ** If the publication name is specified, check to make sure that it
    ** conforms to the rules for identifiers and that the publication
    ** actually exists.  Disallow NULL.
    */

    IF @publication IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@publication')
            RETURN (1)
        END

    IF @publication <> '%'
        BEGIN

            EXECUTE @retcode = dbo.sp_validname @publication

            IF @retcode <> 0
                RETURN (1)

            IF NOT EXISTS (SELECT * FROM syspublications WHERE name = @publication)
                BEGIN
                   RAISERROR (20026, 11, -1, @publication)
                   RETURN (1)
                END

        END

    /*
    ** Parameter Check:  @article.
    ** If the article name is specified, check to make sure that it
    ** conforms to the rules for identifiers and that the article
    ** actually exists.  Disallow NULL.
    **
    ** If @article is 'all', only return one entry for the whole publication
    ** for full subscriptions (subscriptions inlcluding all the articles in a
    ** publication).
    ** 
    */

    IF @article IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@article')
            RETURN (1)
        END

    IF LOWER(@article) <> 'all' 
    BEGIN
        IF @article <> '%'
            BEGIN
                
                /*
                EXECUTE @retcode = dbo.sp_validname @article

                IF @retcode <> 0
                RETURN (1)
                */

                IF NOT EXISTS (SELECT *
                                 FROM sysextendedarticlesview
                                WHERE name = @article
                                  AND pubid IN (SELECT pubid
                                                  FROM syspublications
                                                 WHERE name LIKE @publication))
                    BEGIN
                        RAISERROR (20027, 11, -1, @article)
                        RETURN (1)
                    END

            END


        IF EXISTS (SELECT * 
              FROM syssubscriptions sub,
                   master..sysservers ss,
                   syspublications pub,
                   sysextendedarticlesview art
             WHERE ((@subscriber = N'%') OR (ss.srvname = @subscriber collate database_default))
               AND sub.srvid = ss.srvid
               AND pub.name LIKE @publication
               AND art.name LIKE @article
               AND art.pubid = pub.pubid
               AND sub.artid = art.artid
               AND ((@destination_db = N'%') OR (sub.dest_db = @destination_db))
               AND (sub.login_name = suser_sname(suser_sid()) OR 
                    is_srvrolemember('sysadmin') = 1 OR
                    is_member ('db_owner') = 1)
                )

        BEGIN
            SELECT @found = 1
            IF @no_row <> 0 RETURN (0)
        END
        ELSE
        BEGIN
            SELECT @found = 0
            RETURN(0)
        END
    END

/*
        SELECT 'subscriber'           = ss.srvname,
               'publication'          = pub.name,
               'article'              = art.name,
               'destination database' = sub.dest_db,
               'subscription status'  = sub.status,
               'synchronization type' = sub.sync_type
          FROM syssubscriptions sub,
               master..sysservers ss,
               syspublications pub,
               sysextendedarticlesview art
         WHERE UPPER(ss.srvname) LIKE UPPER(@subscriber) collate database_default
           AND sub.srvid = ss.srvid
           AND pub.name LIKE @publication collate database_default
           AND art.name LIKE @article collate database_default
           AND art.pubid = pub.pubid
           AND sub.artid = art.artid
           AND (sub.login_name = suser_sname(suser_sid()) collate database_default OR 
                    is_srvrolemember('sysadmin') = 1 OR
                    is_member ('db_owner') = 1)
                )
         ORDER BY subscriber, publication, article
*/    

    CREATE TABLE #helpsubscription 
    (
    /* Info that will be returned */
    subscriber sysname collate database_default not null,
    publication sysname collate database_default not null,
    article sysname collate database_default not null,
    destination_db sysname collate database_default not null,
    status tinyint NOT NULL,
    sync_type tinyint NOT NULL,
    subscription_type int NOT NULL,
    full_subscription bit NOT NULL, /* full subscription or not */
    distribution_jobid binary(16) NULL,
    subscription_name nvarchar(255) collate database_default not null,
    -- SyncTran
    update_mode int NOT NULL,
    loopback_detection bit not null
    )


    /* Open a CURSOR LOCAL FOR subscriber/destination_db and publication pair 
    **
    ** Get subscriptions
    ** sa or dbo can see every subscriptions while
    ** others only see their own.
    */

    /* 
    ** Performance Optimization: Eliminate the 'LIKE' clause for publication name.
    **                           Empirical evidence shows almost 50% speed improvement when
    **                           opening the cursor if publication name is provided.
    */
    IF (@publication <> '%')
        DECLARE hChelpsubscription_pub CURSOR LOCAL FAST_FORWARD FOR
            SELECT DISTINCT ss.srvname,
                   pub.name,
                   sub.dest_db,
                   pub.pubid,
                   sub.srvid,
                   pub.immediate_sync
              FROM syssubscriptions sub,
                   master..sysservers ss,
                   syspublications pub,
                   sysextendedarticlesview art
             WHERE ((@subscriber = N'%') OR (UPPER(ss.srvname) = UPPER(@subscriber) collate database_default))
               AND sub.srvid = ss.srvid
               AND pub.name = @publication collate database_default
               AND art.pubid = pub.pubid
               AND sub.artid = art.artid
               AND ((@destination_db = N'%') OR (sub.dest_db = @destination_db))
               AND (sub.login_name = suser_sname(suser_sid()) OR 
                        is_srvrolemember('sysadmin') = 1 OR
                        is_member ('db_owner') = 1)       
               FOR READ ONLY
    ELSE 
        DECLARE hChelpsubscription_pub CURSOR LOCAL FAST_FORWARD FOR
            SELECT DISTINCT ss.srvname,
                   pub.name,
                   sub.dest_db,
                   pub.pubid,
                   sub.srvid,
                   pub.immediate_sync
              FROM syssubscriptions sub,
                   master..sysservers ss,
                   syspublications pub,
                   sysextendedarticlesview art
             WHERE ((@subscriber = N'%') OR (UPPER(ss.srvname) = UPPER(@subscriber) collate database_default))
               AND sub.srvid = ss.srvid
               AND art.pubid = pub.pubid
               AND sub.artid = art.artid
               AND ((@destination_db = N'%') OR (sub.dest_db = @destination_db))
               AND (sub.login_name = suser_sname(suser_sid()) OR 
                        is_srvrolemember('sysadmin') = 1 OR
                        is_member ('db_owner') = 1)       
               FOR READ ONLY
 
    OPEN hChelpsubscription_pub
    FETCH hChelpsubscription_pub INTO @subscriber, @publication, 
        @destination_db, @pubid, @srvid, @immediate_sync
        
    WHILE (@@fetch_status <> -1)
    BEGIN

        /* 
        ** Is it a full subscription ? i.e. Does it include all the articles? 
        **
        */

        IF NOT EXISTS (SELECT * FROM sysextendedarticlesview art WHERE
                art.pubid = @pubid and 
                NOT EXISTS (SELECT * from syssubscriptions sub WHERE
                    sub.artid = art.artid and
					sub.srvid = @srvid and
					sub.dest_db = @destination_db))
        BEGIN
            /* Do all the subscriptions on the publication have same
            ** sync_type and subscription_type ?
            */
  
              /* 
            ** Get subscription type on the publication
            */ 
            SELECT @subscription_type_id = subs.subscription_type,
                @sync_typeid = subs.sync_type 
                FROM 
                sysextendedarticlesview art, syssubscriptions subs  
                WHERE
                art.pubid = @pubid AND
                subs.srvid = @srvid AND
                subs.dest_db = @destination_db AND
                subs.artid = art.artid


            /* 
            ** if the subscription all have the same subscription type
            ** and sync_type
            */
            IF NOT EXISTS (SELECT * from 
                sysextendedarticlesview art, syssubscriptions subs where 
                art.pubid = @pubid AND
                subs.srvid = @srvid AND
                subs.dest_db = @destination_db AND
                subs.artid = art.artid AND
                (subscription_type <> @subscription_type_id OR
                sync_type <> @sync_typeid))
                

                SELECT @full_subscription = 1
            ELSE
                SELECT @full_subscription = 0
        END
        ELSE
        BEGIN
            SELECT @full_subscription = 0
        END
            
        /* 
        ** If it is a full subscription and the @article is 'all',
        ** only return one entry for the whole publication.
        ** Always return one row per publication if @article is 'ALL'
        */
        IF    LOWER(@article) = 'all'
        BEGIN    
            INSERT INTO #helpsubscription 
                SELECT TOP 1 @subscriber, @publication, @article, @destination_db,
                    sub.status, sub.sync_type, sub.subscription_type,
                    @full_subscription, sub.distribution_jobid,
                    @subscriber + ':' + @destination_db  ,
                    -- NOTE: For Queued case: we will always return 2/3 for the 4/5 case
                    -- since we overload update_mode based on queue_type
                    case 	when sub.update_mode = 4 then 2
                    		when sub.update_mode = 5 then 3
                    		else sub.update_mode
                    end,
                    sub.loopback_detection
                    -- end SyncTran
                    FROM syssubscriptions sub, sysextendedarticlesview art 
                    WHERE sub.srvid = @srvid AND
                        sub.dest_db = @destination_db AND
                        sub.artid = art.artid and
                        art.pubid = @pubid
        END
        ELSE
        BEGIN
            /*
            ** Get subscriptions
            ** sa or dbo can see every subscriptions while
            ** others only see their own.
            */

            INSERT INTO #helpsubscription 
                SELECT    @subscriber, @publication, art.name, @destination_db,
                    sub.status, sub.sync_type, sub.subscription_type,
                    @full_subscription, sub.distribution_jobid,
                    @subscriber + ':' + @destination_db + ':' + art.name,
                    -- NOTE: For Queued case: we will always return 2/3 for the 4/5 case
                    -- since we overload update_mode based on queue_type
                    case 	when sub.update_mode = 4 then 2
                    		when sub.update_mode = 5 then 3
                    		else sub.update_mode
                    end,
                    sub.loopback_detection
                    -- end SyncTran
                    FROM
                    syssubscriptions sub, sysextendedarticlesview art WHERE
                        sub.srvid = @srvid AND
                        sub.dest_db = @destination_db AND
                        art.pubid = @pubid AND
                        art.name LIKE @article AND
                        sub.artid = art.artid AND
                        (sub.login_name = suser_sname(suser_sid()) OR 
                         is_srvrolemember('sysadmin') = 1 OR
                         is_member ('db_owner') = 1)     
        END
        FETCH hChelpsubscription_pub INTO @subscriber, @publication, 
            @destination_db, @pubid, @srvid, @immediate_sync
    END

    CLOSE hChelpsubscription_pub
    DEALLOCATE hChelpsubscription_pub

    CREATE TABLE #dist_agent_properties
    (
        job_id					VARBINARY(16) NOT NULL,
        offload_enabled			bit NULL,
        offload_server			sysname collate database_default null,
        dts_package_name		sysname collate database_default null,
        dts_package_location	int NULL,
		status					int NULL  
    )

    EXEC @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT,
                                           @distribdb = @distributiondb OUTPUT
    IF @retcode <> 0
        RETURN @retcode

    SELECT @distributor = RTRIM(@distributor)

    -- Get distribution agent properties 
    SELECT @distproc = @distributor + '.' + @distributiondb + '.dbo.sp_MSenumdistributionagentproperties'

    SELECT @dbname = db_name()

    INSERT INTO #dist_agent_properties
    	EXEC @retcode = @distproc @publisher 	= @@SERVERNAME, 
                                @publisher_db	= @dbname, 
                                @publication 	= @orig_publication            

    /*
    ** Get subscriptions
    */
    SELECT 'subscriber'           = hs.subscriber,
           'publication'          = hs.publication,
           'article'              = hs.article,
           'destination database' = hs.destination_db,
           'subscription status'  = case
				-- distributionstatus = 0 means that the subscription has been deactivated.  
				when hs.status = 2 and ap.status = 0 then 0
				else hs.status
				end,
           'synchronization type' = hs.sync_type,
           'subscription type'    = hs.subscription_type,
           'full subscription'    = hs.full_subscription,
           'subscription_name'      = hs.subscription_name,
              -- SyncTran
           'update mode'          = hs.update_mode,
           'distribution job id' = hs.distribution_jobid,
           'loopback_detection'  = hs.loopback_detection,
           'offload_enabled'     = ap.offload_enabled,
           'offload_server'      = ap.offload_server,
           'dts_package_name'    = ap.dts_package_name,
           'dts_package_location' = ap.dts_package_location
      FROM #helpsubscription hs
      LEFT OUTER JOIN #dist_agent_properties ap
      ON hs.distribution_jobid = ap.job_id  
      ORDER BY subscriber, publication, article

    DROP TABLE #dist_agent_properties
go
 
EXEC dbo.sp_MS_marksystemobject sp_helpsubscription
GO

print ''
print 'Creating procedure sp_articlefilter'
go
create procedure sp_articlefilter (
	@publication sysname,           /* publication name */
	@article sysname,             /* article name */
	@filter_name nvarchar (386) = NULL,     /* name of filter procedure*/
	@filter_clause ntext = NULL,               /* article's filter clause */
	@force_invalidate_snapshot bit = 0,	/* Force invalidate existing snapshot */
	@force_reinit_subscription bit = 0	/* Force reinit subscription */
)
as
BEGIN
	declare @pubid smallint
			,@table_name sysname
			,@user_name sysname
			,@qualified_table_name nvarchar (258)
			,@filter_id int
			,@type tinyint        
			,@previous_proc nvarchar(386)
			,@retcode int
			,@site sysname
			,@db sysname
			,@owner sysname
			,@object nvarchar(386)
			,@artid int
			,@active tinyint
			,@obid int
			,@view_id int
			,@cmd nvarchar(4000)
			,@allow_sync_tran bit
			,@allow_queued_tran bit

    select @active = 2

    /*
    ** Security Check.
    */
    exec @retcode = dbo.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    /*
    ** Parameter Check:  @publication.
    ** Make sure that the publication exists and that it conforms to the
    ** rules for identifiers.
    */
    if @publication is null
           begin
              RAISERROR (14043, 16, -1, '@publication')
              return (1)
           END
    
    execute @retcode = dbo.sp_validname @publication
    if @retcode <> 0
    RETURN (1)

	select @pubid = pubid
		,@allow_sync_tran = allow_sync_tran
		,@allow_queued_tran = allow_queued_tran
	from syspublications where name = @publication

        if @pubid is null
           begin
            RAISERROR (20026, 11, -1, @publication)
            return (1)
           end

    /*
    ** Parameter Check:  @article.
    ** Check to make sure that the article exists in the publication.
    */

    if @article is null
       begin
              RAISERROR (14043, 16, -1, '@article')
              return (1)
           end
        /*
        execute @retcode = dbo.sp_validname @article
        if @retcode <> 0
       return (1)
       */

    /*
    ** Get the article information.
    */
    select @artid = art.artid, @table_name = so.name, @type = art.type,
       @filter_id = art.filter, @user_name = USER_NAME(so.uid)
		,@view_id = ISNULL(art.sync_objid, art.objid)
       from sysarticles art, sysobjects so
       where art.pubid = @pubid
       and art.name = @article
       and art.objid = so.id

    /*
    ** Fail if there is no article information.
    */
    if @artid is null
       begin
              RAISERROR (20027, 11, -1, @article)
              return (1)
       end

        /*
        ** Error out if this is a not a table based article
        */
        IF NOT EXISTS ( SELECT * FROM sysarticles WHERE artid = @artid
                          AND pubid = @pubid
                          AND (type & 1) = 1 )
        BEGIN
            RAISERROR (14112, 11, -1 )
            RETURN (1)
        END



    /*
    ** Make sure a valid @filter_name was provided and it is
    ** a valid name.
    */
    if datalength(@filter_clause) > 0  
    begin

        /*
        ** Make sure a valid @filter_name was provided and it is
        ** a valid name.
        */

        if @filter_name is null
        begin
           RAISERROR (14043, 16, -1, '@filter_name')
           return (1)
        end
        
        select @object = PARSENAME( @filter_name, 1 )
        select @owner  = PARSENAME( @filter_name, 2 )
        select @db     = PARSENAME( @filter_name, 3 )
        select @site   = PARSENAME( @filter_name, 4 )

         if @object IS NULL
               return 1

		if @owner is NULL
			select @owner = user_name()
		select @object = quotename(@owner) + N'.' + quotename(@object)
    end

	-- Check if there are snapshot or subscriptions and raiserror if needed.
    EXECUTE @retcode  = dbo.sp_MSreinit_article
        @publication = @publication, 
        @article = @article,
		@need_new_snapshot = 1,
		@need_reinit_subscription = 1
		,@force_invalidate_snapshot = @force_invalidate_snapshot	/* Force invalidate existing snapshot */
		,@force_reinit_subscription = @force_reinit_subscription	/* Force reinit subscription */
		,@check_only = 1
    IF @@ERROR <> 0 OR @retcode <> 0
		return 1

	begin tran
    save TRANSACTION articlefilter

    /*
    ** If the article has a generated filter (not manually created), then
    ** drop the current filter before creating the new one.
    */
    if ((@type & 0x3) <> 0x3) and @filter_id <> 0
       begin
          if exists (select * from sysobjects where id = @filter_id
                and type = 'RF')
         begin
			exec dbo.sp_MSget_qualified_name @filter_id, @previous_proc output
			exec ('drop procedure ' + @previous_proc)
			if @@error <> 0
			   goto UNDO
         end

       end


    /*
    ** make an owner qualified table name for these operations name
    */

    select @qualified_table_name = quotename(@user_name) + '.' + quotename(@table_name)

    -- Drop replication filter if it exists.
    -- Note: upgrade needs this logic
    if datalength(@filter_clause) > 0
        and exists (select * from sysobjects where id = object_id(@object)
                and type = 'RF')
    begin
          exec ('drop procedure ' + @object)
        if @@error <> 0
           goto UNDO
    end


    /*
    ** If there is a @filter_clause, create the new filter and
    ** update the article filter id and filter_clause.
    **/
    if datalength(@filter_clause) > 0
       begin
		declare @subst_clause nvarchar(4000)
		select @subst_clause = @filter_clause

		exec @retcode = sp_MSsubst_filter_names @user_name, @table_name, @subst_clause output
		if @retcode <> 0 or @@error <> 0
			goto UNDO
        exec ('create procedure ' + @object +
            ' for replication as ' +
            'if exists (select * from ' + @qualified_table_name +
            ' where ' + @subst_clause +
            ') return 1 else return 0')
        if @@error <> 0
           goto UNDO

		if (@user_name in ('dbo','INFORMATION_SCHEMA'))
		begin
			exec @retcode = dbo.sp_MS_marksystemobject @object
			if @@error <> 0 
				goto UNDO
		end

        select @filter_id = id  from sysobjects where id = object_id(@object)
            and type = 'RF'
        if @filter_id is null or @filter_id = 0
           begin
              RAISERROR (15001, 11, -1, @object)
              goto UNDO
           end

        /*
        ** Update article
        */
        update sysarticles set filter = @filter_id,
           filter_clause = @filter_clause
           where pubid = @pubid
              and name = @article
		if @@error <> 0
			goto UNDO

       -------------------------------------------------------------
       -- SQL SERVER 7.0 ONLY: update sysobjects, set parent id = underlying
       -- object id
       -------------------------------------------------------------

        select @obid = object_id( @qualified_table_name )
        EXEC @retcode = dbo.sp_MSsetfilterparent @object, @obid
		if @retcode <> 0 or @@error <> 0
			goto UNDO
        EXEC @retcode = dbo.sp_MSsetfilteredstatus @obid
		if @retcode <> 0 or @@error <> 0
			goto UNDO

       end
    else
    BEGIN
        /*
        ** Clear the filter id and filter_clause.
        */
        update sysarticles set filter = 0,
           filter_clause = NULL
           where pubid = @pubid
              and name = @article
		if @@error <> 0
			goto UNDO

        ---------------------------------------------------
        -- SQL SERVER 7.0 ONLY:  remove parent_id from filter proc
        ---------------------------------------------------

        select @obid = object_id( @qualified_table_name )
        if exists ( select * from sysobjects where name = @object
                and type = 'RF')
        begin
		    EXEC @retcode = dbo.sp_MSsetfilterparent @object, 0
        	if @retcode <> 0 or @@error <> 0
				goto UNDO
		end
		EXEC @retcode = dbo.sp_MSsetfilteredstatus @obid
		if @retcode <> 0 or @@error <> 0
			goto UNDO
    END

	-- Have to call this stored procedure to invalidate existing snapshot or reint
	-- subscriptions if needed
    EXECUTE @retcode  = dbo.sp_MSreinit_article
        @publication = @publication, 
        @article = @article,
		@need_new_snapshot = 1,
		@need_reinit_subscription = 1
		,@force_invalidate_snapshot = @force_invalidate_snapshot	/* Force invalidate existing snapshot */
		,@force_reinit_subscription = @force_reinit_subscription	/* Force reinit subscription */
    IF @@ERROR <> 0 OR @retcode <> 0
		GOTO UNDO

    /*
    ** Force the article cache to be refreshed with the new definition.
    */
    EXECUTE dbo.sp_replflush
    COMMIT TRANSACTION
	return 0
UNDO:
	if @@trancount > 0
    begin
        ROLLBACK TRANSACTION articlefilter
        commit tran
    end
    RETURN (1)
END
go
 
dump tran master with no_log
go

EXEC dbo.sp_MS_marksystemobject sp_articlefilter
GO

create procedure sp_MSscript_article_view 
@artid int,
@view_name sysname, 
@include_timestamps bit
as
declare @base_objid int
declare @columns varbinary(32)
declare @user_name sysname
declare @table_name sysname
declare @qualified_table_name nvarchar(520)
declare @filter_clause nvarchar(4000)

declare @cmdfrag nvarchar(4000)
declare @separator nvarchar(1)
declare @colname sysname

declare @retcode int

select @table_name = so.name,
	   @base_objid = art.objid,
	   @columns = art.columns,
	   @user_name = USER_NAME(so.uid),
	   @table_name = so.name,
	   @qualified_table_name = QUOTENAME(USER_NAME(so.uid)) + '.' + QUOTENAME(so.name),
	   @filter_clause = art.filter_clause
	   from sysarticles art, sysobjects so
	   where art.artid = @artid
	   and art.objid = so.id

create table #tempcmd( c1 int identity NOT NULL, cmdfrag nvarchar(4000) collate database_default )

insert into #tempcmd (cmdfrag) values ( N'create view ' + QUOTENAME(@view_name) + N'as select ' )

create table #tmp (colid int NOT NULL primary key, name sysname collate database_default not null, published bit NOT NULL)

insert into #tmp select colid, name,
	convert(bit, convert( varbinary, substring( convert( nvarchar(16), @columns ), 16 - floor((colid-1)/16),1 )) & power( 2, ((colid-1)%16)))
	from syscolumns
	where id = @base_objid

if 1 = @include_timestamps
begin
	update #tmp set published = 1 where colid = (select colid from syscolumns c
		where c.id = @base_objid and c.xtype = 189)
end

declare hc  CURSOR LOCAL FAST_FORWARD FOR select name from #tmp
    where published = 1 order by colid asc
open hc
fetch hc into @colname

select @cmdfrag = N''
select @separator = N''

while (@@fetch_status <> -1)
begin
	if datalength( @cmdfrag ) > 3500 
	begin
		insert into #tempcmd(cmdfrag) values (@cmdfrag)
		select @cmdfrag = N''
	end
	select @cmdfrag = @cmdfrag + @separator + quotename(@colname)

	select @separator = N','
	fetch hc into @colname
end            
close hc
deallocate hc

insert into #tempcmd( cmdfrag ) values (@cmdfrag) 

insert into #tempcmd( cmdfrag ) values (N' from ')
insert into #tempcmd( cmdfrag ) values (@qualified_table_name)


insert into #tempcmd( cmdfrag ) values (N' where permissions(' + 
	convert(nvarchar(10), @base_objid)  + ') & 1 = 1 ')

if( @filter_clause is not null and datalength( @filter_clause ) > 0 )
begin
	exec @retcode = sp_MSsubst_filter_names @user_name, @table_name, @filter_clause output
	if @retcode <> 0 or @@error <> 0
		return 1		
	insert into #tempcmd( cmdfrag ) values ('and (' + @filter_clause + ')')
end

select cmdfrag from #tempcmd order by c1 asc

return 0
go

EXEC dbo.sp_MS_marksystemobject sp_MSscript_article_view 
GO


print ''
print 'Creating procedure sp_articleview'
go
create procedure sp_articleview (
	@publication sysname,        /* Publication name */
	@article sysname,          /* Article name */
	@view_name nvarchar (386) = NULL,  /* View name */
	@filter_clause ntext = NULL            /* Article's filter clause */
	,@change_active int = 0
	,@force_invalidate_snapshot bit = 0	/* Force invalidate existing snapshot */
	,@force_reinit_subscription bit = 0	/* Force reinit subscription */
)
as
BEGIN
	declare @pubid smallint
			,@table_name sysname
			,@user_id int
			,@user_name sysname
			,@columns varbinary (32)
			,@name sysname
			,@retcode int
			,@view_id int
			,@type tinyint
			,@table_id int
			,@previous_view sysname
			,@quoted_prev_view sysname
			,@colid int
			,@object sysname
			,@quoted_object nvarchar(512)
			,@artid int
			,@active tinyint
			,@allow_sync_tran bit
			,@allow_queued_tran bit
			,@filter_id int

    select @active = 2

    /*
    ** Security Check.
    */
    exec @retcode = dbo.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    /*
    ** Parameter Check:  @publication.
    ** Make sure that the publication exists and that it conforms to the
    ** rules for identifiers.
    */
    if @publication is null
           begin
              RAISERROR (14043, 16, -1, '@publication')
              return (1)
           END

    execute @retcode = dbo.sp_validname @publication
    if @retcode <> 0
            RETURN (1)

	select @pubid = pubid
		,@allow_sync_tran = allow_sync_tran
		,@allow_queued_tran = allow_queued_tran		
	from syspublications where name = @publication
	if @pubid is null
	begin
		RAISERROR (20026, 11, -1, @publication)
		return (1)
	end

    /*
    ** Parameter Check:  @article.
    ** Check to make sure that the article exists in the publication.
    */

    if @article is null
       begin
              RAISERROR (14043, 16, -1, '@article')
              return (1)
           end
    
    /*
        execute @retcode = dbo.sp_validname @article
        if @retcode <> 0
       return (1)
       */

    /*
    ** Get the article information.
    */
	declare @status tinyint
	declare @objid int
    select @artid = art.artid, @table_name = so.name,
       @user_id = uid, @user_name = USER_NAME(so.uid),
       @columns = art.columns, @type = art.type,
	   @status = art.status,
	   @objid = art.objid,
       @view_id = art.sync_objid, @table_id = art.objid
		,@type = art.type
		,@filter_id = art.filter
       from sysarticles art, sysobjects so
       where art.pubid = @pubid
       and art.name = @article
       and art.objid = so.id

    /*
    ** Fail if there is no article information.
    */
    if @artid is null
       begin
              RAISERROR (20027, 11, -1, @article)
              return (1)
       end

        /*
        ** Error out if this is a not a table based article
        */
        IF NOT EXISTS ( SELECT * FROM sysarticles WHERE artid = @artid
                          AND pubid = @pubid
                          AND (type & 1) = 1 )
            BEGIN
                RAISERROR (14112, 11, -1 )
                RETURN (1)
            END
   
    
	-- Special handling for timestamp column for queued publication.
	-- Although timestamp values are not needed, they have to be presented in bcp files. Otherwise
	-- bcp in will fail with data file format error.

	declare @include_timestamps bit
	select @include_timestamps = 0
	
	if @allow_queued_tran = 1 and @status & 32 <> 0
	begin
		select @include_timestamps = 1
	end
	
    /* Break out the specified view name and get the non-ownerqual'd name, then validate that. */
    select @object = PARSENAME( @view_name, 1 )
 
    if @object IS NULL
	begin
		-- generate view name
		declare @viewname varchar(255)
		declare @guid uniqueidentifier
		set @guid = CONVERT(varbinary(16), LEFT(NEWID(),8))
		exec @retcode = master.dbo.xp_varbintohexstr @guid, @viewname OUTPUT
		if @@ERROR <> 0 OR @retcode <> 0
			return (1)
		set @viewname = 'syncobj_' + @viewname
		set @object = @viewname
	end


	select @quoted_object = QUOTENAME(@object)

    execute @retcode = dbo.sp_validname @object
    if @retcode <> 0
        return (1)

	-- Check if there are snapshot or subscriptions and raiserror if needed.
    EXECUTE @retcode  = dbo.sp_MSreinit_article
        @publication = @publication, 
        @article = @article,
		@need_new_snapshot = 1,
		@need_reinit_subscription = 1
		,@force_invalidate_snapshot = @force_invalidate_snapshot	/* Force invalidate existing snapshot */
		,@force_reinit_subscription = @force_reinit_subscription	/* Force reinit subscription */
		,@check_only = 1
    IF @@ERROR <> 0 OR @retcode <> 0
		return 1

CreateView:
	begin tran
    save TRANSACTION articleview

	/*
	** If the article has a generated view (not manually created), then
	** drop the current view before creating the new one.
	*/
	if ((@type & 0x5) <> 0x5) and @view_id <> 0
			and @view_id <> @table_id
	begin
		select @previous_view = object_name (@view_id)
		if @previous_view is not null and
				exists (select * from sysobjects where name = @previous_view
				and type = 'V')
		begin
			select @quoted_prev_view = QUOTENAME(@previous_view)
			exec ('drop view ' + @quoted_prev_view)
			if @@error <> 0
				goto UNDO
		end
	end

    /*
    ** Construct and execute the view creation command.
    */
    -- Drop the existing view.
    -- Note: upgrade needs this logic
    if datalength(@filter_clause) > 0 and
        exists (select * from sysobjects where id = object_id(@object)
    and type = 'V') 
    begin
		exec ('drop view ' + @quoted_object)
		if @@error <> 0
			goto UNDO
	end

    ---------------------------------------------
	--  Update article definition 
	--  Set @filter_clause value
    ---------------------------------------------

    if datalength(@filter_clause) > 0
	begin
       update sysarticles set filter_clause = @filter_clause
          where pubid = @pubid
          and name = @article
		if @@error <> 0
			goto UNDO
	end
    else
	begin
       update sysarticles set filter_clause = NULL
          where pubid = @pubid
          and name = @article
		if @@error <> 0
			goto UNDO
	end


	-----------------------------------------------------------
	-- create the view
	-----------------------------------------------------------

	declare @cmd nvarchar(2000)
	declare @dbname sysname

	select @cmd = N'exec sp_MSscript_article_view ' + cast(@artid as nvarchar(20)) 
				+ N',N' + quotename(@object,'''') +  
				+ N',' + cast(@include_timestamps as nvarchar(1))
	select @dbname = db_name()
	
	exec @retcode = master..xp_execresultset @cmd, @dbname
	if @@error <> 0 or @retcode <> 0
		goto UNDO

	-----------------------------------------------------------
	-- grant the permission of the view to public so that non
	-- db_owner can do validation.
	-----------------------------------------------------------
	select @cmd = 'grant select on ' + QUOTENAME(@object) + ' to public'
	exec (@cmd)
	if @@error <> 0
		goto UNDO


	-----------------------------------------------------------
    -- Update the article's sync_objid with the new view id
    -----------------------------------------------------------

    select @view_id = id from sysobjects where name = @object and type = 'V'
    if @view_id is null or @view_id = 0
    begin
        RAISERROR (15001, 11, -1, @object)
		goto UNDO
    end
	else
	begin
		if (@user_name in ('dbo','INFORMATION_SCHEMA'))
		    EXEC dbo.sp_MS_marksystemobject @object
	end

    ---------------------------------------------
	--  Update article definition 
	--  Set new sync_objid 
	---------------------------------------------

    update sysarticles set sync_objid = @view_id
      where pubid = @pubid
      and name = @article
	if @@error <> 0 
		goto UNDO

	-- sp_repldropcolumn used @change_active = 2 to prepare, don't invalidate or reinitialize
	if @change_active <> 2
	begin
		-- Have to call this stored procedure to invalidate existing snapshot or reint
		-- subscriptions if needed
        EXECUTE @retcode  = dbo.sp_MSreinit_article
            @publication = @publication, 
            @article = @article,
			@need_new_snapshot = 1,
			@need_reinit_subscription = 1
			,@force_invalidate_snapshot = @force_invalidate_snapshot	/* Force invalidate existing snapshot */
			,@force_reinit_subscription = @force_reinit_subscription	/* Force reinit subscription */
        IF @@ERROR <> 0 OR @retcode <> 0
			GOTO UNDO
	end

    /*
    ** Force the article cache to be refreshed with the new definition.
    */
    EXECUTE dbo.sp_replflush
    COMMIT TRANSACTION

	--
	-- Validate that the new view does not exclude the horizontal filter
	--
	if ((@allow_sync_tran = 1 or @allow_queued_tran = 1) and
		(datalength(@filter_clause) > 0))
	begin
		select @cmd = N'select top 0 * into #dummy from ' + 
			quotename(@user_name) + N'.' + quotename(object_name(@view_id)) +
			N' where ' + cast(@filter_clause as nvarchar(4000))
		exec (@cmd)
		if (@@error != 0)
		begin
			--
			-- The row filter does not work for the current view
			--
			declare @h_filter nvarchar(4000)
			select @h_filter = cast(@filter_clause as nvarchar(4000))
			raiserror(21392, 16, 1, @h_filter, @object, @article, @publication)
			
			--
			-- drop the filter if not manually generated 
			--
			if ((@type & 0x3) <> 0x3) and @filter_id <> 0
			begin
				if exists (select * from sysobjects where id = @filter_id and type = 'RF')
				begin
					declare @filter_proc nvarchar(386)
					exec dbo.sp_MSget_qualified_name @filter_id, @filter_proc output
					exec ('drop procedure ' + @filter_proc)

					update dbo.sysarticles set filter = 0, filter_clause = NULL
						where pubid = @pubid and name = @article

					raiserror(21393, 16, 1, @h_filter, @article, @publication)
				end
			end
			return 1
		end
	end

	return 0
UNDO:
	if @@trancount > 0
    begin
        ROLLBACK TRANSACTION articleview
        commit tran
    end
    RETURN (1)
END
go
 
EXEC dbo.sp_MS_marksystemobject sp_articleview
GO

dump tran master with no_log
go

print ''
print 'Creating procedure sp_MSaddexearticle'
go
CREATE PROCEDURE sp_MSaddexecarticle
    @publication sysname,               /* publication name */
    @article     sysname,             /* article name */
    @source_proc nvarchar (92),                 /* table name */
    @destination_proc sysname = NULL,           /* destination table name */
    @type sysname = NULL,                       /* article type */
    @creation_script nvarchar (127) = NULL,           /* article schema script */
    @description nvarchar (255) = NULL,                 /* article description */
    @pre_creation_cmd nvarchar(10) = 'drop',           /* 'none', 'drop', 'delete', 'truncate' */
    @schema_option binary(8) = NULL,    /* script out stored procedure */
    @destination_owner sysname,
    @article_id int OUTPUT

    AS


    SET NOCOUNT ON

    /* variables for SP_NAMECRACK */

    DECLARE @site sysname
    DECLARE @db sysname
    DECLARE @owner sysname
    DECLARE @object sysname

    DECLARE @retcode   int

    DECLARE @procid    int
    DECLARE @procnum   smallint
    DECLARE @pubid     int
    DECLARE @precmdid  int

    DECLARE @typeid      smallint
    DECLARE @publish_bit smallint
    DECLARE @incompatible_typeid smallint

    DECLARE @cmd nvarchar(255)
    DECLARE @sysobj_colname  sysname

    SELECT  @typeid      = 24


    SELECT @sysobj_colname = 'replinfo'
    SELECT @publish_bit = 1


    /*
    ** Parameter Check: @article.
    ** The @article name cannot be NULL and must conform to the rules
    ** for identifiers.
    */

    IF @article IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@article')
            RETURN (1)
        END
    
    /*
    EXECUTE @retcode = dbo.sp_validname @article

    IF @retcode <> 0
    return(1)
    */

    if LOWER(@article) = 'all'
        BEGIN
            RAISERROR (14032, 16, -1, '@article')
            RETURN (1)
        END

    /*
    ** Parameter Check: @publication.
    ** The @publication name cannot be NULL and must conform to the rules
    ** for identifiers.
    */

    IF @publication IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@publication')
            RETURN (1)
        END

    EXECUTE @retcode = dbo.sp_validname @publication

    IF @retcode <> 0
    RETURN (1)

    /*
    ** Parameter Check: @source_proc.
    ** Check to see that the @source_proc is local, that it conforms
    ** to the rules for identifiers, and that it is a procedure
    */

    IF @source_proc IS NULL
    BEGIN
        RAISERROR (14043, 16, -1, '@source_proc')
        RETURN (1)
    END

   select @object = PARSENAME( @source_proc, 1 )
   select @owner  = PARSENAME( @source_proc, 2 )
   select @db     = PARSENAME( @source_proc, 3 )
   select @site   = PARSENAME( @source_proc, 4 )

   if @object IS NULL
         return 1


    IF @source_proc LIKE '%.%.%' AND @db <> DB_NAME()
    BEGIN
        RAISERROR (14004, 16, -1, @source_proc)
		RETURN (1)
    END
    

    /*
    **  Get the id of the @source_proc
    */

    SELECT @procid = id
      FROM sysobjects
     WHERE id = OBJECT_ID(@source_proc)
     AND   type = 'P'

    IF @procid IS NULL
    BEGIN
        RAISERROR (14027, 11, -1, @source_proc)
        RETURN (1)
    END

    /*
    ** Parameter Check:  @destination_proc.
    ** If the destination proc is not specified, assume it's the same
    ** as the source. 
    */
    
    IF @destination_proc IS NULL
    BEGIN
        -- Perform parsing only if the destination_proc parameter is not provided
        SELECT @destination_proc = @source_proc

	    select @object = PARSENAME( @destination_proc, 1 )
	    select @owner  = PARSENAME( @destination_proc, 2 )
	    select @db     = PARSENAME( @destination_proc, 3 )
	    select @site   = PARSENAME( @destination_proc, 4 )

	    if @object IS NULL
		     return 1 
    END

    /*
    ** Get the pubid.
    */

    SELECT @pubid = pubid FROM syspublications WHERE name = @publication

    IF @pubid IS NULL
        BEGIN
            RAISERROR (14027, 11, -1, @publication)
            RETURN (1)
        END

    /*
    ** Parameter Check:  @article, @publication.
    ** Check if the article already exists in this publication.
    */

    IF EXISTS (SELECT *
                 FROM sysextendedarticlesview
                WHERE pubid = @pubid
                  AND name = @article)
        BEGIN
            RAISERROR (14030, 16, -1, @article, @publication)
            RETURN (1)
        END


    /*
    ** Set the precmdid.  The default type is 'drop'.
    **
    **      @precmdid   pre_creation_cmd
    **      =========   ================
    **            0     none
    **          1     drop
    */
    IF LOWER(@pre_creation_cmd collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('none', 'drop')
       BEGIN
          RAISERROR (14111, 16, -1)
          RETURN (1)
       END

    /*
    ** Determine the integer value for the pre_creation_cmd.
    */

    IF LOWER(@pre_creation_cmd collate SQL_Latin1_General_CP1_CS_AS) = 'none'
       SELECT @precmdid = 0
    ELSE IF LOWER(@pre_creation_cmd collate SQL_Latin1_General_CP1_CS_AS) = 'drop'
       SELECT @precmdid = 1

    /*  Determine 'type' value for article.
    **
    **            8     proc exec
    **           24     serializable proc exec
    */

    IF @type IS NULL
    BEGIN
        SELECT @type = 'serializable proc exec'
    END
    ELSE IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('proc exec', 'serializable proc exec')
    BEGIN
            RAISERROR (14118, 16, -1)
            RETURN (1)
    END

    IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = 'proc exec'
    BEGIN
       SELECT @typeid = 8
       SELECT @incompatible_typeid = 24
    END
    ELSE IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = 'serializable proc exec'
    BEGIN
       SELECT @typeid = 24
       SELECT @incompatible_typeid = 8
    END

    -- make sure we haven't already created an article of a different type
    -- on this proc

    IF EXISTS ( select * from sysobjects where id = @procid
                and replinfo & @incompatible_typeid = @incompatible_typeid )
    BEGIN
       RAISERROR (21024, 16, -1, @source_proc )
       RETURN(1)
    END

    /*
    ** Parameter Check:  @creation_script and @schema_option
    ** @schema_option is null, set the default value
    */
    IF @schema_option IS NULL
    BEGIN
        SELECT @schema_option = 1
    /* 
        RAISERROR (14043, 16, -1, '@schema_option')
        RETURN (1)
        */
    END

    IF @schema_option <> 0x0000000000000000 AND 
       @schema_option <> 0x0000000000000001 AND
       @schema_option <> 0x0000000000002000 AND
       @schema_option <> 0x0000000000002001  
    BEGIN
        RAISERROR (20014, 10, -1)
        RETURN (1)
    END

    /*
    **  Add article to sysarticles and update sysobjects category bit.
    */

    begin tran
    save TRAN sp_MSaddexecarticle
        INSERT sysarticles (columns, creation_script, del_cmd, description,
                            dest_table, filter, filter_clause, ins_cmd, name,
                objid, pre_creation_cmd, pubid,
                            status, sync_objid, type, upd_cmd, schema_option,
                            dest_owner)
        VALUES (0, @creation_script, NULL, @description,
                @destination_proc, 0, '', NULL, @article,
                @procid, @precmdid, @pubid,
                0, @procid, @typeid, NULL, @schema_option,
                @destination_owner)

        IF @@ERROR <> 0
        BEGIN
            if @@trancount > 0
            begin
                ROLLBACK TRAN sp_MSaddexecarticle
                commit tran
            end
            RETURN (1)
        END

        SELECT @article_id = @@IDENTITY

        select @cmd = 'UPDATE sysobjects SET ' + @sysobj_colname
        select @cmd = @cmd + ' = ' + @sysobj_colname + ' | ' + CONVERT( nvarchar, @publish_bit )
        select @cmd = @cmd + ' WHERE id = (SELECT objid FROM sysarticles WHERE name = '''
        select @cmd = @cmd + @article + ''' and pubid = ' + CONVERT( nvarchar, @pubid ) + ')'

        EXEC (@cmd)

        IF @@ERROR <> 0
        BEGIN
            if @@trancount > 0
            begin
                ROLLBACK TRAN sp_MSaddexecarticle
                commit tran
            end
            RETURN (1)
        END


    COMMIT TRAN
go
 
EXEC dbo.sp_MS_marksystemobject sp_MSaddexecarticle
GO

print ''
print 'Creating procedure sp_MSaddschemaarticle'
go

CREATE PROCEDURE sp_MSaddschemaarticle
    @publication        sysname,        /* Name of the publciation */
    @article            sysname,        /* Name of the article */
    @source_object      sysname,        /* Name of object to be replicated */
    @destination_object sysname,        /* Name of the object created on the subscriber */
    @type               tinyint,        /* Must be one of 0x20, 0x40, or 0x80 */
    @creation_script    nvarchar(255),  /* custom creation script for the article */
    @description        nvarchar(255),  /* article description */
    @pre_creation_cmd   nvarchar(10),   /* must be 'none' or 'drop' */
    @schema_option      binary(8),
    @destination_owner  sysname,        /* owner of the article object on the subscriber */
    @status             tinyint, 
    @artid              int OUTPUT
AS
    SET NOCOUNT ON
    DECLARE @retcode int
        
    DECLARE @source_owner           sysname
    DECLARE @object                 sysname
    DECLARE @bInTran                bit
    DECLARE @pubid                  int
    DECLARE @source_objid           int
    DECLARE @pre_creation_cmdid     tinyint
    
    /* Variables for setting up RPC call to the Distributor */
    DECLARE @distproc               nvarchar(2000)
    DECLARE @distributor            sysname
    DECLARE @distributiondb         sysname
    DECLARE @dbname                 sysname
    DECLARE @valid_schema_options   int
    
    SELECT @bInTran = 0

    SELECT @source_owner = PARSENAME(@source_object, 2)
    SELECT @object = PARSENAME(@source_object, 1)

    /* Note that @article & @publication has been by sp_addarticle 
       as non-null */
        
    /*
    ** Get the pubid of the publication
    */
    SELECT @pubid = NULL
    SELECT @pubid = pubid
      FROM syspublications
     WHERE name = @publication

    IF @pubid IS NULL
    BEGIN
        RAISERROR (14027, 11, -1, @publication)
        RETURN (1)
    END

    /*
    ** Get the source object id
    */
    SELECT @source_objid = NULL
    SELECT @source_objid = OBJECT_ID(@source_object)    

    /*
    **  Destination object name
    */
    IF @destination_object IS NULL
        SELECT @destination_object = @source_object

    /*
    ** Parameter check: @type
    ** @type must correspond to the object type of the source object
    **
    ** @type = 0x20 => source object type = 'P'
    ** @type = 0x40 => source object type = 'V'
    ** @type = 0x80 => source object type = 'FN' OR 'TF' OR 'IF'
    */
    IF @type = 0x20
    BEGIN
        IF NOT EXISTS (SELECT *
                         FROM sysobjects
                        WHERE id = @source_objid
                          AND xtype = 'P ')
        BEGIN
            RAISERROR(21219, 16, -1)
            RETURN (1)
        END
    END
    ELSE IF @type = 0x40
    BEGIN
        IF NOT EXISTS (SELECT *
                         FROM sysobjects 
                        WHERE id = @source_objid
                          AND xtype = 'V ')
        BEGIN
            RAISERROR(21221, 16, -1)
            RETURN (1)
        END
    END   
    ELSE IF @type = 0x80
    BEGIN
        IF NOT EXISTS (SELECT *
                         FROM sysobjects 
                        WHERE id = @source_objid
                          AND (xtype in ('FN','TF','IF')))
        BEGIN
            RAISERROR(21228, 16, -1)            
            RETURN (1)
        END
    END


    /*
    ** Parameter check: @schema_option
    ** @schema_option can only contain the bits 0x0000000000000001 and
    ** 0x0000000000002000
    ** for schema only articles except view. View articles can contain 
    ** the options 0x0000000000000010, 0x0000000000000020, and 
    ** 0x0000000000000100 in addition to the aforementioned options.
    */
    IF @type = 0x40
    BEGIN

        -- Since only the lower 32 bits of @schema_option are
        -- currently used, the following check is sufficient.
        -- Note that @schema_option should have been padded out by now
        DECLARE @schema_option_lodword int
        SELECT @valid_schema_options = 0x2151
        SELECT @schema_option_lodword = fn_replgetbinary8lodword(@schema_option)
        IF (@schema_option_lodword & ~@valid_schema_options) <> 0
        BEGIN
            RAISERROR (21229, 16, -1)
            RETURN (1)
        END
    END
    ELSE IF @schema_option NOT IN (0x0000000000000000,
                                   0x0000000000000001,
                                   0x0000000000002000,
                                   0x0000000000002001)
    BEGIN
        RAISERROR (21222, 16, -1)
        RETURN (1)
    END 
    
    /*
    ** Parameter check: @pre_creation_command must be
    ** 'drop' (id = 1) or 'none' (id = 0)
    */
    SELECT @pre_creation_cmd = LOWER(@pre_creation_cmd collate SQL_Latin1_General_CP1_CS_AS)
    IF @pre_creation_cmd NOT IN (N'none', N'drop')
    BEGIN
        RAISERROR(21223, 16, -1)
        RETURN (1)
    END
    
    IF @pre_creation_cmd = N'none'
        SELECT @pre_creation_cmdid = 0
    ELSE IF @pre_creation_cmd = N'drop'
        SELECT @pre_creation_cmdid = 1
    
    /*
    ** Parameter Check:  @article, @publication.
    ** Check if the article already exists in this publication.
    */

    IF EXISTS (SELECT *
                 FROM sysextendedarticlesview
                WHERE pubid = @pubid
                  AND name = @article)
        BEGIN
            RAISERROR (14030, 16, -1, @article, @publication)
            RETURN (1)
        END

    BEGIN TRANSACTION sp_MSaddschemaarticle
    SAVE TRANSACTION sp_MSaddschemaarticle
    SELECT @bInTran = 1    

    -- Add a dummy record to sysarticles to reserve an artid
    INSERT sysarticles (dest_table, filter, name, objid, pubid, 
                        pre_creation_cmd, status, sync_objid, type)  
    VALUES (@destination_object, N'', @article, @source_objid, @pubid,
            @pre_creation_cmdid, @status, @source_objid, @type)

    IF @@ERROR <> 0
        GOTO Failure

    SELECT @artid = @@IDENTITY   

    -- Now that we have reserved an artid in sysarticles,
    -- we can remove the dummy record
    
    DELETE sysarticles WHERE artid = @artid AND pubid = @pubid

    IF @@ERROR <> 0
        GOTO Failure

    -- Insert a record into sysschemaarticles to represent this
    -- schema only article
    INSERT sysschemaarticles 
    VALUES (@artid, @creation_script, @description, @destination_object,
            @article, @source_objid, @pubid, @pre_creation_cmdid, @status,
            @type, @schema_option, @destination_owner)

    IF @@ERROR <> 0
        GOTO Failure

    -- Make a bit in replinfo to prevent the source object from
    -- being dropped
    UPDATE sysobjects SET replinfo = replinfo | 0x00000200 WHERE id = @source_objid
    
    IF @@ERROR <> 0
        GOTO Failure       

    COMMIT TRANSACTION  

    RETURN (0)
Failure:

    IF @bInTran = 1
    BEGIN
        ROLLBACK TRANSACTION sp_MSaddschemaarticle
        COMMIT TRANSACTION
    END
    RETURN (1)
go

EXEC dbo.sp_MS_marksystemobject sp_MSaddschemaarticle
GO

print ''
print 'Creating procedure sp_addarticle'
go
CREATE PROCEDURE sp_addarticle
    @publication        sysname,                /* publication name */
    @article            sysname,                /* article name */
    @source_table       nvarchar (386) = NULL,  /* table name */
    @destination_table  sysname = NULL,	        /* destination table name */
    @vertical_partition nchar(5) = 'false',     /* vertical partition */
    @type               sysname = NULL,	        /* article type */
    @filter	            nvarchar (386) = NULL,  /* stored procedure used to filter table */
    @sync_object        nvarchar (386) = NULL,  /* view or table used for synchronization */
    @ins_cmd            nvarchar (255) = NULL,  /* insert format string */
    @del_cmd            nvarchar (255) = NULL,  /* delete format string */
    @upd_cmd            nvarchar (255) = NULL,  /* update format string */
    @creation_script    nvarchar (127) = NULL,  /* article schema script */
    @description        nvarchar (255) = NULL,  /* article description */
    @pre_creation_cmd   nvarchar(10) = 'drop',  /* 'none', 'drop', 'delete', 'truncate' */
    @filter_clause      ntext    = NULL,        /* where clause */
    @schema_option      varbinary(8) = NULL,
	@destination_owner  sysname = NULL,
	@status	            tinyint = 16,           /* Default: binary command format */
	@source_owner       sysname = NULL,         /* NULL for 6.5 users, not NULL for 7.0 users */
	@sync_object_owner  sysname = NULL,         /* NULL for 6.5 users, not NULL for 7.0 users */
	@filter_owner       sysname = NULL,	        /* NULL for 6.5 users, not NULL for 7.0 users */
	@source_object      sysname = NULL,	        /* if @source_table is NULL, this parameter can not be NULL */
    @artid              int = NULL OUTPUT,       /* article id of the new article  */
    @auto_identity_range	nvarchar(5)	= 'FALSE',	/* set it to false for now - change is possible */
    @pub_identity_range		bigint	= NULL,
    @identity_range			bigint = NULL,
    @threshold				int	= NULL,
	@force_invalidate_snapshot bit = 0	/* Force invalidate existing snapshot */
    AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */	
    DECLARE @bak_source sysname
	DECLARE @num_columns int
    DECLARE @accessid smallint
    DECLARE @db sysname
    DECLARE @filterid int
    DECLARE @object sysname
    DECLARE @owner sysname
    DECLARE @pubid int
    DECLARE @publish_bit smallint
    DECLARE @retcode int
    DECLARE @site sysname
    DECLARE @syncid int
    DECLARE @tabid int
    DECLARE @typeid smallint
    DECLARE @pkkey sysname
    DECLARE @i int
    DECLARE @indid int
    DECLARE @precmdid int
    DECLARE @object_type nchar(2)
    DECLARE @push tinyint
    DECLARE @dbname sysname
    DECLARE @cmd nvarchar(255)
    DECLARE @fHasPk int
    DECLARE @no_sync tinyint
    DECLARE @immediate_sync bit
    DECLARE @is_filter_in_use int
    DECLARE @distributor sysname
    DECLARE @distribdb sysname
    DECLARE @distproc nvarchar (255)
    DECLARE @sync_method tinyint
    -- SyncTran
    DECLARE @autogen_sync_procs_id int
	DECLARE @custom_proc_name nvarchar(255)
	DECLARE @guid varbinary(16)
	declare @allow_sync_tran bit
    DECLARE @repl_freq int
	declare @allow_queued_tran bit
	declare @allow_dts bit
	declare @merge_pub_object_bit  int
	declare @valid_ins_cmd nvarchar(255)
			,@valid_upd_cmd nvarchar(255)
			,@valid_del_cmd nvarchar(255)
			,@MSrepl_tran_version_datatype sysname
			,@colid int
	declare @backward_comp_level int
	declare @schema_option_int int
	select @backward_comp_level = 10 -- default to sphinx
	
	select @merge_pub_object_bit 	= 128
    SELECT @push = 0
    SELECT @dbname = DB_NAME()

    SELECT @publish_bit = 1

    SELECT @no_sync = 2 /* no sync type in syssubscriptions */
    /*
    ** Security Check.
    */
	exec @retcode = dbo.sp_MSreplcheck_publish
	if @@ERROR <> 0 or @retcode <> 0
		return(1)

    /*
    ** Padding out the specified schema option to the left
    */
    select @schema_option = fn_replprepadbinary8(@schema_option)

    /*
    ** Parameter Check: @article.
    ** The @article name cannot be NULL and must conform to the rules
    ** for identifiers.
    */

    IF @article IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@article')
            RETURN (1)
        END

	exec @retcode = dbo.sp_MSreplcheck_name @article
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    if LOWER(@article) = 'all'
        BEGIN
            RAISERROR (14032, 16, -1, '@article')
            RETURN (1)
        END

    /*
    ** Parameter Check: @publication.
    ** The @publication name cannot be NULL and must conform to the rules
    ** for identifiers.
    */

    IF @publication IS NULL
    BEGIN
        RAISERROR (14043, 16, -1, '@publication')
        RETURN (1)
    END

    EXECUTE @retcode = dbo.sp_validname @publication

    IF @retcode <> 0
    RETURN (1)

    /*
    ** Parameter Check: @destination_owner.
    ** The @destination_owner must conform to the rules
    ** for identifiers.
    */

    if @destination_owner is not null
	BEGIN
		EXECUTE @retcode = dbo.sp_validname @destination_owner

		IF @retcode <> 0
			RETURN (1)
	END

    /*
    ** Parameter Check: @source_table.
    ** Check to see that the @source_table is local, that it conforms
    ** to the rules for identifiers, and that it is a table, and not
    ** a view or another database object.
    */

    IF @source_table IS NULL
        BEGIN
        	if @source_object is NOT NULL
        		select @source_table = @source_object
        	else
        		begin
            		RAISERROR (14043, 16, -1, '@source_table')
            		RETURN (1)
            	end
        END

	IF @source_owner is NULL -- 6.5 users only
	begin
    	IF @source_table LIKE '%.%.%' AND PARSENAME(@source_table, 3) <> DB_NAME()
       		BEGIN
          		RAISERROR (14004, 16, -1, @source_table)
      			RETURN (1)
       		END
    end

	-- For 7.0 users, @source_owner is not nullable.
	
	select @bak_source = @source_table
	
	IF @source_owner is not NULL
		begin
			select @source_table = QUOTENAME(@source_owner) + '.' + QUOTENAME(@source_table)
		    IF @destination_table IS NULL
        		SELECT @destination_table = @bak_source
        end
    ELSE 
	begin
		-- Make @source_table qualifed.
		select @tabid = object_id(@source_table)
		if @tabid is not null
		begin
			exec @retcode = dbo.sp_MSget_qualified_name @tabid, @source_table output
			if @retcode <> 0 or @@error <> 0
				return 1	
			IF @destination_table IS NULL
			-- Set destination_table if not provided or default by now.
			-- If @source_table is qualified (6.x behavior) only use table name for destination name.
				SELECT @destination_table = PARSENAME(@source_table, 1)	
		end
	end
        
	select @num_columns=count(*) from syscolumns where id = object_id(@source_table)
	if @num_columns > 255
		begin
			RAISERROR (20068, 16, -1, @source_table, 255)
            RETURN (1)
        end

    /*
    **  Get the id of the @source_table
    */
    SELECT @tabid = id, @object_type = type
    FROM sysobjects
    WHERE id = OBJECT_ID(@source_table)

    IF @tabid IS NULL
        BEGIN
            RAISERROR (14027, 11, -1, @source_table)
            RETURN (1)
        END

    /*
    ** Parameter Check: @type
    ** If the article is added as a 'schema-bound view schema only' article,
    ** make sure that the source object is a schema-bound view.
    ** Conversely, a schema-bound view cannot be published as a 
    ** 'view schema only' article.
    */
    IF @type IS NULL
		SELECT @type = 'logbased'

    select @type = lower(@type collate SQL_Latin1_General_CP1_CS_AS)

    if @type = N'indexed view schema only' and objectproperty(@tabid, 'IsSchemaBound') <> 1
    begin
        raiserror (21277, 11, -1, @source_table)        
        return (1)    
    end

    /*
    ** If the article is published as an IV logbased article, we'd better make
    ** sure that the view is schema bound and it has a clustered index.
    ** Conversely, a schema-bound view should never be published as a regular
    ** table logbased article.
    */
    if @type in (N'indexed view logbased', 
                 N'indexed view logbased manualfilter', 
                 N'indexed view logbased manualview', 
                 N'indexed view logbased manualboth') and 
        (isnull(objectproperty(@tabid, 'IsSchemaBound'),0) <> 1 or
         not exists (select * from sysindexes where id = @tabid) or
         isnull(objectproperty(@tabid, 'IsView'),0) = 0) 
    begin
        raiserror (21278, 11, -1, @source_table)
        return (1)
    end    
    
    if @type in (N'view schema only', 
                 N'logbased', 
                 N'logbased manualfilter', 
                 N'logbased manualview', 
                 N'logbased manualboth') and 
        objectproperty(@tabid, 'IsSchemaBound') = 1
    begin
        raiserror (21275, 11, -1, @source_table)
        return (1)
    end

	-- Check if there are snapshot or subscriptions and raiserror if needed.
    EXECUTE @retcode  = dbo.sp_MSreinit_article
        @publication = @publication, 
		-- Virtual subscriptions of all the articles will be deactivated.
        -- @article = @article,
		@need_new_snapshot = 1,
		@force_invalidate_snapshot = @force_invalidate_snapshot,
		@check_only = 1
    IF @@ERROR <> 0 OR @retcode <> 0
		return 1


    -- Encrypted objects are not publishable for replication
    IF @type IN (N'proc exec',
                 N'serializable proc exec',
                 N'proc schema only',
                 N'indexed view schema only',
                 N'indexed view logbased',
                 N'indexed view logbased manualfilter',
                 N'indexed view logbased manualview',
                 N'indexed view logbased manualboth',
                 N'view schema only',
                 N'func schema only')
    BEGIN
        IF EXISTS (SELECT * FROM syscomments
                    WHERE id = @tabid
                      AND encrypted = 1)
        BEGIN
            RAISERROR(21004, 16, -1, @source_table)        
            RETURN (1)
        END
    END   

    -- at this point, we've done all the common parameter checks.
    -- If this is a procedure execution article, branch to the proc execution publishing
    -- routine; or if this is a schema only procedure or view article, brach to the
    -- schema only article publishing routine; otherwise continue processing as if it were a table

    IF (@object_type = 'P' AND @type <> N'proc schema only') or 
		@type IN (N'proc schema only', N'view schema only', N'func schema only', 'indexed view schema only')
    BEGIN
		begin tran
        save TRAN sp_addarticle

		IF (@object_type = 'P' AND @type <> N'proc schema only')
		begin

            IF @schema_option IS NULL
            BEGIN
                SELECT @schema_option = 0x0000000000000001
            END

			EXECUTE @retcode = dbo.sp_MSaddexecarticle @publication,
				@article,
				@source_table,
				@destination_table,
				@type,
				@creation_script,
				@description,
				@pre_creation_cmd,
				@schema_option,
				@destination_owner,
				@artid OUTPUT
		end
		else 
		begin
		    -- Note: a transaction is started inside sp_MSaddschemaarticle        
            IF @schema_option IS NULL
            BEGIN
                SELECT @schema_option = 0x0000000000000001
            END

			IF @type = N'proc schema only'
			BEGIN
				SELECT @typeid = 0x20
			END
			ELSE IF @type = N'view schema only'
			BEGIN
				SELECT @typeid = 0x40
			END    
			ELSE IF @type = N'func schema only'
			BEGIN
				SELECT @typeid = 0x80
			select @backward_comp_level = 40 -- UDF not available in sphinx
			END
			ELSE IF @type = N'indexed view schema only'
			BEGIN
				SELECT @typeid = 0x40
			select @backward_comp_level = 40 -- SchemaBinding not available in sphinx
			END    

			EXECUTE @retcode = dbo.sp_MSaddschemaarticle 
				@publication = @publication,
				@article = @article,
				@source_object = @source_table,
				@destination_object = @destination_table,
				@type = @typeid,
				@creation_script = @creation_script,
				@description = @description,
				@pre_creation_cmd = @pre_creation_cmd,
				@schema_option = @schema_option,
				@destination_owner = @destination_owner,
				@status = @status,
				@artid = @artid OUTPUT

		end
		IF @retcode <> 0 or @@error <> 0
			goto UNDO
	end
    ELSE
	begin

		/*
		** Make sure that the table name specified is a table and not a view.
		*/

		IF NOT EXISTS (SELECT * FROM sysobjects
			WHERE id = (SELECT OBJECT_ID(@source_table))
			AND type = 'U')
			AND NOT EXISTS ( SELECT * FROM sysobjects so, sysindexes si
			WHERE so.id = OBJECT_ID(@source_table)
			AND so.type = 'V'
			AND si.id = so.id )
		BEGIN
			RAISERROR (14028, 16, -1)
			RETURN (1)
		END


		/*
		** Parameter Check:  @destination_table.
		** If the destination table is not specified, assume it's the same
		** as the source table.  Make sure that the table name is not qualified.
		*/

		/*
		** Parameter Check: @vertical_partition
		** Check to make sure that the vertical partition is either TRUE or FALSE.
		*/

		SELECT @vertical_partition = LOWER(@vertical_partition collate SQL_Latin1_General_CP1_CS_AS)
		IF @vertical_partition NOT IN ('true', 'false')
		BEGIN
			RAISERROR (14029, 16, -1)
			RETURN (1)
		END

		/*
		** Parameter Check: @filter
		** Make sure that the filter is a valid stored procedure.
		*/
		IF @filter IS NOT NULL
		BEGIN
			IF @filter_owner IS NULL
			BEGIN
    			select @object = PARSENAME( @filter, 1 )
    			select @owner  = PARSENAME( @filter, 2 )
	    		select @db     = PARSENAME( @filter, 3 )
    			select @site   = PARSENAME( @filter, 4 )

    			if @object IS NULL
        		  return 1
			END  
			ELSE
			BEGIN
				select @filter = QUOTENAME(@filter_owner) + '.' + QUOTENAME(@filter)
			END

			/*
			** Get the id of the @filter
			*/
			select @filterid = id from sysobjects where
				id = OBJECT_ID(@filter) and type = 'RF'
			IF @filterid IS NULL
			BEGIN
				RAISERROR (14027, 11, -1, @filter)
				RETURN (1)
			END

			EXEC @is_filter_in_use = dbo.sp_MSdoesfilterhaveparent @filterid
			if( @is_filter_in_use <> 0 )
			BEGIN
				RAISERROR( 21009, 11, -1 )
				RETURN (1)
			END
		END
		ELSE
			select @filterid = 0


		/*
		** Get the pubid and its properties
		*/
		SELECT @pubid = pubid, @autogen_sync_procs_id = autogen_sync_procs, @sync_method = sync_method,
			@allow_sync_tran = allow_sync_tran,
			@allow_queued_tran = allow_queued_tran,
			@allow_dts	= allow_dts,
			@repl_freq = repl_freq 
		FROM syspublications where name = @publication

		IF @pubid IS NULL
			BEGIN
				RAISERROR (14027, 11, -1, @publication)
				RETURN (1)
			END

		-- Only allow table and index view for dts publications
		if @allow_dts <> 0 and @type not in ( 
			N'logbased', 
			N'logbased manualfilter', 
			N'logbased manualview', 
			N'logbased manualboth',
			N'indexed view logbased', 
			N'indexed view logbased manualfilter', 
			N'indexed view logbased manualview', 
			N'indexed view logbased manualboth')
		begin
			raiserror(20611, 16, -1)
			return(1)
		end

		-- can't do fancy type stuff with MVs!

		ELSE IF (@allow_sync_tran <> 0 
				OR @allow_queued_tran <> 0)
				AND EXISTS ( select * from sysobjects 
					where id = OBJECT_ID(@source_table)
					and xtype = 'V' )
		BEGIN
			RAISERROR(14059, 16, -1)
			RETURN 1
		END
		
		--
		-- parameter check: @status
		-- bits 8, 16, 64 can be set directly
		-- Other bits from 1 ~ 64 are used but cannot be set here.
		-- Bit 64 can only be set for publication that allows DTS.
		-- Bit 32 is set internally according to whether or not timestamp is in
		-- the partition for queued publications.

		IF (@status & ~ 88 ) <> 0 
		BEGIN
			RAISERROR( 21061, 16, -1, @status, @article )
			RETURN (1)
		END
		else if (@status & 64 <> 0 and @allow_dts = 0)
		begin
			raiserror(20590, 16, -1)
			return (1)
		end


		/*
		** Parameter Check:  @article, @publication.
		** Check if the article already exists in this publication.
		*/

		IF EXISTS (SELECT *
					 FROM sysextendedarticlesview
					WHERE pubid = @pubid
					  AND name = @article)
			BEGIN
				RAISERROR (14030, 16, -1, @article, @publication)
				RETURN (1)
			END

		/*
		** Set the typeid.  The default type is logbased.  Anything else is
		** currently undefined (reserved for future use).
		**
		**      @typeid     type
		**      =======     ========
		**          1     logbased
		**          3     logbased manualfilter
		**          5     logbased manualview
		**          7     logbased manualboth
		**          8     proc exec              (valid in dbo.sp_MSaddexecarticle)
		**          24    serializable proc exec (valid in dbo.sp_MSaddexecarticle)
		**          32    proc schema only       (valid in dbo.sp_MSaddschemaarticle)
		**          64    view schema only       (valid in dbo.sp_MSaddschemaarticle)
		**         128    func schema only       (valid in dbo.sp_MSaddschemaarticle)
		**       Note that for the following article types, the 256 bit is not really persisted
		**         257    indexed view logbased
		**         259    indexed view logbased manualfilter
		**         261    indexed view logbased manualview
		**         263    indexed view logbased manualboth
		**         320    indexed view schema only (valid in dbo.sp_MSaddschemaarticle)
		*/


		IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) NOT IN 
                                    ('logbased', 
									 'logbased manualfilter', 
									 'logbased manualview', 
									 'logbased manualboth',
									 'indexed view logbased', 
									 'indexed view logbased manualfilter', 
									 'indexed view logbased manualview', 
									 'indexed view logbased manualboth',
									 'proc schema only', 
									 'view schema only')
		BEGIN
			RAISERROR (14023, 16, -1)
			RETURN (1)
		END

		IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = 'logbased' OR LOWER(@type) = 'indexed view logbased'
			SELECT @typeid = 1
		ELSE IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = 'logbased manualfilter' OR LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = 'indexed view logbased manualfilter'
		   SELECT @typeid = 3
		ELSE IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = 'logbased manualview' OR LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = 'indexed view logbased manualview'
		   SELECT @typeid = 5
		ELSE IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = 'logbased manualboth' OR LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) = 'indexed view logbased manualboth'
			SELECT @typeid = 7

		/*
		** Set the precmdid.  The default type is 'drop'.
		**
		**      @precmdid   pre_creation_cmd
		**      =========   ================
		**            0     none
		**          1     drop
		**          2     delete
		**          3     truncate
		*/
		IF LOWER(@pre_creation_cmd collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('none', 'drop', 'delete', 'truncate')
		BEGIN
			RAISERROR (14061, 16, -1)
			RETURN (1)
		END

		/*
		** Determine the integer value for the pre_creation_cmd.
		*/
		IF LOWER(@pre_creation_cmd collate SQL_Latin1_General_CP1_CS_AS) = 'none'
			SELECT @precmdid = 0
		ELSE IF LOWER(@pre_creation_cmd collate SQL_Latin1_General_CP1_CS_AS) = 'drop'
			SELECT @precmdid = 1
		ELSE IF LOWER(@pre_creation_cmd collate SQL_Latin1_General_CP1_CS_AS) = 'delete'
			SELECT @precmdid = 2
		ELSE IF LOWER(@pre_creation_cmd collate SQL_Latin1_General_CP1_CS_AS) = 'truncate'
			SELECT @precmdid = 3

		IF @sync_object IS NULL
			select @syncid = @tabid
		ELSE
		BEGIN

		IF @sync_object_owner is NULL  -- 6.5 only
		BEGIN

			/*
			** Parameter Check: @sync_object.
			** Check to see that the sync_object is local and that it
			** conforms to the rules for identifiers.
			*/

			select @object = PARSENAME( @sync_object, 1 )
			select @owner  = PARSENAME(  @sync_object, 2 )
			select @db     = PARSENAME(  @sync_object, 3 )
			select @site   = PARSENAME(  @sync_object, 4 )

			if @object IS NULL
				  return 1


			IF @sync_object LIKE '%.%.%' AND @db <> DB_NAME()
			BEGIN
				RAISERROR (14004, 16, -1, @sync_object)
				RETURN (1)
			END

		END -- end of 65 processing
		else -- for sphinx, @sync_object_owner can not be null
			select @sync_object = QUOTENAME(@sync_object_owner) + '.' + QUOTENAME(@sync_object)
			
			/*
			**  Get the id of the @sync_object
			*/

			SELECT @syncid = id FROM sysobjects WHERE id = OBJECT_ID(@sync_object)

			IF @syncid IS NULL
			BEGIN
				RAISERROR (14027, 11, -1, @sync_object)
				RETURN (1)
			END

			/*
			** Make sure the sync object specified is a table or a view.
			*/

			IF NOT EXISTS (SELECT * FROM sysobjects
					WHERE id = (SELECT OBJECT_ID(@sync_object))
					AND (type = 'U' or
						 type = 'V'))
			BEGIN
				RAISERROR (14031, 16, -1)
				RETURN (1)
			END
		END

		/*
		** If the publication is log-based, or allows updating subscribers
		** make sure there is a primary key on the source table.
		** or a UCI on the view.
		** NOTE!  sprok in SPSUP.SQL
		*/
		IF EXISTS (SELECT * FROM syspublications 
			WHERE pubid = @pubid AND
				(repl_freq = 0 OR allow_sync_tran = 1 OR @allow_queued_tran = 1))
		BEGIN
			EXEC @fHasPk = dbo.sp_MSreplsup_table_has_pk @tabid

			IF @fHasPk = 0
			BEGIN
				IF EXISTS ( select * from sysobjects 
					where id = OBJECT_ID(@source_table)
					and xtype = 'U' )
				BEGIN
					RAISERROR (14088, 16, -1, @source_table)
				END
				ELSE
				BEGIN
					RAISERROR( 14089, 16, -1, @source_table)
				END
				RETURN (1)
			END
		END

		/*
		** Parameter Check:  @creation_script and @schema_option
		** @schema_option cannot be null
		** If @schema_option is 0, there have to be @creation_script defined.
		*/
		IF @schema_option IS NULL
		BEGIN
			-- Snapshot publication, no custom proc. generation
			-- We need insert proc for snapshot publications for DTS.
			-- Do not generate user triggers by default (0x00100 - user trigger flag)
			IF @repl_freq = 1 and @allow_dts = 0
			BEGIN
				SELECT @schema_option  = 0x0000000000000071
			END
			ELSE
			BEGIN
				SELECT @schema_option  = 0x00000000000000F3
			END

			if (@allow_queued_tran = 1)
				select @schema_option = fn_replprepadbinary8(fn_replgetbinary8lodword(@schema_option) | 0x8000)
		END

		/*
		** Parameter Check: @schema_option
		** If bit 0x2 is set, this cannot be an article for a snapshot publication
		** For queued updating, DRI_Primary Key option has to be set
		*/
		IF ((CONVERT(INT, @schema_option) & 0x2) <> 0) AND (@repl_freq = 1)
		BEGIN
			RAISERROR (21143, 16, -1)
			RETURN (1)
		END
		else if ((@allow_queued_tran = 1) and 
			((fn_replgetbinary8lodword(@schema_option) & 0x8000) = 0))
		BEGIN
			RAISERROR (21394, 16, 1)
			RETURN (1)
		END
		
		/* 
		** Since custom proc name is based on destination table name
		*/
		
		if @allow_dts = 0
			set @custom_proc_name = @destination_table
		else
			set @custom_proc_name = @article

		-- Publication allow dts/queued must use parameterized commands and 
		-- ins/upd/del commands are generated 
		-- internally and are fixed (they will used as keys to find the correct transformations)
		if (@allow_dts = 1 or @allow_queued_tran = 1)
		begin
			select @valid_ins_cmd = N'CALL sp_MSins_' + @custom_proc_name
			IF @repl_freq = 0
			begin
				--
				-- Note :
				-- Now that we are autogenrating the custom procs for queued
				-- we will follow the current behavior :
				-- for queued INS/UPD/DEL will use CALL/XCALL/XCALL respectively
				-- for DTS INS/UPD/DEL will use CALL/XCALL/XCALL
				-- In case it allows both queued and DTS, DTS will take precedence
				-- YWU: Check this
				--
				select @valid_del_cmd = N'XCALL sp_MSdel_' + @custom_proc_name
				if (@allow_queued_tran = 1)
				begin
					select @valid_upd_cmd = N'XCALL sp_MSupd_' + @custom_proc_name
				end
				if (@allow_dts = 1)
				begin
					if @status = 16
						select @valid_upd_cmd = N'CALL sp_MSupd_' + @custom_proc_name
					else 
						select @valid_upd_cmd = N'XCALL sp_MSXpd_' + @custom_proc_name
				end
			end
			else
			begin
				select @valid_upd_cmd = N'SQL'
				select @valid_del_cmd = N'SQL'
			end

			if (@allow_dts = 1)
			begin
				-- For publication allows DTS, @status can only be 16 or 48
				-- 
				if  (@status <> 16 and @status <> 80) or
					(@ins_cmd is not null and @ins_cmd <> @valid_ins_cmd) or 
					(@upd_cmd is not null and @upd_cmd <> @valid_upd_cmd) or 
					(@del_cmd is not null and @del_cmd <> @valid_del_cmd)
				begin
					raiserror(21174, 16, -1)
					return (1)
				end
			end 
			else if (@allow_queued_tran = 1)
			begin
				if  (@ins_cmd is not null and @ins_cmd != @valid_ins_cmd) or 
					(@upd_cmd is not null and @upd_cmd != @valid_upd_cmd) or 
					(@del_cmd is not null and @del_cmd != @valid_del_cmd)
				begin
					raiserror(21191, 16, -1, @valid_ins_cmd, @valid_upd_cmd, @valid_del_cmd)
					return (1)
				end
			end
				
			select @ins_cmd = @valid_ins_cmd
			select @upd_cmd = @valid_upd_cmd
			select @del_cmd = @valid_del_cmd
		end
		-- If pub sync_type is character mode bcp(1) 
		else if @sync_method = 1
		begin
			-- Parameterized command is not supported. Mask it off.
			select @status = @status & ~16
			if @ins_cmd is NULL  select @ins_cmd = 'SQL'
			if @upd_cmd is NULL  select @upd_cmd = 'SQL'
			if @del_cmd is NULL  select @del_cmd = 'SQL'
		end

		/*
		** Parameter Check: @schema_option
		** If Autogeneration of custom procedures is not enabled 
		** then the default commands will be SQL
		*/
		IF ((CONVERT(int, @schema_option) & 0x2) = 0)
		BEGIN
			if @ins_cmd is NULL  select @ins_cmd = 'SQL'
			if @upd_cmd is NULL  select @upd_cmd = 'SQL'
			if @del_cmd is NULL  select @del_cmd = 'SQL'
		END
	 		 
		-- Autogenerate custom procedure names if not provided.
		-- Use destination table name as the base name by default. 
	/*	
		-- Use article name by default. If the name is too long or there's confliction,
		-- use guid (to prevent proc for diff art in diff pub has same name which cause problems
		-- when being subscribed by the same subscriber db.
		if ((@source_object is not NULL and len(@article) > 119) or
			(@source_object is NULL and len(@article) > 21)) or
			exists (select * from sysarticles where name = @article)
		begin
			set @guid = CONVERT(varbinary(16), LEFT(NEWID(),8))
			exec @retcode = master.dbo.xp_varbintohexstr @guid, @custom_proc_name OUTPUT
			if @@error <> 0 or @retcode <> 0
				RETURN(1)
		end
		else
			set @custom_proc_name = @article
	*/

		-- If no command then construct name 
		if @ins_cmd is NULL
		begin
			if (@status & 16) <> 0 -- parameterized
			begin
				if @source_object is not NULL  -- 7.0 format
					set @ins_cmd = N'CALL ' + convert (sysname, 'sp_MSins_' + @custom_proc_name)
				else -- 6.x compatible
					set @ins_cmd = N'CALL ' + convert(nvarchar(30), 'sp_MSins_' + @custom_proc_name)
			end
			else
				select @ins_cmd = 'SQL'
		end

		if @del_cmd is NULL
		begin
			if (@status & 16) <> 0 -- parameterized
			begin
				if @source_object is not NULL  -- 7.0 format
					set @del_cmd = N'CALL ' + convert (sysname, 'sp_MSdel_' + @custom_proc_name)
				else -- 6.x compatible
					set @del_cmd = N'CALL ' + convert(nvarchar(30), 'sp_MSdel_' + @custom_proc_name)
			end
			else
				select @del_cmd = 'SQL'
		end

		if @upd_cmd is NULL
		begin
			if (@status & 16) <> 0 -- parameterized
			begin
				if @source_object is not NULL  -- 7.0 format
					set @upd_cmd = N'MCALL ' + convert (sysname, 'sp_MSupd_' + @custom_proc_name)
				else -- 6.x compatible
					set @upd_cmd = N'CALL ' + convert(nvarchar(30), 'sp_MSupd_' + @custom_proc_name)
			end
			else
				select @upd_cmd = 'SQL'
		end

		--
		-- Sync/QueuedTran
		-- Add guid column if not exists
		--
		if (@allow_sync_tran = 1 or @allow_queued_tran = 1)
		begin
			SELECT @MSrepl_tran_version_datatype = TYPE_NAME(xtype),
				@colid = colid
			FROM dbo.syscolumns  
			WHERE id = @tabid AND name = 'msrepl_tran_version'

			if (@MSrepl_tran_version_datatype IS NULL)
			begin
				--
				-- column does not exist, add it
				--
				if exists (select * from sysobjects where id = @tabid and replinfo & @merge_pub_object_bit <>0)
				begin
					--if being merged, call stored procedure to add this column.
					exec @retcode = sp_repladdcolumn @source_object=@source_table,
													 @column = 'msrepl_tran_version',
													 @typetext= 'uniqueidentifier not null default newid()'
					if @@ERROR<>0 or @retcode<>0
						return (1)
				end
				else
				begin
					exec ('alter table ' + @source_table + ' add msrepl_tran_version uniqueidentifier not null default newid()' )
	        		IF @@ERROR <> 0 
	            		RETURN (1)
				end
			end
			else
			begin
				--
				-- column exists, it should be of type uniqueidentifier
				--
				if (@MSrepl_tran_version_datatype != 'uniqueidentifier')
				begin
					raiserror(21192, 16, -1) 
					RETURN (1)
				end

				-- Create the default constraint if it is not there
				if not exists (select * from sysconstraints where
					id = @tabid and 
					colid = @colid and
					status & 5 = 5) -- default
				begin 
					declare @constraint_name sysname
					select @constraint_name = 'MSrepl_tran_version_default_' + convert(nvarchar(10), @tabid)
					if not exists (select * from sysobjects where name = @constraint_name)
					begin
						exec ('alter table ' + @source_table + 
							' add constraint ' + @constraint_name + 
							' default newid() for msrepl_tran_version')
						if @@ERROR<>0 return 1
					end
				end

			end
		end

	/*  
		-- SyncTran
		-- Add timestamp column if not exists
		if @allow_sync_tran = 1 and @allow_queued_tran = 0 and ObjectProperty(@tabid, 'TableHasTimestamp') = 0
		begin
			exec ('alter table ' + @source_table + ' add msrepl_synctran_ts timestamp not null' )
			IF @@ERROR <> 0 
				RETURN (1)
		end
	*/

		-- if concurrent, must use stored procedures at subcriber when replicating commands

		if @sync_method IN (3,4) and 
		   (lower(@del_cmd) not like N'%call%' or 
			lower(@ins_cmd) not like N'%call%' or
			lower(@upd_cmd) not like N'%call%' )
		begin
			raiserror( 21153, 16, -1, @article )
			return 1
		end

		-- Identity range support
			/*
		** Parameter Check:  @allow_push.
		*/

		IF @auto_identity_range IS NULL OR LOWER(@auto_identity_range collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
			BEGIN
				RAISERROR (14148, 16, -1, '@auto_identity_range')
				RETURN (1)
			END
		IF LOWER(@auto_identity_range collate SQL_Latin1_General_CP1_CS_AS) = 'true' 
		begin
			if @allow_queued_tran = 0
			begin
				raiserror(21231, 16 ,-1)
				return 1
			end
			/*
			** If you want to have identity support, @range and threshold can not be NULL
			*/
			if (@pub_identity_range is NULL or @identity_range is NULL or @threshold is NULL)
				begin
					raiserror(21193, 16, -1)
					return (1)
				end

			if OBJECTPROPERTY(@tabid, 'tablehasidentity') <> 1
			begin
				raiserror(21194, 16, -1)
				return (1)
			end

			if @pub_identity_range <= 1 or @identity_range <= 1
			begin
				raiserror(21232, 16 ,-1)
				return 1
			end

			if @threshold < 1 or @threshold > 100
			begin
				raiserror(21233, 16 ,-1)
				return 1
			end

			declare @xtype int, @xprec int, @max_range bigint
			select @xtype=xtype, @xprec=xprec from syscolumns where id=@tabid and 
				columnproperty(id, name, 'IsIdentity')=1
			select @max_range =
					case @xtype when 52 then power((convert(bigint,2)), 8*2-1) - 1 --smallint 
						when 48 then power((convert(bigint,2)), 8-1) - 1 		 --tinyint
						when 56 then power((convert(bigint,2)), 8*4-1) - 1 		 --int
						when 127 then power((convert(bigint,2)), 62) - 1 + power((convert(bigint,2)), 62)  	--bigint
       					when 108 then power((convert(bigint,10)), @xprec) 	 --numeric
       					when 106 then power((convert(bigint,10)), @xprec) 	 --decimal
 					else
						power((convert(bigint,2)), 62) + power((convert(bigint,2)), 62) - 1  -- defaulted to bigint
					end
		
			if @pub_identity_range * 2 + @identity_range > (@max_range - IDENT_CURRENT(@source_table))
				begin
					raiserror(21290, 16, -1)
					return (1)
				end

			-- Set the range to negtive if incr of the identity is negtive
			if IDENT_INCR(@source_table) < 0
			begin
				select @pub_identity_range = -1 * @pub_identity_range;
				select @identity_range = -1 * @identity_range;
			end

			-- If the table is already published in queued but is not auto identity
			-- raiserror error
			if exists (select * from  sysarticles sa, sysarticleupdates au, syspublications pub where 
				sa.objid = @tabid and
				au.artid = sa.artid and
				au.pubid = pub.pubid and
				pub.allow_queued_tran = 1 and 
				au.identity_support = 0)
			begin
				raiserror (21240, 16, -1, @source_table)
				return (1)
			end

			-- If the table is already published in queued and have different auto identity 
			-- range values, raise error.
			if exists (select * from MSpub_identity_range where objid=@tabid and 
			((pub_range<>@pub_identity_range) or (range <> @identity_range) or (threshold <> @threshold)))
			begin
				raiserror(21291, 16, -1)
				return (1)
			end
		end
		else
		begin
			-- @auto_identity_range is false
			-- If the publication is queued and the table has published with auto identity
			-- already, raise error.
			if exists (select * from  sysarticles sa, sysarticleupdates au where 
				sa.objid = @tabid and
				au.artid = sa.artid and
				au.identity_support = 1)
			begin
				raiserror (21240, 16, -1, @source_table)
				return (1)
			end
		end

		-- SQL insert doesn't work because we need to do 'set identity_insert on/off' 
		-- before and after the insert.
		-- The exception is snapshot publication.
		/* Queue now uses fixed commands. Message 21234 should be removed.
		if @allow_queued_tran = 1 and OBJECTPROPERTY(@tabid, 'tablehasidentity') = 1 and
			@ins_cmd = 'SQL' and @repl_freq <> 1
		begin
			raiserror(21234, 16, -1)
			return (1)
		end
		*/

		-- Do not allow the table to be published by both merge and queued tran
		if @allow_queued_tran = 1
		begin
			if exists (select * from sysobjects where name = 'sysmergearticles')
			begin
				if exists (select * from sysmergearticles where objid = @tabid)
				begin
					declare @obj_name sysname
					select @obj_name = object_name(@tabid)
					raiserror(21266, 16, -1, @obj_name)
					return (1)
				end
			end
		end

		/*
		**  Add article to sysarticles and update sysobjects category bit.
		*/
		-- begin tran
		begin tran
		save TRAN sp_addarticle

		INSERT sysarticles (columns, creation_script, del_cmd, description,
			dest_table, filter, filter_clause, ins_cmd, name,
			objid, pre_creation_cmd, pubid,
			status, sync_objid, type, upd_cmd, schema_option,
			dest_owner)
		VALUES (0, @creation_script, @del_cmd, @description, @destination_table,
			@filterid, @filter_clause, @ins_cmd, @article, @tabid,
			@precmdid, @pubid, @status, @syncid, @typeid, @upd_cmd, @schema_option, 
			@destination_owner)

		IF @@ERROR <> 0
			goto UNDO

		SELECT @artid = @@IDENTITY

		UPDATE sysobjects SET replinfo =  replinfo |  @publish_bit
			WHERE id = (SELECT objid FROM sysarticles WHERE name = @article 
				and pubid =  @pubid)

		IF @@ERROR <> 0
			goto UNDO

		IF @filter IS NOT NULL
		BEGIN
			EXEC dbo.sp_MSsetfilterparent @filter, @tabid
			IF @@ERROR <> 0
				goto UNDO
		END

		EXEC dbo.sp_MSsetfilteredstatus @tabid
		IF @@ERROR <> 0
			goto UNDO

		/*
		** Set all bits to '1' in the columns column to include all columns.
		*/

		IF @vertical_partition = 'false'
		BEGIN
			EXECUTE @retcode  = dbo.sp_articlecolumn @publication, @article
			-- synctran
			, @refresh_synctran_procs = 0
 			, @force_invalidate_snapshot = @force_invalidate_snapshot
      
			IF @@ERROR <> 0 OR @retcode <> 0
				goto UNDO
		END
    
		/*
		** 1. Set all bits to '1' for all columns in the primary key.
		** 2. Set version column bit to 1 if the publication is synctran
		** 3. For queued replication, set all column bits to 1 that do not allow
		**		NULL or DEFAULT value
		*/
		ELSE
		BEGIN
			--
			-- for updating subscribers build a temp table
			-- to keep track of columns that are mandatory
			--
			if (@allow_sync_tran = 1 or @allow_queued_tran = 1)
			begin
