Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_instdist

  No additional text.


Syntax
create procedure sys.sp_instdist
as
begin
    set nocount on

    exec('--***************************************************************************
-- Copyright (c) 1997 - 2001 Microsoft Corporation.
-- All Rights Reserved

-- @File: instdist.sql

-- Purpose:
--  replication procedures for distribution database

-- Notes:

-- History:

--     @Version: Yukon

-- @EndHeader@

--***************************************************************************

if (    (db_id()     = 1)  -- ''master'' db
    OR
        is_srvrolemember(''sysadmin'') <> 1  -- SA
   )
    begin
    raiserror(21760,11,127)  -- State=127 should halt ISQL.EXE
    end

if (    (db_id()     = 1)  -- ''master'' db
    OR
        is_srvrolemember(''sysadmin'') <> 1 -- SA
   )
    begin
    raiserror(21761,22,127) with log   -- SeverityLevel>=19 kills spid.
    end

')
    if @@error <> 0 return 1
    exec('
EXEC dbo.sp_configure ''allow updates'', 1

')
    if @@error <> 0 return 1
    exec('reconfigure with override

')
    if @@error <> 0 return 1
    exec('
set ANSI_NULLS off

')
    if @@error <> 0 return 1
    exec('

-- Check and make sure the database has the correct compatibility level

declare @dbname sysname
		,@cmptlevelmaster tinyint
		,@cmptlevel tinyint
		
select  @dbname = db_name()
select 	@cmptlevelmaster = cmptlevel from master.dbo.sysdatabases where name = ''master''
select 	@cmptlevel = cmptlevel from master.dbo.sysdatabases where name = @dbname
if (@cmptlevel != @cmptlevelmaster)
begin
    if (@@nestlevel = 0)
    begin
	    raiserror(21762,10, 1, @dbname, @cmptlevel, @cmptlevelmaster)
	    exec dbo.sp_dbcmptlevel @dbname, @cmptlevelmaster
    end
end

')
    if @@error <> 0 return 1
    exec('
/****************************************************************************/
PRINT ''''
PRINT ''Creating distribution tables''
PRINT ''''
/****************************************************************************/
EXEC dbo.sp_MScreate_dist_tables

')
    if @@error <> 0 return 1
    exec('
/****************************************************************************/
PRINT ''''
PRINT ''Dropping all distribution stored procedures and functions that are now in resource or are obsolete''
PRINT ''''
/****************************************************************************/
IF EXISTS (SELECT * FROM sys.objects WHERE
   name = ''sp_MSadd_repl_command'' and type = ''P'')
      DROP PROCEDURE sp_MSadd_repl_command

IF EXISTS (SELECT * FROM sys.objects WHERE
   name = ''sp_MScheckretention'' and type = ''P'')
      DROP PROCEDURE sp_MScheckretention

IF EXISTS (SELECT * FROM sys.objects WHERE
   name = ''sp_MSpersistPerfNumber'' and type = ''P'')
      DROP PROCEDURE sp_MSpersistPerfNumber

IF EXISTS (SELECT * FROM sys.objects WHERE
   name = ''sp_MScheck_Jet_Subscriber'' and type = ''P'')
      DROP PROCEDURE sp_MScheck_Jet_Subscriber

IF EXISTS (SELECT * FROM sys.objects WHERE
   name = ''sp_MSadd_replcmds_mcit'' and type = ''P'')
      DROP PROCEDURE sp_MSadd_replcmds_mcit

IF EXISTS (SELECT * FROM sys.objects WHERE
   name = ''sp_MSadd_repl_commands27hp'' and type = ''P'')
      DROP PROCEDURE sp_MSadd_repl_commands27hp

IF EXISTS (SELECT * FROM sys.objects WHERE
   name = ''sp_MSrefresh_anonymous'' and type = ''P'')
      DROP PROCEDURE sp_MSrefresh_anonymous

IF EXISTS (SELECT * FROM sys.objects WHERE
   name = ''sp_MSadd_subscription'' and type = ''P'')
      DROP PROCEDURE sp_MSadd_subscription

IF EXISTS (SELECT * FROM sys.objects WHERE
   name = ''sp_MSdrop_subscription'' and type = ''P'')
      DROP PROCEDURE sp_MSdrop_subscription

if exists (select * from sys.objects where
	type = ''P'' and name = ''sp_MSfetchAdjustidentityrange'')
	drop procedure sp_MSfetchAdjustidentityrange

IF EXISTS (SELECT * FROM sys.objects WHERE
    name = ''sp_MSupdate_subscription'' and type = ''P'')
       DROP PROCEDURE sp_MSupdate_subscription

IF EXISTS (SELECT * FROM sys.objects WHERE
    name = ''sp_MSget_repl_commands'' and type = ''P'')
       DROP PROCEDURE sp_MSget_repl_commands

IF EXISTS (SELECT * FROM sys.objects WHERE
    name = ''sp_MSget_repl_cmds_anonymous'' and type = ''P'')
       DROP PROCEDURE sp_MSget_repl_cmds_anonymous

IF EXISTS (SELECT * FROM sys.objects WHERE
    name = ''sp_MSadd_anonymous_agent'' and type = ''P'')
       DROP PROCEDURE sp_MSadd_anonymous_agent

IF EXISTS (SELECT * FROM sys.objects WHERE
    name = ''sp_MSanonymous_status'' and type = ''P'')
       DROP PROCEDURE sp_MSanonymous_status

IF EXISTS (SELECT * FROM sys.objects WHERE
    name = ''sp_MSsubscription_status'' and type = ''P'')
       DROP PROCEDURE sp_MSsubscription_status

')
    if @@error <> 0 return 1
    exec('
IF EXISTS (SELECT * FROM sys.objects WHERE
   name = ''sp_MSget_last_transaction'' and type = ''P'')
      DROP PROCEDURE sp_MSget_last_transaction


IF EXISTS (SELECT * FROM sys.objects WHERE
   name = ''sp_MSadd_subscriber_info'' and type = ''P'')
      DROP PROCEDURE sp_MSadd_subscriber_info

IF EXISTS (SELECT * FROM sys.objects WHERE
   name = ''sp_MSadd_subscriber_schedule'' and type = ''P'')
      DROP PROCEDURE sp_MSadd_subscriber_schedule

IF EXISTS (SELECT * FROM sys.objects WHERE
   name = ''sp_MSupdate_subscriber_info'' and type = ''P'')
      DROP PROCEDURE sp_MSupdate_subscriber_info

IF EXISTS (SELECT * FROM sys.objects WHERE
   name = ''sp_MSupdate_subscriber_schedule'' and type = ''P'')
      DROP PROCEDURE sp_MSupdate_subscriber_schedule

IF EXISTS (SELECT * FROM sys.objects WHERE
   name = ''sp_MSdrop_subscriber_info'' and type = ''P'')
      DROP PROCEDURE sp_MSdrop_subscriber_info

IF EXISTS (SELECT * FROM sys.objects WHERE
   name = ''sp_MShelp_subscriber_info'' and type = ''P'')
      DROP PROCEDURE sp_MShelp_subscriber_info

IF EXISTS (select * from sys.objects where
   name = ''sp_MSdistribution_counters'' and type = ''P'')
      DROP PROCEDURE sp_MSdistribution_counters

IF EXISTS (select * from sys.objects where
   name = ''sp_MSset_snapshot_xact_seqno'' and type = ''P'')
      DROP PROCEDURE sp_MSset_snapshot_xact_seqno

IF EXISTS (select * from sys.objects where
   name = ''sp_MSadd_snapshot_history'' and type = ''P'')
      DROP PROCEDURE sp_MSadd_snapshot_history

IF EXISTS (select * from sys.objects where
   name = ''sp_MSadd_logreader_history'' and type = ''P'')
      DROP PROCEDURE sp_MSadd_logreader_history

IF EXISTS (select * from sys.objects where
   name = ''sp_MSadd_distribution_history'' and type = ''P'')
      DROP PROCEDURE sp_MSadd_distribution_history

IF EXISTS (select * from sys.objects where
   name = ''sp_MSreplremoveuncdir'' and type = ''P'')
      DROP PROCEDURE sp_MSreplremoveuncdir

IF EXISTS (select * from sys.objects where
   name = ''sp_MSdrop_snapshot_dirs'' and type = ''P'')
      DROP PROCEDURE sp_MSdrop_snapshot_dirs

IF EXISTS (select * from sys.objects where
   name = ''sp_MSfast_delete_trans'' and type = ''P'')
      DROP PROCEDURE sp_MSfast_delete_trans

IF EXISTS (select * from sys.objects where
   name = ''sp_MSenum_subscriptions'' and type = ''P'')
      DROP PROCEDURE sp_MSenum_subscriptions

IF EXISTS (select * from sys.objects where
   name = ''sp_MSIfExistsSubscription'' and type = ''P'')
      DROP PROCEDURE sp_MSIfExistsSubscription

IF EXISTS (select * from sys.objects where
   name = ''sp_MSenum_snapshot'' and type = ''P'')
      DROP PROCEDURE sp_MSenum_snapshot

IF EXISTS (select * from sys.objects where
   name = ''sp_MSadd_merge_anonymous_agent'' and type = ''P'')
      DROP PROCEDURE sp_MSadd_merge_anonymous_agent

IF EXISTS (select * from sys.objects where
   name = ''sp_MSenum_snapshot_s'' and type = ''P'')
      DROP PROCEDURE sp_MSenum_snapshot_s

IF EXISTS (select * from sys.objects where
   name = ''sp_MSenum_snapshot_sd'' and type = ''P'')
      DROP PROCEDURE sp_MSenum_snapshot_sd

IF EXISTS (select * from sys.objects where
   name = ''sp_MSenum_logreader'' and type = ''P'')
      DROP PROCEDURE sp_MSenum_logreader

IF EXISTS (select * from sys.objects where
   name = ''sp_MSenum_logreader_s'' and type = ''P'')
      DROP PROCEDURE sp_MSenum_logreader_s

IF EXISTS (select * from sys.objects where
   name = ''sp_MSenum_logreader_sd'' and type = ''P'')
      DROP PROCEDURE sp_MSenum_logreader_sd

IF EXISTS (select * from sys.objects where
   name = ''sp_MSenum_qreader'' and type = ''P'')
      DROP PROCEDURE sp_MSenum_qreader

IF EXISTS (select * from sys.objects where
   name = ''sp_MSenum_qreader_s'' and type = ''P'')
      DROP PROCEDURE sp_MSenum_qreader_s

IF EXISTS (select * from sys.objects where
   name = ''sp_MSenum_qreader_sd'' and type = ''P'')
      DROP PROCEDURE sp_MSenum_qreader_sd

IF EXISTS (select * from sys.objects where
   name = ''sp_MSenum_distribution'' and type = ''P'')
      DROP PROCEDURE sp_MSenum_distribution

IF EXISTS (select * from sys.objects where
   name = ''sp_MSenum_distribution_s'' and type = ''P'')
      DROP PROCEDURE sp_MSenum_distribution_s

IF EXISTS (SELECT * FROM sys.objects WHERE
   name = ''sp_MShelp_subscription_status'' and type = ''P'')
      DROP PROCEDURE sp_MShelp_subscription_status

IF EXISTS (SELECT * FROM sys.objects WHERE
   name = ''sp_MScleanup_agent_entry'' and type = ''P'')
      DROP PROCEDURE sp_MScleanup_agent_entry

IF EXISTS (select * from sys.objects where
   name = ''sp_MSenum_distribution_sd'' and type = ''P'')
      DROP PROCEDURE sp_MSenum_distribution_sd

IF EXISTS (select * from sys.objects where
   name = ''sp_MSenum_merge'' and type = ''P'')
      DROP PROCEDURE sp_MSenum_merge

IF EXISTS (select * from sys.objects where
   name = ''sp_MSenum_merge_s'' and type = ''P'')
      DROP PROCEDURE sp_MSenum_merge_s

IF EXISTS (select * from sys.objects where
   name = ''sp_MSenum_merge_sd'' and type = ''P'')
      DROP PROCEDURE sp_MSenum_merge_sd

IF EXISTS (select * from sys.objects where
   name = ''sp_MSgetagentoffloadinfo'' and type = ''P'')
      DROP PROCEDURE sp_MSgetagentoffloadinfo

IF EXISTS (select * from sys.objects where
   name = ''sp_MSenableagentoffload'' and type = ''P'')
      DROP PROCEDURE sp_MSenableagentoffload

IF EXISTS (select * from sys.objects where
   name = ''sp_MSdisableagentoffload'' and type = ''P'')
      DROP PROCEDURE sp_MSdisableagentoffload

IF EXISTS (select * from sys.objects where
   name = ''sp_MSadd_repl_error'' and type = ''P'')
      DROP PROCEDURE sp_MSadd_repl_error

IF EXISTS (select * from sys.objects where
   name = ''sp_MSadd_repl_alert'' and type = ''P'')
      DROP PROCEDURE sp_MSadd_repl_alert

IF EXISTS (select * from sys.objects where
   name = ''sp_MSadd_replmergealert'' and type = ''P'')
      DROP PROCEDURE sp_MSadd_replmergealert

IF EXISTS (select * from sys.objects where
   name = ''sp_MSget_new_errorid'' and type = ''P'')
      DROP PROCEDURE sp_MSget_new_errorid

IF EXISTS (select * from sys.objects where
   name = ''sp_MSget_repl_error'' and type = ''P'')
      DROP PROCEDURE sp_MSget_repl_error

IF EXISTS (select * from sys.objects where
   name = ''sp_MSadd_merge_history'' and type = ''P'')
      DROP PROCEDURE sp_MSadd_merge_history

IF EXISTS (select * from sys.objects where
   name = ''sp_MSdist_activate_auto_sub'' and type = ''P'')
      DROP PROCEDURE sp_MSdist_activate_auto_sub

IF EXISTS (select * from sys.objects where
   name = ''sp_MSlock_auto_sub'' and type = ''P'')
      DROP PROCEDURE sp_MSlock_auto_sub

IF EXISTS (select * from sys.objects where
   name = ''sp_MSget_new_xact_seqno'' and type = ''P'')
      DROP PROCEDURE sp_MSget_new_xact_seqno

IF EXISTS (select * from sys.objects where
   name = ''sp_MSvalidate_distpublisher'' and type = ''P'')
      DROP PROCEDURE sp_MSvalidate_distpublisher

IF EXISTS (select * from sys.objects where
   name = ''sp_MSadd_publication'' and type = ''P'')
      DROP PROCEDURE sp_MSadd_publication

IF EXISTS (select * from sys.objects where
   name = ''sp_MSchange_publication'' and type = ''P'')
      DROP PROCEDURE sp_MSchange_publication

IF EXISTS (select * from sys.objects where
   name = ''sp_MSadd_article'' and type = ''P'')
      DROP PROCEDURE sp_MSadd_article

IF EXISTS (select * from sys.objects where
   name = ''sp_MSchange_article'' and type = ''P'')
      DROP PROCEDURE sp_MSchange_article

IF EXISTS (select * from sys.objects where
   name = ''sp_MSdrop_publication'' and type = ''P'')
      DROP PROCEDURE sp_MSdrop_publication

IF EXISTS (select * from sys.objects where
   name = ''sp_MSdrop_article'' and type = ''P'')
      DROP PROCEDURE sp_MSdrop_article

IF EXISTS (select * from sys.objects where
   name = ''sp_MShelp_publication'' and type = ''P'')
      DROP PROCEDURE sp_MShelp_publication

IF EXISTS (select * from sys.objects where
   name = ''sp_MShelp_article'' and type = ''P'')
      DROP PROCEDURE sp_MShelp_article

IF EXISTS (select * from sys.objects where
   name = ''sp_MShelp_subscription'' and type = ''P'')
      DROP PROCEDURE sp_MShelp_subscription

IF EXISTS (select * from sys.objects where
   name = ''sp_MSadd_subscription_3rd'' and type = ''P'')
      DROP PROCEDURE sp_MSadd_subscription_3rd

IF EXISTS (select * from sys.objects where
   name = ''sp_MSdrop_subscription_3rd'' and type = ''P'')
      DROP PROCEDURE sp_MSdrop_subscription_3rd

IF EXISTS (select * from sys.objects where
   name = ''sp_MSactivate_subscriptions'' and type = ''P'')
      DROP PROCEDURE sp_MSactivate_subscriptions

IF EXISTS (select * from sys.objects where
   name = ''sp_MSrepl_raiserror'' and type = ''P'')
      DROP PROCEDURE sp_MSrepl_raiserror

IF EXISTS (select * from sys.objects where
   name = ''sp_MSadd_merge_subscription'' and type = ''P'')
      DROP PROCEDURE sp_MSadd_merge_subscription

IF EXISTS (select * from sys.objects where
   name = ''sp_MSdrop_merge_subscription'' and type = ''P'')
      DROP PROCEDURE sp_MSdrop_merge_subscription

IF EXISTS (select * from sys.objects where
   name = ''sp_MSenum_merge_subscriptions'' and type = ''P'')
      DROP PROCEDURE sp_MSenum_merge_subscriptions

IF EXISTS (select * from sys.objects where
   name = ''sp_MSadd_snapshot_agent'' and type = ''P'')
      DROP PROCEDURE sp_MSadd_snapshot_agent

IF EXISTS (select * from sys.objects where
   name = ''sp_MSdrop_snapshot_agent'' and type = ''P'')
      DROP PROCEDURE sp_MSdrop_snapshot_agent

IF EXISTS (select * from sys.objects where
   name = ''sp_MSadd_logreader_agent'' and type = ''P'')
      DROP PROCEDURE sp_MSadd_logreader_agent

IF EXISTS (select * from sys.objects where
   name = ''sp_MSdrop_logreader_agent'' and type = ''P'')
      DROP PROCEDURE sp_MSdrop_logreader_agent

IF EXISTS (select * from sys.objects where
   name = ''sp_MSadd_distribution_agent'' and type = ''P'')
      DROP PROCEDURE sp_MSadd_distribution_agent

IF EXISTS (select * from sys.objects where
   name = ''sp_MSdrop_distribution_agent'' and type = ''P'')
      DROP PROCEDURE sp_MSdrop_distribution_agent

IF EXISTS (select * from sys.objects where
   name = ''sp_MSdrop_distribution_agentid'' and type = ''P'')
      DROP PROCEDURE sp_MSdrop_distribution_agentid

IF EXISTS (select * from sys.objects where
   name = ''sp_MSdrop_merge_agentid'' and type = ''P'')
      DROP PROCEDURE sp_MSdrop_merge_agentid

IF EXISTS (select * from sys.objects where
   name = ''sp_MSadd_merge_agent'' and type = ''P'')
      DROP PROCEDURE sp_MSadd_merge_agent

IF EXISTS (select * from sys.objects where
   name = ''sp_MSdrop_merge_agent'' and type = ''P'')
      DROP PROCEDURE sp_MSdrop_merge_agent

IF EXISTS (select * from sys.objects where
   name = ''sp_MSadd_qreader_agent'' and type = ''P'')
      DROP PROCEDURE sp_MSadd_qreader_agent

IF EXISTS (select * from sys.objects where
   name = ''sp_MSadd_qreader_history'' and type = ''P'')
      DROP PROCEDURE sp_MSadd_qreader_history

IF EXISTS (select * from sys.objects where
   name = ''sp_MSdrop_qreader_agent'' and type = ''P'')
      DROP PROCEDURE sp_MSdrop_qreader_agent

IF EXISTS (select * from sys.objects where
   name = ''sp_MSdrop_qreader_history'' and type = ''P'')
      DROP PROCEDURE sp_MSdrop_qreader_history

IF EXISTS (select name from sys.objects where
   name = ''sp_update_agent_profile'' and type = ''P'')
    DROP PROCEDURE sp_update_agent_profile

IF EXISTS (select name from sys.objects where
   name = ''sp_MSprofile_in_use'' and type = ''P'')
    DROP PROCEDURE sp_MSprofile_in_use

IF EXISTS (select * from sys.objects where
   name = ''sp_MSreset_subscription'' and type = ''P'')
      DROP PROCEDURE sp_MSreset_subscription

IF EXISTS (select * from sys.objects where
   name = ''sp_MSget_subscription_guid'' and type = ''P'')
      DROP PROCEDURE sp_MSget_subscription_guid

IF EXISTS (select * from sys.objects where
   name = ''sp_MSreset_subscription_seqno'' and type = ''P'')
      DROP PROCEDURE sp_MSreset_subscription_seqno

IF EXISTS (select * from sys.objects where
   name = ''sp_MShelp_profile'' and type = ''P'')
      DROP PROCEDURE sp_MShelp_profile

IF EXISTS (select * from sys.objects where
   name = ''sp_MShelp_snapshot_agentid'' and type = ''P'')
      DROP PROCEDURE sp_MShelp_snapshot_agentid

IF EXISTS (select * from sys.objects where
   name = ''sp_MShelp_logreader_agentid'' and type = ''P'')
      DROP PROCEDURE sp_MShelp_logreader_agentid

IF EXISTS (select * from sys.objects where
   name = ''sp_MShelp_merge_agentid'' and type = ''P'')
      DROP PROCEDURE sp_MShelp_merge_agentid

IF EXISTS (select * from sys.objects where
   name = ''sp_MSenum_replication_status'' and type = ''P'')
      DROP PROCEDURE sp_MSenum_replication_status

IF EXISTS (select * from sys.objects where
   name = ''sp_MSagent_stethoscope'' and type = ''P'')
      DROP PROCEDURE sp_MSagent_stethoscope

IF EXISTS (select * from sys.objects where
   name = ''sp_MSlock_distribution_agent'' and type = ''P'')
      DROP PROCEDURE sp_MSlock_distribution_agent

IF EXISTS (select * from sys.objects where
   name = ''sp_MSdetect_nonlogged_shutdown'' and type = ''P'')
      DROP PROCEDURE sp_MSdetect_nonlogged_shutdown

IF EXISTS (select * from sys.objects where
   name = ''sp_MSdistpublisher_cleanup'' and type = ''P'')
      DROP PROCEDURE sp_MSdistpublisher_cleanup

IF EXISTS (select * from sys.objects where
   name = ''sp_MSenumerate_PAL'' and type = ''P'')
      DROP PROCEDURE sp_MSenumerate_PAL

IF EXISTS (select * from sys.objects where
   name = ''sp_MSpublication_access'' and type = ''P'')
      DROP PROCEDURE sp_MSpublication_access

IF EXISTS (select * from sys.objects where
   name = ''sp_MScheck_pull_access'' and type = ''P'')
      DROP PROCEDURE sp_MScheck_pull_access


')
    if @@error <> 0 return 1
    exec('IF EXISTS (select * from sys.objects where
   name = ''sp_MSdrop_6x_publication'' and type = ''P'')
      DROP PROCEDURE sp_MSdrop_6x_publication

IF EXISTS (select * from sys.objects where
   name = ''sp_MShelp_distribution_agentid'' and type = ''P'')
      DROP PROCEDURE sp_MShelp_distribution_agentid

')
    if @@error <> 0 return 1
    exec('
IF EXISTS (select * from sys.objects where
   name = ''sp_MScheck_tran_retention'' and type = ''P'')
      DROP PROCEDURE sp_MScheck_tran_retention

')
    if @@error <> 0 return 1
    exec('
IF EXISTS (select * from sys.objects where
   name = ''sp_MSreinit_subscription'' and type = ''P'')
      DROP PROCEDURE sp_MSreinit_subscription

')
    if @@error <> 0 return 1
    exec('
IF EXISTS (select * from sys.objects where
   name = ''sp_MSmarkreinit'' and type = ''P'')
      DROP PROCEDURE sp_MSmarkreinit

')
    if @@error <> 0 return 1
    exec('
IF EXISTS (select * from sys.objects where
   name = ''sp_MSbrowsesnapshotfolder'' and type = ''P'')
      DROP PROCEDURE sp_MSbrowsesnapshotfolder

')
    if @@error <> 0 return 1
    exec('
IF EXISTS (select * from sys.objects where
   name = ''sp_MSquery_syncstates'' and type = ''P'')
      DROP PROCEDURE sp_MSquery_syncstates

')
    if @@error <> 0 return 1
    exec('
IF EXISTS (select * from sys.objects where
   name = ''sp_MSdist_adjust_identity'' and type = ''P'')
      DROP PROCEDURE sp_MSdist_adjust_identity

IF EXISTS (select * from sys.objects where
   name = ''sp_MSchange_subscription_dts_info'' and type = ''P'')
      DROP PROCEDURE sp_MSchange_subscription_dts_info

IF EXISTS (select * from sys.objects where
   name = ''sp_MSget_subscription_dts_info'' and type = ''P'')
      DROP PROCEDURE sp_MSget_subscription_dts_info

')
    if @@error <> 0 return 1
    exec('
IF EXISTS (select * from sys.objects where
   name = ''sp_MSenumdistributionagentproperties'' and type = ''P'')
      DROP PROCEDURE sp_MSenumdistributionagentproperties

IF EXISTS (select * from sys.objects where
   name = ''sp_MSenum_merge_agent_properties'' and type = ''P'')
      DROP PROCEDURE sp_MSenum_merge_agent_properties

IF EXISTS (select * from sys.objects where
   name = ''sp_MSinsert_identity'' and type = ''P'')
      DROP PROCEDURE sp_MSinsert_identity

IF EXISTS (select * from sys.objects where
   name = ''sp_MSadjust_pub_identity'' and type = ''P'')
      DROP PROCEDURE sp_MSadjust_pub_identity

IF EXISTS (select * from sys.objects where
   name = ''sp_MScheck_pub_identity'' and type = ''P'')
      DROP PROCEDURE sp_MScheck_pub_identity

IF EXISTS (select * from sys.objects where
   name = ''sp_dropanonymoussubscription'' and type = ''P'')
      DROP PROCEDURE sp_dropanonymoussubscription

IF EXISTS (select * from sys.objects where
   name = ''sp_MSdrop_anonymous_entry'' and type = ''P'')
      DROP PROCEDURE sp_MSdrop_anonymous_entry

IF EXISTS (select * from sys.objects where
   name = ''sp_MSadddynamicsnapshotjobatdistributor'' and type = ''P'')
      DROP PROCEDURE sp_MSadddynamicsnapshotjobatdistributor

IF EXISTS (select * from sys.objects where
   name = ''sp_MSdeleterepljob'' and type = ''P'')
      DROP PROCEDURE sp_MSdeleterepljob

IF EXISTS (select * from sys.objects where
   name = ''sp_MSdeletefoldercontents'' and type = ''P'')
      DROP PROCEDURE sp_MSdeletefoldercontents

IF EXISTS (select * from sys.objects where
   name = ''sp_MSinvalidate_snapshot'' and type = ''P'')
      DROP PROCEDURE sp_MSinvalidate_snapshot

IF EXISTS (select * from sys.objects where
   name = ''sp_MSrepl_init_backup_lsns'' and type = ''P'')
      DROP PROCEDURE sp_MSrepl_init_backup_lsns

IF EXISTS (select * from sys.objects where
   name = ''sp_MSispublicationqueued'' and type = ''P'')
      DROP PROCEDURE sp_MSispublicationqueued

if exists (select * from sys.objects
        where type = ''P'' and
        name = ''sp_MSwritemergeperfcounter'')
        drop procedure sp_MSwritemergeperfcounter

if exists (select * from sys.objects
        where type = ''P'' and
        name = ''sp_browseagentcmds'')
        drop procedure sp_browseagentcmds

IF EXISTS (SELECT * FROM sys.objects WHERE
    name = ''sp_MSsetupnosyncsubwithlsnatdist'' and type = ''P'')
        DROP PROCEDURE sp_MSsetupnosyncsubwithlsnatdist

')
    if @@error <> 0 return 1
    exec('
if object_id(''dbo.sp_MSadd_tracer_history'', ''local'') is not null
	drop procedure dbo.sp_MSadd_tracer_history

')
    if @@error <> 0 return 1
    exec('if object_id(''dbo.sp_MSupdate_subscriber_tracer_history'', ''local'') is not null
	drop procedure dbo.sp_MSupdate_subscriber_tracer_history

')
    if @@error <> 0 return 1
    exec('if object_id(''dbo.sp_MSupdate_tracer_history'', ''local'') is not null
	drop procedure dbo.sp_MSupdate_tracer_history

')
    if @@error <> 0 return 1
    exec('if object_id(''dbo.sp_MSdelete_tracer_history'', ''local'') is not null
	drop procedure dbo.sp_MSdelete_tracer_history

if object_id(''dbo.sp_MSislogreaderjobnamegenerated'', ''local'') is not null
    drop procedure dbo.sp_MSislogreaderjobnamegenerated

if object_id(''dbo.sp_MSisqueuereaderjobnamegenerated'', ''local'') is not null
    drop procedure dbo.sp_MSisqueuereaderjobnamegenerated

if object_id(''dbo.sp_MSissnapshotjobnamegenerated'', ''local'') is not null
    drop procedure dbo.sp_MSissnapshotjobnamegenerated

if object_id(''dbo.sp_MSisdistributionjobnamegenerated'', ''local'') is not null
    drop procedure dbo.sp_MSisdistributionjobnamegenerated

if object_id(''dbo.sp_MSismergejobnamegenerated'', ''local'') is not null
    drop procedure dbo.sp_MSismergejobnamegenerated

if object_id(N''dbo.sp_MSget_undelivered_commands'', ''local'') is not null
    drop procedure dbo.sp_MSget_undelivered_commands

if object_id(N''dbo.sp_MSget_anonymous_cmds'', ''local'') is not null
    drop procedure dbo.sp_MSget_anonymous_cmds

if object_id(N''dbo.sp_MSget_loopback_cmds'', ''local'') is not null
    drop procedure dbo.sp_MSget_loopback_cmds

if object_id(N''dbo.sp_browsereplcmds'', ''local'') is not null
    drop procedure dbo.sp_browsereplcmds

if object_id(N''dbo.sp_dumpparamcmd'', ''local'') is not null
    drop procedure dbo.sp_dumpparamcmd

if object_id(N''dbo.sp_MSadd_repl_commands27hp_mcit'', ''local'') is not null
    drop procedure dbo.sp_MSadd_repl_commands27hp_mcit

if object_id(N''dbo.sp_MSadd_repl_commands27hp6x'', ''local'') is not null
    drop procedure dbo.sp_MSadd_repl_commands27hp6x

')
    if @@error <> 0 return 1
    exec('
/****************************************************************************/
PRINT ''''
PRINT ''Dropping all distribution stored procedures and functions that are created locally''
PRINT ''''
/****************************************************************************/

if object_id(N''dbo.sp_MSadd_replcmds'', ''local'') is not null
	drop procedure dbo.sp_MSadd_replcmds

if object_id(N''dbo.sp_MSadd_repl_commands27'', ''local'') is not null
	drop procedure dbo.sp_MSadd_repl_commands27

if object_id(N''dbo.sp_MSremove_published_jobs'', ''local'') is not null
	drop procedure dbo.sp_MSremove_published_jobs

if object_id(N''dbo.sp_MSdistribution_cleanup'', ''local'') is not null
	drop procedure dbo.sp_MSdistribution_cleanup

if object_id(N''dbo.sp_MSsubscription_cleanup'', ''local'') is not null
	drop procedure dbo.sp_MSsubscription_cleanup

if object_id(N''dbo.sp_MSdistribution_delete'', ''local'') is not null
	drop procedure dbo.sp_MSdistribution_delete

if object_id(N''dbo.sp_MSmaximum_cleanup_seqno'', ''local'') is not null
	drop procedure dbo.sp_MSmaximum_cleanup_seqno

if object_id(N''dbo.sp_MSdelete_dodelete'', ''local'') is not null
	drop procedure dbo.sp_MSdelete_dodelete

if object_id(N''dbo.sp_MSdelete_publisherdb_trans'', ''local'') is not null
	drop procedure dbo.sp_MSdelete_publisherdb_trans

if object_id(N''dbo.sp_MShistory_cleanup'', ''local'') is not null
	drop procedure dbo.sp_MShistory_cleanup

if object_id(N''dbo.sp_MSget_repl_version'', ''local'') is not null
	drop procedure dbo.sp_MSget_repl_version

if object_id(N''dbo.sp_MSset_syncstate'', ''local'') is not null
	drop procedure dbo.sp_MSset_syncstate

if object_id(N''dbo.fn_MSmask_agent_type'', ''local'') is not null
	drop function dbo.fn_MSmask_agent_type

if object_id(N''dbo.sp_MSlog_agent_cancel'', ''local'') is not null
	drop procedure dbo.sp_MSlog_agent_cancel


')
    if @@error <> 0 return 1
    exec('

-- Name: fn_MSmask_agent_type

-- Descriptions:
-- This function is used internally by other stored procedures to mark the agent type.
-- Only distribution agents and merge agents should use this function.
-- BUGBUG: Is this obsolete ?

-- Parameters: as defined in create statement

-- Returns: int

-- Security: Not public (db owner chaining)

raiserror(15339,-1,-1,''fn_MSmask_agent_type'')

')
    if @@error <> 0 return 1
    exec('create function dbo.fn_MSmask_agent_type(
    @agent_id int,
	@agent_type int
    ) returns int
as
begin
	declare @anonymous_mask int
	select @anonymous_mask = 0x80000000
	if @agent_type = 3 -- If dist agent
	begin
		if exists (select * from MSdistribution_agents where id = @agent_id and
			subscriber_name is not null)
			select @agent_type = 3 | @anonymous_mask
		else
			select @agent_type = 3
	end
	else if @agent_type = 4 -- if merge agent
	begin
		if exists (select * from dbo.MSmerge_agents where id = @agent_id and
			anonymous_subid is not null)
			select @agent_type = 4 | @anonymous_mask
		else
			select @agent_type = 4
	end
	-- if other agents, @agent_type will not change.
	return @agent_type
end

')
    if @@error <> 0 return 1
    exec('

-- Name: sp_MSset_syncstate

-- Descriptions:

-- Parameters: as defined in create statement

-- Returns: 0 - success
--          1 - Otherwise

-- Security: Not public (db owner chaining)

raiserror(15339,-1,-1,''sp_MSset_syncstate'')

')
    if @@error <> 0 return 1
    exec('create procedure sp_MSset_syncstate
@publisher_id smallint,
@publisher_db sysname,
@article_id int,
@sync_state int,
@xact_seqno varbinary(16)
as
set nocount on
declare @publication_id int

select top 1 @publication_id = s.publication_id
from MSsubscriptions s
where
s.publisher_id = @publisher_id and
s.publisher_db = @publisher_db and
s.article_id = @article_id     and
s.subscription_seqno < @xact_seqno


if @publication_id is not null
begin
	if( @sync_state = 1 )
	begin
		if not exists( select * from MSsync_states
		               where publisher_id = @publisher_id and
					   publisher_db = @publisher_db and
					   publication_id = @publication_id )
		begin
			insert into MSsync_states( publisher_id, publisher_db, publication_id )
			values( @publisher_id, @publisher_db, @publication_id )
		end
	end
	else if @sync_state = 0
	begin
		
		delete MSsync_states
		where
		publisher_id = @publisher_id and
		publisher_db = @publisher_db and
		publication_id = @publication_id

		-- activate the subscription(s) so the distribution agent can start processing
		declare @automatic int
		declare @active int	
		declare @initiated int

		select @automatic = 1
		select @active = 2
		select @initiated = 3

		-- set status to active, ss_cplt_seqno = commit LSN of xact containing
		-- syncdone token.
		
		-- VERY IMPORTANT:  We can only do this because we know that the publisher
		-- tables are locked in the same transaction that writes the SYNCDONE token.
		-- If the tables were NOT locked, we could get into a situation where data
		-- in the table was changed and committed between the time the SYNCDONE token was
		-- written and the time the SYNCDONE xact was committed.  This would cause the
		-- logreader to replicate the xact with no compensation records, but the advance
		-- of the ss_cplt_seqno would cause the dist to skip that command since only commands
		-- with the snapshot bit set will be processed if they are <= ss_cplt_seqno.
		
		update MSsubscriptions
		set status = @active,
			subscription_time = getdate(),
			ss_cplt_seqno = @xact_seqno		
		where
			publisher_id = @publisher_id and
			publisher_db = @publisher_db and
			publication_id = @publication_id and
			sync_type = @automatic and
			status in( @initiated ) and
			ss_cplt_seqno <= @xact_seqno	
	end
end

')
    if @@error <> 0 return 1
    exec('

-- Name: sp_MSadd_repl_commands27

-- Descriptions: this procedure is used by replication agent
-- to directly insert commands in distribution queue.

-- Parameters: as defined in create statement

-- Returns: 0 - success
--          1 - Otherwise

-- Security: Not public (db owner chaining)

raiserror(15339,-1,-1,''sp_MSadd_repl_commands27'')

')
    if @@error <> 0 return 1
    exec('CREATE PROCEDURE sp_MSadd_repl_commands27
(
@publisher_id smallint,
@publisher_db sysname,
@xact_id varbinary(16) = 0x0,
@xact_seqno varbinary(16) = 0x0,
@originator sysname,
@originator_db sysname,
@article_id int,
@command_id int,
@type int = 0,
@partial_command bit,
@command varbinary(1024),

@1xact_id varbinary(16) = 0x0,

@1xact_seqno varbinary(16) = 0x0,
@1originator sysname = NULL,
@1originator_db sysname = NULL,
@1article_id int = 0,
@1command_id int = 0,
@1type int = 0,
@1partial_command bit = 0,
@1command varbinary(1024) = NULL,

@2xact_id varbinary(16) = 0x0,

@2xact_seqno varbinary(16) = 0x0,
@2originator sysname = NULL,
@2originator_db sysname = NULL,
@2article_id int = 0,
@2command_id int = 0,
@2type int = 0,
@2partial_command bit = 0,
@2command varbinary(1024) = NULL,

@3xact_id varbinary(16) = 0x0,

@3xact_seqno varbinary(16) = 0x0,
@3originator sysname = NULL,
@3originator_db sysname = NULL,
@3article_id int = 0,
@3command_id int = 0,
@3type int = 0,
@3partial_command bit = 0,
@3command varbinary(1024) = NULL,

@4xact_id varbinary(16) = 0x0,

@4xact_seqno varbinary(16) = 0x0,
@4originator sysname = NULL,
@4originator_db sysname = NULL,
@4article_id int = 0,
@4command_id int = 0,
@4type int = 0,
@4partial_command bit = 0,
@4command varbinary(1024) = NULL,

@5xact_id varbinary(16) = 0x0,

@5xact_seqno varbinary(16) = 0x0,
@5originator sysname = NULL,
@5originator_db sysname = NULL,
@5article_id int = 0,
@5command_id int = 0,
@5type int = 0,
@5partial_command bit = 0,
@5command varbinary(1024) = NULL,

@6xact_id varbinary(16) = 0x0,

@6xact_seqno varbinary(16) = 0x0,
@6originator sysname = NULL,
@6originator_db sysname = NULL,
@6article_id int = 0,
@6command_id int = 0,
@6type int = 0,
@6partial_command bit = 0,
@6command varbinary(1024) = NULL,

@7xact_id varbinary(16) = 0x0,

@7xact_seqno varbinary(16) = 0x0,
@7originator sysname = NULL,
@7originator_db sysname = NULL,
@7article_id int = 0,
@7command_id int = 0,
@7type int = 0,
@7partial_command bit = 0,
@7command varbinary(1024) = NULL,

@8xact_id varbinary(16) = 0x0,

@8xact_seqno varbinary(16) = 0x0,
@8originator sysname = NULL,
@8originator_db sysname = NULL,
@8article_id int = 0,
@8command_id int = 0,
@8type int = 0,
@8partial_command bit = 0,
@8command varbinary(1024) = NULL,

@9xact_id varbinary(16) = 0x0,

@9xact_seqno varbinary(16) = 0x0,
@9originator sysname = NULL,
@9originator_db sysname = NULL,
@9article_id int = 0,
@9command_id int = 0,
@9type int = 0,
@9partial_command bit = 0,
@9command varbinary(1024) = NULL,

@10xact_id varbinary(16) = 0x0,

@10xact_seqno varbinary(16) = 0x0,
@10originator sysname = NULL,
@10originator_db sysname = NULL,
@10article_id int = 0,
@10command_id int = 0,
@10type int = 0,
@10partial_command bit = 0,
@10command varbinary(1024) = NULL,

@11xact_id varbinary(16) = 0x0,

@11xact_seqno varbinary(16) = 0x0,
@11originator sysname = NULL,
@11originator_db sysname = NULL,
@11article_id int = 0,
@11command_id int = 0,
@11type int = 0,
@11partial_command bit = 0,
@11command varbinary(1024) = NULL,

@12xact_id varbinary(16) = 0x0,

@12xact_seqno varbinary(16) = 0x0,
@12originator sysname = NULL,
@12originator_db sysname = NULL,
@12article_id int = 0,
@12command_id int = 0,
@12type int = 0,
@12partial_command bit = 0,
@12command varbinary(1024) = NULL,

@13xact_id varbinary(16) = 0x0,

@13xact_seqno varbinary(16) = 0x0,
@13originator sysname = NULL,
@13originator_db sysname = NULL,
@13article_id int = 0,
@13command_id int = 0,
@13type int = 0,
@13partial_command bit = 0,
@13command varbinary(1024) = NULL,

@14xact_id varbinary(16) = 0x0,

@14xact_seqno varbinary(16) = 0x0,
@14originator sysname = NULL,
@14originator_db sysname = NULL,
@14article_id int = 0,
@14command_id int = 0,
@14type int = 0,
@14partial_command bit = 0,
@14command varbinary(1024) = NULL,

@15xact_id varbinary(16) = 0x0,

@15xact_seqno varbinary(16) = 0x0,
@15originator sysname = NULL,
@15originator_db sysname = NULL,
@15article_id int = 0,
@15command_id int = 0,
@15type int = 0,
@15partial_command bit = 0,
@15command varbinary(1024) = NULL,

@16xact_id varbinary(16) = 0x0,

@16xact_seqno varbinary(16) = 0x0,
@16originator sysname = NULL,
@16originator_db sysname = NULL,
@16article_id int = 0,
@16command_id int = 0,
@16type int = 0,
@16partial_command bit = 0,
@16command varbinary(1024) = NULL,

@17xact_id varbinary(16) = 0x0,

@17xact_seqno varbinary(16) = 0x0,
@17originator sysname = NULL,
@17originator_db sysname = NULL,
@17article_id int = 0,
@17command_id int = 0,
@17type int = 0,
@17partial_command bit = 0,
@17command varbinary(1024) = NULL,

@18xact_id varbinary(16) = 0x0,

@18xact_seqno varbinary(16) = 0x0,
@18originator sysname = NULL,
@18originator_db sysname = NULL,
@18article_id int = 0,
@18command_id int = 0,
@18type int = 0,
@18partial_command bit = 0,
@18command varbinary(1024) = NULL,

@19xact_id varbinary(16) = 0x0,

@19xact_seqno varbinary(16) = 0x0,
@19originator sysname = NULL,
@19originator_db sysname = NULL,
@19article_id int = 0,
@19command_id int = 0,
@19type int = 0,
@19partial_command bit = 0,
@19command varbinary(1024) = NULL,

@20xact_id varbinary(16) = 0x0,

@20xact_seqno varbinary(16) = 0x0,
@20originator sysname = NULL,
@20originator_db sysname = NULL,
@20article_id int = 0,
@20command_id int = 0,
@20type int = 0,
@20partial_command bit = 0,
@20command varbinary(1024) = NULL,

@21xact_id varbinary(16) = 0x0,

@21xact_seqno varbinary(16) = 0x0,
@21originator sysname = NULL,
@21originator_db sysname = NULL,
@21article_id int = 0,
@21command_id int = 0,
@21type int = 0,
@21partial_command bit = 0,
@21command varbinary(1024) = NULL,

@22xact_id varbinary(16) = 0x0,

@22xact_seqno varbinary(16) = 0x0,
@22originator sysname = NULL,
@22originator_db sysname = NULL,
@22article_id int = 0,
@22command_id int = 0,
@22type int = 0,
@22partial_command bit = 0,
@22command varbinary(1024) = NULL,

@23xact_id varbinary(16) = 0x0,

@23xact_seqno varbinary(16) = 0x0,
@23originator sysname = NULL,
@23originator_db sysname = NULL,
@23article_id int = 0,
@23command_id int = 0,
@23type int = 0,
@23partial_command bit = 0,
@23command varbinary(1024) = NULL,

@24xact_id varbinary(16) = 0x0,

@24xact_seqno varbinary(16) = 0x0,
@24originator sysname = NULL,
@24originator_db sysname = NULL,
@24article_id int = 0,
@24command_id int = 0,
@24type int = 0,
@24partial_command bit = 0,
@24command varbinary(1024) = NULL,

@25xact_id varbinary(16) = 0x0,

@25xact_seqno varbinary(16) = 0x0,
@25originator sysname = NULL,
@25originator_db sysname = NULL,
@25article_id int = 0,
@25command_id int = 0,
@25type int = 0,
@25partial_command bit = 0,
@25command varbinary(1024) = NULL,

@26xact_id varbinary(16) = 0x0,

@26xact_seqno varbinary(16) = 0x0,
@26originator sysname = NULL,
@26originator_db sysname = NULL,
@26article_id int = 0,
@26command_id int = 0,
@26type int = 0,
@26partial_command bit = 0,
@26command varbinary(1024) = NULL
)
AS
begin
    SET NOCOUNT ON

    DECLARE @publisher_database_id int
    DECLARE @date datetime
    declare @originator_id int
	declare @syncstat int

    SELECT @date = GETDATE()

    -- Get publisher database id.
    SELECT @publisher_database_id = id from MSpublisher_databases where publisher_id = @publisher_id and
        publisher_db = @publisher_db

    -- First insert into MS_repl_transactions
    IF @command_id = 1
      INSERT INTO MSrepl_transactions VALUES (@publisher_database_id,
         @xact_id,  @xact_seqno, @date)

    IF @1xact_id = 0x0
      goto INSERT_CMDS
    IF @1command_id = 1
      INSERT INTO MSrepl_transactions VALUES (@publisher_database_id,
         @1xact_id,  @1xact_seqno, @date)

    IF @2xact_id = 0x0
      goto INSERT_CMDS
    IF @2command_id = 1
      INSERT INTO MSrepl_transactions VALUES (@publisher_database_id,
         @2xact_id,  @2xact_seqno, @date)

    IF @3xact_id = 0x0
      goto INSERT_CMDS
    IF @3command_id = 1
      INSERT INTO MSrepl_transactions VALUES (@publisher_database_id,
         @3xact_id,  @3xact_seqno, @date)

    IF @4xact_id = 0x0
      goto INSERT_CMDS
    IF @4command_id = 1
      INSERT INTO MSrepl_transactions VALUES (@publisher_database_id,
        @4xact_id,  @4xact_seqno, @date)

    IF @5xact_id = 0x0
      goto INSERT_CMDS

    IF @5command_id = 1
      INSERT INTO MSrepl_transactions VALUES (@publisher_database_id,
         @5xact_id,  @5xact_seqno, @date)

    IF @6xact_id = 0x0
      goto INSERT_CMDS
    IF @6command_id = 1
      INSERT INTO MSrepl_transactions VALUES (@publisher_database_id,
         @6xact_id,  @6xact_seqno, @date)

    IF @7xact_id = 0x0
      goto INSERT_CMDS
    IF @7command_id = 1
      INSERT INTO MSrepl_transactions VALUES (@publisher_database_id,
         @7xact_id,  @7xact_seqno, @date)

    IF @8xact_id = 0x0
      goto INSERT_CMDS
    IF @8command_id = 1
      INSERT INTO MSrepl_transactions VALUES (@publisher_database_id,
         @8xact_id,  @8xact_seqno, @date)

    IF @9xact_id = 0x0
      goto INSERT_CMDS
    IF @9command_id = 1
      INSERT INTO MSrepl_transactions VALUES (@publisher_database_id,
         @9xact_id,  @9xact_seqno, @date)

    IF @10xact_id = 0x0
      goto INSERT_CMDS
    IF @10command_id = 1
      INSERT INTO MSrepl_transactions VALUES (@publisher_database_id,
         @10xact_id,  @10xact_seqno, @date)

    IF @11xact_id = 0x0
      goto INSERT_CMDS
    IF @11command_id = 1
      INSERT INTO MSrepl_transactions VALUES (@publisher_database_id,
         @11xact_id,  @11xact_seqno, @date)

    IF @12xact_id = 0x0
      goto INSERT_CMDS
    IF @12command_id = 1
      INSERT INTO MSrepl_transactions VALUES (@publisher_database_id,
         @12xact_id,  @12xact_seqno, @date)

    IF @13xact_id = 0x0
      goto INSERT_CMDS
    IF @13command_id = 1
      INSERT INTO MSrepl_transactions VALUES (@publisher_database_id,
         @13xact_id,  @13xact_seqno, @date)

    IF @14xact_id = 0x0
      goto INSERT_CMDS
    IF @14command_id = 1
      INSERT INTO MSrepl_transactions VALUES (@publisher_database_id,
         @14xact_id,  @14xact_seqno, @date)

    IF @15xact_id = 0x0
      goto INSERT_CMDS
    IF @15command_id = 1
      INSERT INTO MSrepl_transactions VALUES (@publisher_database_id,
         @15xact_id,  @15xact_seqno, @date)

    IF @16xact_id = 0x0
      goto INSERT_CMDS
    IF @16command_id = 1
      INSERT INTO MSrepl_transactions VALUES (@publisher_database_id,
         @16xact_id,  @16xact_seqno, @date)

    IF @17xact_id = 0x0
      goto INSERT_CMDS
    IF @17command_id = 1
      INSERT INTO MSrepl_transactions VALUES (@publisher_database_id,
         @17xact_id,  @17xact_seqno, @date)

    IF @18xact_id = 0x0
      goto INSERT_CMDS
    IF @18command_id = 1
      INSERT INTO MSrepl_transactions VALUES (@publisher_database_id,
         @18xact_id,  @18xact_seqno, @date)

    IF @19xact_id = 0x0
      goto INSERT_CMDS
    IF @19command_id = 1
      INSERT INTO MSrepl_transactions VALUES (@publisher_database_id,
         @19xact_id,  @19xact_seqno, @date)

    IF @20xact_id = 0x0
      goto INSERT_CMDS
    IF @20command_id = 1
      INSERT INTO MSrepl_transactions VALUES (@publisher_database_id,
         @20xact_id,  @20xact_seqno, @date)

    IF @21xact_id = 0x0
      goto INSERT_CMDS
    IF @21command_id = 1
      INSERT INTO MSrepl_transactions VALUES (@publisher_database_id,
         @21xact_id,  @21xact_seqno, @date)

    IF @22xact_id = 0x0
      goto INSERT_CMDS
    IF @22command_id = 1
      INSERT INTO MSrepl_transactions VALUES (@publisher_database_id,
         @22xact_id,  @22xact_seqno, @date)

    IF @23xact_id = 0x0
      goto INSERT_CMDS
    IF @23command_id = 1
      INSERT INTO MSrepl_transactions VALUES (@publisher_database_id,
         @23xact_id,  @23xact_seqno, @date)

    IF @24xact_id = 0x0
      goto INSERT_CMDS
    IF @24command_id = 1
      INSERT INTO MSrepl_transactions VALUES (@publisher_database_id,
         @24xact_id,  @24xact_seqno, @date)

    IF @25xact_id = 0x0
      goto INSERT_CMDS
    IF @25command_id = 1
      INSERT INTO MSrepl_transactions VALUES (@publisher_database_id,
         @25xact_id,  @25xact_seqno, @date)

    IF @26xact_id = 0x0
      goto INSERT_CMDS
    IF @26command_id = 1
      INSERT INTO MSrepl_transactions VALUES (@publisher_database_id,
         @26xact_id,  @26xact_seqno, @date)

INSERT_CMDS:

    -- Get the originator_id for the first command
    if @originator <> N'''' and @originator_db <> N'''' and @originator is not null and @originator_db is not null
    begin
        set @originator_id = null select @originator_id = id from MSrepl_originators where
            publisher_database_id = @publisher_database_id and UPPER(srvname) = UPPER(@originator) and
            dbname = @originator_db
        if @originator_id is null
        begin
            insert into MSrepl_originators (publisher_database_id, srvname, dbname) values
                (@publisher_database_id, @originator, @originator_db)
            select @originator_id = @@identity
        end
    end
    else
        select @originator_id = 0

    -- Now insert into MSrepl_commands
    IF @command IS NOT NULL
	begin
		if( @type in( 37,38 ) )
		begin
		  select @syncstat = 38 - @type
		  exec sp_MSset_syncstate @publisher_id, @publisher_db, @article_id, @syncstat, @xact_seqno
		end

        INSERT INTO MSrepl_commands (publisher_database_id, xact_seqno, type, article_id, originator_id, command_id, partial_command, command)
		VALUES (@publisher_database_id,
            @xact_seqno,@type, @article_id,
            @originator_id,
            @command_id, @partial_command, @command)
	end

    IF @1xact_id = 0x0
      return

    IF @1command IS NOT NULL
    begin
            if @1originator <> N'''' and @1originator_db <> N'''' and @1originator is not null and @1originator_db is not null
            begin
                set @originator_id = null select @originator_id = id from MSrepl_originators where
                    publisher_database_id = @publisher_database_id and UPPER(srvname) = UPPER(@1originator) and
                    dbname = @1originator_db
                if @originator_id is null
                begin
                    insert into MSrepl_originators (publisher_database_id, srvname, dbname) values
                        (@publisher_database_id, @1originator, @1originator_db)
                    select @originator_id = @@identity
                end
            end
            else
                select @originator_id = 0

		if( @type in( 37,38 ) )
		begin
		  select @syncstat = 38 - @1type
		  exec sp_MSset_syncstate @publisher_id, @publisher_db, @1article_id, @syncstat, @1xact_seqno
		end

        INSERT INTO MSrepl_commands (publisher_database_id, xact_seqno, type, article_id, originator_id, command_id, partial_command, command)
		VALUES (@publisher_database_id,
            @1xact_seqno,@1type, @1article_id,
            @originator_id,
            @1command_id, @1partial_command, @1command)
    end

    IF @2xact_id = 0x0
      return
    IF @2command IS NOT NULL
    begin
            if @2originator <> N'''' and @2originator_db <> N'''' and @2originator is not null and @2originator_db is not null
            begin
                set @originator_id = null select @originator_id = id from MSrepl_originators where
                    publisher_database_id = @publisher_database_id and UPPER(srvname) = UPPER(@2originator) and
                    dbname = @2originator_db
                if @originator_id is null
                begin
                    insert into MSrepl_originators (publisher_database_id, srvname, dbname) values
                        (@publisher_database_id, @2originator, @2originator_db)
                    select @originator_id = @@identity
                end
            end
            else
                select @originator_id = 0

		if( @type in( 37,38 ) )
		begin
		  select @syncstat = 38 - @2type
		  exec sp_MSset_syncstate @publisher_id, @publisher_db, @2article_id, @syncstat, @2xact_seqno
		end

        INSERT INTO MSrepl_commands (publisher_database_id, xact_seqno, type, article_id, originator_id, command_id, partial_command, command)
		VALUES (@publisher_database_id,
            @2xact_seqno,@2type, @2article_id,
            @originator_id,
            @2command_id, @2partial_command, @2command)
    end

    IF @3xact_id = 0x0
      return
    IF @3command IS NOT NULL
    begin
            if @3originator <> N'''' and @3originator_db <> N'''' and @3originator is not null and @3originator_db is not null
            begin
                set @originator_id = null select @originator_id = id from MSrepl_originators where
                    publisher_database_id = @publisher_database_id and UPPER(srvname) = UPPER(@3originator) and
                    dbname = @3originator_db
                if @originator_id is null
                begin
                    insert into MSrepl_originators (publisher_database_id, srvname, dbname) values
                        (@publisher_database_id, @3originator, @3originator_db)
                    select @originator_id = @@identity
                end
            end
            else
                select @originator_id = 0

		if( @type in( 37,38 ) )
		begin
		  select @syncstat = 38 - @3type
		  exec sp_MSset_syncstate @publisher_id, @publisher_db, @3article_id, @syncstat, @3xact_seqno
		end

        INSERT INTO MSrepl_commands (publisher_database_id, xact_seqno, type, article_id, originator_id, command_id, partial_command, command)
		VALUES (@publisher_database_id,
            @3xact_seqno,@3type, @3article_id,
            @originator_id,
            @3command_id, @3partial_command, @3command)
    end

    IF @4xact_id = 0x0
      return
    IF @4command IS NOT NULL
    begin
            if @4originator <> N'''' and @4originator_db <> N'''' and @4originator is not null and @4originator_db is not null
            begin
                set @originator_id = null select @originator_id = id from MSrepl_originators where
                    publisher_database_id = @publisher_database_id and UPPER(srvname) = UPPER(@4originator) and
                    dbname = @4originator_db
                if @originator_id is null
                begin
                    insert into MSrepl_originators (publisher_database_id, srvname, dbname) values
                        (@publisher_database_id, @4originator, @4originator_db)
                    select @originator_id = @@identity
                end
            end
            else
                select @originator_id = 0

		if( @type in( 37,38 ) )
		begin
		  select @syncstat = 38 - @4type
		  exec sp_MSset_syncstate @publisher_id, @publisher_db, @4article_id, @syncstat, @4xact_seqno
		end

        INSERT INTO MSrepl_commands (publisher_database_id, xact_seqno, type, article_id, originator_id, command_id, partial_command, command)
		VALUES (@publisher_database_id,
            @4xact_seqno,@4type, @4article_id,
            @originator_id,
            @4command_id, @4partial_command, @4command)
    end

    IF @5xact_id = 0x0
      return
    IF @5command IS NOT NULL
    begin
            if @5originator <> N'''' and @5originator_db <> N'''' and @5originator is not null and @5originator_db is not null
            begin
                set @originator_id = null select @originator_id = id from MSrepl_originators where
                    publisher_database_id = @publisher_database_id and UPPER(srvname) = UPPER(@5originator) and
                    dbname = @5originator_db
                if @originator_id is null
                begin
                    insert into MSrepl_originators (publisher_database_id, srvname, dbname) values
                        (@publisher_database_id, @5originator, @5originator_db)
                    select @originator_id = @@identity
                end
            end
            else
                select @originator_id = 0

		if( @type in( 37,38 ) )
		begin
		  select @syncstat = 38 - @5type
		  exec sp_MSset_syncstate @publisher_id, @publisher_db, @5article_id, @syncstat, @5xact_seqno
		end

        INSERT INTO MSrepl_commands (publisher_database_id, xact_seqno, type, article_id, originator_id, command_id, partial_command, command)
		VALUES (@publisher_database_id,
            @5xact_seqno,@5type, @5article_id,
            @originator_id,
            @5command_id, @5partial_command, @5command)
    end

    IF @6xact_id = 0x0
      return
    IF @6command IS NOT NULL
    begin
            if @6originator <> N'''' and @6originator_db <> N'''' and @6originator is not null and @6originator_db is not null
            begin
                set @originator_id = null select @originator_id = id from MSrepl_originators where
                    publisher_database_id = @publisher_database_id and UPPER(srvname) = UPPER(@6originator) and
                    dbname = @6originator_db
                if @originator_id is null
                begin
                    insert into MSrepl_originators (publisher_database_id, srvname, dbname) values
                        (@publisher_database_id, @6originator, @6originator_db)
                    select @originator_id = @@identity
                end
            end
            else
                select @originator_id = 0

		if( @type in( 37,38 ) )
		begin
		  select @syncstat = 38 - @6type
		  exec sp_MSset_syncstate @publisher_id, @publisher_db, @6article_id, @syncstat, @6xact_seqno
		end

        INSERT INTO MSrepl_commands (publisher_database_id, xact_seqno, type, article_id, originator_id, command_id, partial_command, command)
		VALUES (@publisher_database_id,
            @6xact_seqno,@6type, @6article_id,
            @originator_id,
            @6command_id, @6partial_command, @6command)
    end

    IF @7xact_id = 0x0
      return
    IF @7command IS NOT NULL
    begin
            if @7originator <> N'''' and @7originator_db <> N'''' and @7originator is not null and @7originator_db is not null
            begin
                set @originator_id = null select @originator_id = id from MSrepl_originators where
                    publisher_database_id = @publisher_database_id and UPPER(srvname) = UPPER(@7originator) and
                    dbname = @7originator_db
                if @originator_id is null
                begin
                    insert into MSrepl_originators (publisher_database_id, srvname, dbname) values
                        (@publisher_database_id, @7originator, @7originator_db)
                    select @originator_id = @@identity
                end
            end
            else
                select @originator_id = 0

		if( @type in( 37,38 ) )
		begin
		  select @syncstat = 38 - @7type
		  exec sp_MSset_syncstate @publisher_id, @publisher_db, @7article_id, @syncstat, @7xact_seqno
		end

        INSERT INTO MSrepl_commands (publisher_database_id, xact_seqno, type, article_id, originator_id, command_id, partial_command, command)
		VALUES (@publisher_database_id,
            @7xact_seqno,@7type, @7article_id,
            @originator_id,
            @7command_id, @7partial_command, @7command)
    end

    IF @8xact_id = 0x0
      return
    IF @8command IS NOT NULL
    begin
            if @8originator <> N'''' and @8originator_db <> N'''' and @8originator is not null and @8originator_db is not null
            begin
                set @originator_id = null select @originator_id = id from MSrepl_originators where
                    publisher_database_id = @publisher_database_id and UPPER(srvname) = UPPER(@8originator) and
                    dbname = @8originator_db
                if @originator_id is null
                begin
                    insert into MSrepl_originators (publisher_database_id, srvname, dbname) values
                        (@publisher_database_id, @8originator, @8originator_db)
                    select @originator_id = @@identity
                end
            end
            else
                select @originator_id = 0

		if( @type in( 37,38 ) )
		begin
		  select @syncstat = 38 - @8type
		  exec sp_MSset_syncstate @publisher_id, @publisher_db, @8article_id, @syncstat, @8xact_seqno
		end

        INSERT INTO MSrepl_commands (publisher_database_id, xact_seqno, type, article_id, originator_id, command_id, partial_command, command)
		VALUES (@publisher_database_id,
            @8xact_seqno,@8type, @8article_id,
            @originator_id,
            @8command_id, @8partial_command, @8command)
    end

    IF @9xact_id = 0x0
      return
    IF @9command IS NOT NULL
    begin
            if @9originator <> N'''' and @9originator_db <> N'''' and @9originator is not null and @9originator_db is not null
            begin
                set @originator_id = null select @originator_id = id from MSrepl_originators where
                    publisher_database_id = @publisher_database_id and UPPER(srvname) = UPPER(@9originator) and
                    dbname = @9originator_db
                if @originator_id is null
                begin
                    insert into MSrepl_originators (publisher_database_id, srvname, dbname) values
                        (@publisher_database_id, @9originator, @9originator_db)
                    select @originator_id = @@identity
                end
            end
            else
                select @originator_id = 0

		if( @type in( 37,38 ) )
		begin
		  select @syncstat = 38 - @9type
		  exec sp_MSset_syncstate @publisher_id, @publisher_db, @9article_id, @syncstat, @9xact_seqno
		end

        INSERT INTO MSrepl_commands (publisher_database_id, xact_seqno, type, article_id, originator_id, command_id, partial_command, command)
		VALUES (@publisher_database_id,
            @9xact_seqno,@9type, @9article_id,
            @originator_id,
            @9command_id, @9partial_command, @9command)
    end

    IF @10xact_id = 0x0
      return
    IF @10command IS NOT NULL
    begin
            if @10originator <> N'''' and @10originator_db <> N'''' and @10originator is not null and @10originator_db is not null
            begin
                set @originator_id = null select @originator_id = id from MSrepl_originators where
                    publisher_database_id = @publisher_database_id and UPPER(srvname) = UPPER(@10originator) and
                    dbname = @10originator_db
                if @originator_id is null
                begin
                    insert into MSrepl_originators (publisher_database_id, srvname, dbname) values
                        (@publisher_database_id, @10originator, @10originator_db)
                    select @originator_id = @@identity
                end
            end
            else
                select @originator_id = 0

		if( @type in( 37,38 ) )
		begin
		  select @syncstat = 38 - @10type
		  exec sp_MSset_syncstate @publisher_id, @publisher_db, @10article_id, @syncstat, @10xact_seqno
		end

        INSERT INTO MSrepl_commands (publisher_database_id, xact_seqno, type, article_id, originator_id, command_id, partial_command, command)
		VALUES (@publisher_database_id,
            @10xact_seqno,@10type, @10article_id,
            @originator_id,
            @10command_id, @10partial_command, @10command)
    end

    IF @11xact_id = 0x0
      return
    IF @11command IS NOT NULL
    begin
            if @11originator <> N'''' and @11originator_db <> N'''' and @11originator is not null and @11originator_db is not null
            begin
                set @originator_id = null select @originator_id = id from MSrepl_originators where
                    publisher_database_id = @publisher_database_id and UPPER(srvname) = UPPER(@11originator) and
                    dbname = @11originator_db
                if @originator_id is null
                begin
                    insert into MSrepl_originators (publisher_database_id, srvname, dbname) values
                        (@publisher_database_id, @11originator, @11originator_db)
                    select @originator_id = @@identity
                end
            end
            else
                select @originator_id = 0

		if( @type in( 37,38 ) )
		begin
		  select @syncstat = 38 - @11type
		  exec sp_MSset_syncstate @publisher_id, @publisher_db, @11article_id, @syncstat, @11xact_seqno
		end

        INSERT INTO MSrepl_commands (publisher_database_id, xact_seqno, type, article_id, originator_id, command_id, partial_command, command)
		VALUES (@publisher_database_id,
            @11xact_seqno,@11type, @11article_id,
            @originator_id,
            @11command_id, @11partial_command, @11command)
    end

    IF @12xact_id = 0x0
      return
    IF @12command IS NOT NULL
    begin
            if @12originator <> N'''' and @12originator_db <> N'''' and @12originator is not null and @12originator_db is not null
            begin
                set @originator_id = null select @originator_id = id from MSrepl_originators where
                    publisher_database_id = @publisher_database_id and UPPER(srvname) = UPPER(@12originator) and
                    dbname = @12originator_db
                if @originator_id is null
                begin
                    insert into MSrepl_originators (publisher_database_id, srvname, dbname) values
                        (@publisher_database_id, @12originator, @12originator_db)
                    select @originator_id = @@identity
                end
            end
            else
                select @originator_id = 0

		if( @type in( 37,38 ) )
		begin
		  select @syncstat = 38 - @12type
		  exec sp_MSset_syncstate @publisher_id, @publisher_db, @12article_id, @syncstat, @12xact_seqno
		end

        INSERT INTO MSrepl_commands (publisher_database_id, xact_seqno, type, article_id, originator_id, command_id, partial_command, command)
		VALUES (@publisher_database_id,
            @12xact_seqno,@12type, @12article_id,
            @originator_id,
            @12command_id, @12partial_command, @12command)
    end


    IF @13xact_id = 0x0
      return
    IF @13command IS NOT NULL
    begin
            if @13originator <> N'''' and @13originator_db <> N'''' and @13originator is not null and @13originator_db is not null
            begin
                set @originator_id = null select @originator_id = id from MSrepl_originators where
                    publisher_database_id = @publisher_database_id and UPPER(srvname) = UPPER(@13originator) and
                    dbname = @13originator_db
                if @originator_id is null
                begin
                    insert into MSrepl_originators (publisher_database_id, srvname, dbname) values
                        (@publisher_database_id, @13originator, @13originator_db)
                    select @originator_id = @@identity
                end
            end
            else
                select @originator_id = 0

		if( @type in( 37,38 ) )
		begin
		  select @syncstat = 38 - @13type
		  exec sp_MSset_syncstate @publisher_id, @publisher_db, @13article_id, @syncstat, @13xact_seqno
		end

        INSERT INTO MSrepl_commands (publisher_database_id, xact_seqno, type, article_id, originator_id, command_id, partial_command, command)
VALUES (@publisher_database_id,
            @13xact_seqno,@13type, @13article_id,
            @originator_id,
            @13command_id, @13partial_command, @13command)
    end

    IF @14xact_id = 0x0
      return
    IF @14command IS NOT NULL
    begin
            if @14originator <> N'''' and @14originator_db <> N'''' and @14originator is not null and @14originator_db is not null
            begin
                set @originator_id = null select @originator_id = id from MSrepl_originators where
                    publisher_database_id = @publisher_database_id and UPPER(srvname) = UPPER(@14originator) and
                    dbname = @14originator_db
                if @originator_id is null
                begin
                    insert into MSrepl_originators (publisher_database_id, srvname, dbname) values
                        (@publisher_database_id, @14originator, @14originator_db)
                    select @originator_id = @@identity
                end
            end
            else
                select @originator_id = 0

		if( @type in( 37,38 ) )
		begin
		  select @syncstat = 38 - @14type
		  exec sp_MSset_syncstate @publisher_id, @publisher_db, @14article_id, @syncstat, @14xact_seqno
		end

        INSERT INTO MSrepl_commands (publisher_database_id, xact_seqno, type, article_id, originator_id, command_id, partial_com
 
Last revision 2008RTM
See also

  sp_adddistributiondb (Procedure)
sp_MSdrop_asm_tranrepl (Procedure)
sp_MSrestoredbreplication (Procedure)
sp_vupgrade_replication (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash