C# and I

May 29, 2012

sp_MSretrieve_publication_attributes (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSretrieve_publication_attributes(nvarchar @name
, nvarchar @database)

MetaData:

 create procedure sys.sp_MSretrieve_publication_attributes   
@name             sysname,  
@database         sysname  
AS  
  
SET NOCOUNT ON  
  
declare @mergepublish_bit    int  
declare @tranpublish_bit    int  
declare @procname            nvarchar(640)  
declare @retcode            int  
  
select @mergepublish_bit    = 4   
select @tranpublish_bit     = 1  
  
--   
--  Security Check, will be checked inside sp_MSretrieve_publication  
--  and sp_MSretrieve_mergepublication  
 --   
  
create table #publication_property (  
    Name                     sysname collate database_default,  
    dbname                    sysname collate database_default,  
    Publisher                sysname collate database_default,  
    Type                    nvarchar(15) collate database_default,  
    Description                nvarchar(255) collate database_default,  
    Status                    tinyint,  
    allow_pull                bit,  
    allow_sync_tran            bit,  
    allow_anonymous            bit,  
    allow_queued_tran        bit,  
    enabled_for_internet    bit,  
    third_party                bit  
)  
  
if (select category & @tranpublish_bit from master.dbo.sysdatabases where name = @database collate database_default) <> 0  
    begin  
        select @procname = quotename(@database) + '.sys.sp_MSretrieve_publication '  
        insert into #publication_property exec @retcode = @procname @name  
    end  
  
if (select category & @mergepublish_bit from master.dbo.sysdatabases where name = @database collate database_default) <> 0  
    begin  
        select @procname = quotename(@database) + '.sys.sp_MSretrieve_mergepublication '  
        insert into #publication_property exec @retcode = @procname @name  
    end  
select * from #publication_property  
drop table #publication_property

sp_MSrestoresavedforeignkeys (Transact-SQL MetaData) Definition

Definition:

sys.sp_MSrestoresavedforeignkeys(nvarchar @program_name)

MetaData:

 create procedure sys.sp_MSrestoresavedforeignkeys (  
    @program_name   sysname = null  
)  
as  
begin  
    set nocount on  
    declare @retcode                    int,  
            @constraint_name            sysname,  
            @parent_name                sysname,  
            @parent_schema              sysname,  
            @referenced_object_name     sysname,  
            @referenced_object_schema   sysname,  
            @is_disabled                bit,  
            @is_not_for_replication     bit,  
            @is_not_trusted             bit,  
            @delete_referential_action  tinyint,  
            @update_referential_action  tinyint,  
            @transaction_opened         bit,  
            @cursor_allocated           bit,  
            @cursor_opened              bit,  
            @command                    nvarchar(4000),  
            @referencing_object_id      int,  
            @referenced_object_id       int  
  
    select @retcode = 0,  
           @transaction_opened = 0,  
           @cursor_allocated = 0,  
           @cursor_opened = 0  
  
  
    --  Security check  
    exec @retcode = sys.sp_MSreplcheck_subscribe  
    if @@error <> 0 or @retcode <> 0  
        return 1  
  
    --  There is nothing to do if any of the foreign key restoration   
    --  system tables are missing  
    if object_id('dbo.MSsavedforeignkeys', 'U') is null or  
       object_id('dbo.MSsavedforeignkeycolumns', 'U') is null or  
       object_id('dbo.MSsavedforeignkeyextendedproperties', 'U') is null  
    begin  
        return 0  
    end  
  
    if @program_name is null  
    begin  
        select @program_name = program_name   
          from sys.sysprocesses  
         where spid = @@spid  
    end  
  
    begin transaction   
    save transaction sp_MSrestoresavedforeignkeys  
    set @transaction_opened = 1  
      
    declare hForeignKeys cursor local   
        for  
    select constraint_name,   
           parent_schema,  
           parent_name,  
           referenced_object_schema,  
           referenced_object_name,  
           is_disabled,  
           is_not_for_replication,  
           is_not_trusted,  
           delete_referential_action,  
           update_referential_action  
      from dbo.MSsavedforeignkeys      
    if @@error <> 0 or @retcode <> 0 begin set @retcode = 1 goto Failure end  
    set @cursor_allocated = 1  
  
    open hForeignKeys  
    if @@error <> 0 or @retcode <> 0 begin set @retcode = 1 goto Failure end  
    set @cursor_allocated = 1  
  
    fetch hForeignKeys into @constraint_name, @parent_schema, @parent_name,  
        @referenced_object_schema, @referenced_object_name, @is_disabled,  
        @is_not_for_replication, @is_not_trusted, @delete_referential_action,   
        @update_referential_action  
  
    while (@@fetch_status <> -1)  
    begin  
  
        --  Check if foreign key can be restored  
  
        --  Is the foreign key already present?  
        if object_id(quotename(@parent_schema) + N'.' + quotename(@constraint_name), 'F') is not null goto SkipForeignKey  
      
        --  Is the referencing table present?  
        select @referencing_object_id = object_id(quotename(@parent_schema) + N'.' + quotename(@parent_name), 'U')   
        if @referencing_object_id is null goto SkipForeignKey  
  
        --  Is the referenced table present?  
        select @referenced_object_id =  object_id(quotename(@referenced_object_schema) + N'.' + quotename(@referenced_object_name), 'U')   
        if @referenced_object_id is null goto SkipForeignKey  
  
        --  Are all the referencing columns present?  
        if exists (select *    
                     from dbo.MSsavedforeignkeycolumns  
                    where program_name = @program_name  
                      and constraint_name = @constraint_name  
                      and referencing_column_name not in  
                            (select name   
                               from sys.columns   
                              where object_id = @referencing_object_id))  
            goto SkipForeignKey  
  
        --  Are all the referenced columns present?  
        if exists (select *  
                     from dbo.MSsavedforeignkeycolumns  
                    where program_name = @program_name  
                      and constraint_name = @constraint_name  
                      and referenced_column_name not in  
                            (select name  
                               from sys.columns  
                              where object_id = @referenced_object_id))  
            goto SkipForeignKey  
   
        --  Is there a candidate referenced index key present?  
        if not exists (select key_constraints.name   
                         from sys.index_columns index_columns  
                   inner join sys.indexes indexes  
                           on index_columns.index_id = indexes.index_id  
                          and index_columns.object_id = indexes.object_id  
                   inner join sys.key_constraints key_constraints  
                           on indexes.name = key_constraints.name  
                          and schema_name(key_constraints.schema_id) = @referenced_object_schema  
                        where indexes.object_id = @referenced_object_id  
                          and index_columns.column_id <> 0  
                          and indexes.index_id not in  
                                --  Indexes with columns that do not match  
                                --  those saved in dbo.MSsavedforeignkeycolumns  
                                (select index_columns.index_id  
                                   from sys.index_columns index_columns  
                             inner join sys.columns columns  
                                     on index_columns.object_id = columns.object_id  
                                    and index_columns.column_id = columns.column_id  
                              left join   
                                    (select referenced_column_name, constraint_column_id  
                                       from dbo.MSsavedforeignkeycolumns  
                                      where program_name = @program_name  
                                        and constraint_name = @constraint_name  
                                        and parent_schema = @parent_schema) foreignkeycolumns (referenced_column_name, constraint_column_id)  
                                     on columns.name = foreignkeycolumns.referenced_column_name  
                                    and index_columns.key_ordinal = foreignkeycolumns.constraint_column_id  
                                  where index_columns.object_id = @referenced_object_id  
                                    and index_columns.column_id <> 0  
                                    and foreignkeycolumns.referenced_column_name is null)  
                         --  The the number of columns in the index matches   
                         --  that in the foreign key  
                     group by key_constraints.name  
                    having count(key_constraints.name) =   
                        (select count(*)   
                           from dbo.MSsavedforeignkeycolumns   
                          where program_name = @program_name  
                            and constraint_name = @constraint_name  
                            and parent_schema = @parent_schema))  
            goto SkipForeignKey  
  
        select @command = N'execute sys.sp_MSscriptforeignkeyrestore @program_name = N''' + replace(@program_name, N'''', N'''''') +  
            N''', @constraint_name = N''' + replace(@constraint_name, N'''', N'''''') +  
            N''', @parent_schema = N''' + replace(@parent_schema, N'''', N'''''') +  
            N''', @parent_name = N''' + replace(@parent_name, N'''', N'''''') +  
            N''', @referenced_object_schema = N''' + replace(@referenced_object_schema, N'''', N'''''') +  
            N''', @referenced_object_name = N''' + replace(@referenced_object_name, N'''', N'''''') +  
            N''', @is_not_for_replication = ' + convert(nvarchar(2), @is_not_for_replication) +  
            N', @is_not_trusted = ' + convert(nvarchar(2), @is_not_trusted) +   
            N', @delete_referential_action = ' + convert(nvarchar(3), @delete_referential_action) +  
            N', @update_referential_action = ' + convert(nvarchar(3), @update_referential_action)  
        if @@error <> 0 or @retcode <> 0 begin set @retcode = 1 goto Failure end  
  
        execute @retcode = sys.sp_execresultset @cmd = @command  
        if @@error <> 0 or @retcode <> 0 begin set @retcode = 1 goto Failure end  
  
        --  Disable foreign key if it was originally disabled  
        if @is_disabled = 1  
        begin  
            set @command = N'alter table ' + quotename(@parent_schema) + N'.' + quotename(@parent_name) + ' nocheck constraint ' + quotename(@constraint_name)  
            execute (@command)  
            if @@error <> 0 or @retcode <> 0 begin set @retcode = 1 goto Failure end  
        end  
  
        --  Restore extended properties defined on foreign key  
        execute @retcode = sys.sp_MSrestoresavedforeignkeyextendedproperties   
            @program_name = @program_name,  
            @constraint_name = @constraint_name,  
            @parent_schema = @parent_schema,  
            @parent_name = @parent_name  
        if @@error <> 0 or @retcode <> 0 begin set @retcode = 1 goto Failure end  
   
SkipForeignKey:  
  
        fetch hForeignKeys into @constraint_name, @parent_schema, @parent_name,  
            @referenced_object_schema, @referenced_object_name, @is_disabled,  
            @is_not_for_replication, @is_not_trusted, @delete_referential_action,   
            @update_referential_action  
    end  
  
    close hForeignKeys  
    if @@error <> 0 or @retcode <> 0 begin set @retcode = 1 goto Failure end  
    set @cursor_opened = 0  
  
    deallocate hForeignKeys  
    if @@error <> 0 or @retcode <> 0 begin set @retcode = 1 goto Failure end  
    set @cursor_allocated = 0  
  
    --  Cleanup foreign key tables now that everything is restored  
    delete from dbo.MSsavedforeignkeys where program_name = @program_name  
    if @@error <> 0 or @retcode <> 0 begin set @retcode = 1 goto Failure end  
  
    delete from dbo.MSsavedforeignkeycolumns where program_name = @program_name  
    if @@error <> 0 or @retcode <> 0 begin set @retcode = 1 goto Failure end  
  
    delete from dbo.MSsavedforeignkeyextendedproperties where program_name = @program_name  
    if @@error <> 0 or @retcode <> 0 begin set @retcode = 1 goto Failure end  
  
    commit transaction  
    set @transaction_opened = 0  
  
Failure:  
    if @cursor_opened = 1  
    begin  
        close hForeignKeys  
    end      
  
    if @cursor_allocated = 1  
    begin  
        deallocate hForeignKeys  
    end  
  
    if @transaction_opened = 1  
    begin  
        rollback transaction sp_MSrestoresavedforeignkeys  
        commit transaction  
    end  
    return @retcode  
end

sp_MSresetsnapshotdeliveryprogress (Transact-SQL MetaData) Definition

Definition:

sys.sp_MSresetsnapshotdeliveryprogress(nvarchar @snapshot_session_token)

MetaData:

 --   
--  Name: sp_MSresetsnapshotdeliveryprogress  
--   
--  Description: This procedure removes all rows in the    
--               MSsnapshotdeliveryprogress table (if it exists) corresponding  
--               to the specified snapshot delivery session. This will  
--               effectively wipes out all memory of any previous   
--               progress that the snapshot delivery processes had made to  
--               the subscriber database.  
--   
--  Notes: This procedure should be called at the subscriber database by  
--         the distribution or the merge agent.  
--   
--  Parameter: @snapshot_session_token nvarchar(260) (mandatory)  
--                 - A UniCode string that uniquely identifies a snapshot  
--                   delivery session. For now, this will be the snapshot  
--                   generation folder of the snapshot being applied.   
--   
--  Returns: 0 - succeeded  
--           1 - failed  
--   
--  Security: Execute permission of this procedure is granted to public;  
--            procedural security check will be performed to make sure  
--            that the caller is either a db_owner of the current database  
--            or a sysadmin.   
--   
create procedure sys.sp_MSresetsnapshotdeliveryprogress  
    @snapshot_session_token nvarchar(260)  
as  
begin  
    set nocount on  
    declare @retcode int  
    select @retcode = 0  
    exec @retcode = sys.sp_MSreplcheck_subscribe   
    if @retcode <> 0 or @@error <> 0  
    begin  
        select @retcode = 1  
        goto FAILURE  
    end  
    if object_id('dbo.MSsnapshotdeliveryprogress') is not null  
    begin  
        delete dbo.MSsnapshotdeliveryprogress  
        where session_token = @snapshot_session_token  
        if @retcode <> 0 or @@error <> 0  
        begin  
            select @retcode = 1  
            goto FAILURE  
        end  
    end  
    if @retcode <> 0 or @@error <> 0  
    begin  
        select @retcode = 1  
        goto FAILURE  
    end  
  
  
FAILURE:  
    return @retcode      
end

sp_MSreset_transaction (Transact-SQL MetaData) Definition

Definition:

sys.sp_MSreset_transaction(nvarchar @publisher
, nvarchar @publisher_db
, varbinary @xact_seqno)

MetaData:

 CREATE PROCEDURE sys.sp_MSreset_transaction   
(  
    @publisher sysname,  
    @publisher_db sysname,  
    @xact_seqno varbinary(10)  
)   
AS  
begin  
    SET NOCOUNT ON  
    --   
    --  security check  
    --  only db_owner can execute this  
    --   
  
    if (is_member ('db_owner') != 1)   
    begin  
        raiserror(14260, 16, -1)  
        return (1)  
    end  
    declare @publisher_database_id int  
  
    select @publisher_database_id = a.id      
            from dbo.MSpublisher_databases a join master.dbo.sysservers b on  a.publisher_id = b.srvid  
        where a.publisher_db = @publisher_db  
            and upper(b.srvname) = upper(@publisher) collate database_default  
  
    if @@error<>0  
        return 1   
  
    insert dbo.MSrepl_transactions (publisher_database_id, xact_id, xact_seqno, entry_time)   
                values     (@publisher_database_id, @xact_seqno, @xact_seqno, GetDate())  
    if @@error<>0  
        return 1   
    return 0  
end

sp_MSreset_synctran_bit (Transact-SQL MetaData) Definition

Definition:

sys.sp_MSreset_synctran_bit(nvarchar @owner
, nvarchar @table)

MetaData:

 create procedure sys.sp_MSreset_synctran_bit (  
    @owner          sysname,  
    @table          sysname  
)AS  
    declare @qualified_name nvarchar(300)  
    declare @tabid    int  
    declare @retcode int  
  
    --  Security check  
    exec @retcode = sys.sp_MSreplcheck_subscribe      
    if @retcode<>0 or @@error<>0  
        return 1  
  
    if @owner = N''  
        select @qualified_name = @table  
    else  
        select @qualified_name = quotename(@owner) + '.' + quotename(@table)   
  
    BEGIN TRANSACTION  
    --  Unmark synctran bit  
    select @tabid = object_id(@qualified_name)  
    if not (@tabid is null)  
    begin  
        EXEC %%Object(MultiName = @qualified_name).LockMatchID(ID = @tabid, Exclusive = 1, BindInternal = 0)  
        -- EXEC %%Object(MultiName = @qualified_name).LockExclusiveMatchID(ID = @tabid)  
        if @@error = 0  
            EXEC %%Relation(ID = @tabid).SetSyncTranSubscribe(Value = 0)  
    end  
    COMMIT TRANSACTION  
    return(0)

sp_MSreset_subscription_seqno (Transact-SQL MetaData) Definition

Definition:

sys.sp_MSreset_subscription_seqno(int @agent_id
, bit @get_snapshot)

MetaData:

 CREATE PROCEDURE sys.sp_MSreset_subscription_seqno   
(  
    @agent_id int,  
    @get_snapshot bit   
)  
as  
begin  
    set nocount on  
  
    declare @publication_id int  
    declare @sub_agent_id int  
    declare @virtual_anonymous smallint  
    declare @virtual smallint  
    declare @retcode int  
    declare @automatic tinyint  
  
    select @automatic = 1  
    select @virtual = -1  
    select @virtual_anonymous = -2  
  
    --  Security Check  
    exec @retcode = sys.sp_MScheck_pull_access  
        @agent_id = @agent_id,  
        @agent_type = 0 --  distribution agent  
    if @@error <> 0 or @retcode <> 0  
        return (1)  
  
    --  Get version agent_id  
    select top 1 @sub_agent_id = s2.agent_id from dbo.MSsubscriptions s1,  
        MSsubscriptions s2 where  
        s1.publisher_id = s2.publisher_id and  
        s1.publisher_db = s2.publisher_db and  
        s1.publication_id = s2.publication_id and  
        s1.agent_id = @agent_id and  
        s2.subscriber_id =   
            case @get_snapshot when 0   
                then @virtual_anonymous  
                else @virtual  
            end   
      
    --  If there are no virtual subscriptions defined. Don't reset.  
    --  This might happen when the distribution agent tries to   
    --  reset an subscription on a non immediate_sync publication   
    --  (this can  
    --  only happen when the publication is changed from immediate_sync to non  
    --  immdiate_sync after the distribution agent has queried the immediate_sync  
    --  property)  
    if @sub_agent_id = 0  
        return 0  
  
    --  'no_sync' subscriptions are handled differently  
    if exists (select * from dbo.MSsubscriptions where agent_id = @agent_id and   
        sync_type <> @automatic)  
    begin  
        --  If @get_snapshot = 0, the distribution agent is process the attached  
        --  subscription for the first time. Set subscription_seqno to zero so that  
        --  all changes that are not in the subscription copy will be picked up.  
        --  Otherwise, do noting.  
        if @get_snapshot = 0  
        begin  
            update dbo.MSsubscriptions  set   
                --  Use current date rather than virtual sub date for the  
                --  calculation in cleanup   
                subscription_time = getdate(),  
                --  lsn should be ten bytes long. We will not be here  
                --  if the publisher is 6.x since 6x publisher does not  
                --  support immediate_sync (thus does not support subscription copy as well)  
                subscription_seqno = 0x00000000000000000000,  
                publisher_seqno = 0x00000000000000000000,  
                ss_cplt_seqno = 0x00000000000000000000  
                from dbo.MSsubscriptions rs1 where  
                    agent_id = @agent_id   
        end          
        return 0  
    end  
  
    --  Reset the subscription statue to be that of the virtual_anonymous subscription.  
    --  Thus, only the snapshot transactions that are later than the subscriber transaction  
    --  timestamp will be picked up (i.e., new article or schema change article.)  
    update dbo.MSsubscriptions  set   
        snapshot_seqno_flag =    
            (select snapshot_seqno_flag from dbo.MSsubscriptions rs2  
                where  
                rs2.agent_id = @sub_agent_id and  
                rs2.article_id = rs1.article_id),  
        status =      
            (select status from dbo.MSsubscriptions rs2  
                where  
                rs2.agent_id = @sub_agent_id and  
                rs2.article_id = rs1.article_id),  
        --  Use current date rather than virtual sub date for the  
        --  calculation in cleanup   
        subscription_time = getdate(),  
        subscription_seqno =   
            (select subscription_seqno from dbo.MSsubscriptions rs2  
                where  
                rs2.agent_id = @sub_agent_id and  
                rs2.article_id = rs1.article_id),  
        publisher_seqno =   
            (select publisher_seqno from dbo.MSsubscriptions rs2  
                where  
                rs2.agent_id = @sub_agent_id and  
                rs2.article_id = rs1.article_id),  
        ss_cplt_seqno =   
            (select ss_cplt_seqno from dbo.MSsubscriptions rs2  
                where  
                rs2.agent_id = @sub_agent_id and  
                rs2.article_id = rs1.article_id)  
        from dbo.MSsubscriptions rs1 where  
            agent_id = @agent_id  
          
    if @@ERROR <> 0  
        return 1  
end

sp_MSreset_subscription (Transact-SQL MetaData) Definition

Definition:

sys.sp_MSreset_subscription(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @subscriber
, nvarchar @subscriber_db
, int @subscription_type)

MetaData:

 CREATE PROCEDURE sys.sp_MSreset_subscription   
(  
    @publisher sysname,  
    @publisher_db sysname,  
    @publication sysname,    
    @subscriber sysname,  
    @subscriber_db sysname,  
    @subscription_type int  --  have to have it to identify a distribution agent.  
)   
AS  
begin  
    SET NOCOUNT ON  
  
    --   
    --  Declarations.  
     --   
    DECLARE @retcode            int  
    DECLARE @publisher_id       smallint  
    DECLARE @subscriber_id      smallint  
    DECLARE @virtual            smallint  
    DECLARE @virtual_anonymous  smallint  
    declare @anonymous_subtype  smallint  
    declare @publication_id        int  
    declare @immediate_sync        bit  
  
    --   
    --  security check  
    --  only db_owner can execute this  
    --   
    if (is_member ('db_owner') != 1)   
    begin  
        raiserror(14260, 16, -1)  
        return (1)  
    end  
  
    --   
    --  Initializations  
     --   
    select @virtual = -1  
    select @virtual_anonymous = -2  
    select @anonymous_subtype = 2  
  
    select @publisher_id = srvid from master.dbo.sysservers where   
        UPPER(srvname) = UPPER(@publisher)  
  
    select @subscriber_id = srvid from master.dbo.sysservers where   
        UPPER(srvname) = UPPER(@subscriber)   
  
    select @publication_id = publication_id, @immediate_sync = immediate_sync  
        from dbo.MSpublications where  
        publisher_id = @publisher_id AND  
        publisher_db = @publisher_db AND  
        publication = @publication  
  
    if @subscriber is NULL  
        select @subscriber_id = @virtual  
  
    --  No need to have 2 updates in one transaction.  
    if @immediate_sync = 1  
    begin  
        UPDATE MSdistribution_agents SET subscription_guid = newid()  
            WHERE  
            publisher_id = @publisher_id AND  
            publisher_db = @publisher_db AND  
            publication = @publication and  
            subscriber_id = @subscriber_id and  
            subscriber_db = @subscriber_db and  
            subscription_type = @subscription_type  
  
        IF @@ERROR <> 0  
            GOTO UNDO  
  
        if @subscriber_id = @virtual  
        begin  
            --  reset the partial snapshot progress of all anonymous   
            --  agents associated with this virtual subscription  
  
            update dbo.MSdistribution_agents   
               set reset_partial_snapshot_progress = 1  
             where  
            publisher_id = @publisher_id AND  
            publisher_db = @publisher_db AND  
            publication = @publication and  
            subscriber_id <> @virtual and  
            subscriber_id <> @virtual_anonymous and  
            subscription_type = @anonymous_subtype  
        end   
    end  
  
    UPDATE dbo.MSsubscriptions set subscription_time = getdate()   
        WHERE  
        publisher_id = @publisher_id AND  
        publisher_db = @publisher_db AND  
        publication_id = @publication_id and  
        subscriber_id = @subscriber_id and  
        subscriber_db = @subscriber_db and  
        subscription_type = @subscription_type  
  
    IF @@ERROR <> 0  
        GOTO UNDO  
  
    RETURN(0)  
  
UNDO:  
    return(1)  
end

 create procedure sys.sp_MSreset_queued_reinit   
(  
    @subscriber      sysname,                    --  subscriber server name  
    @subscriber_db   sysname,                --  subscribing database name.   
    @artid    int                                   --  article id  
)  
as  
begin  
    set nocount on  
    declare @retcode int  
                ,@publication sysname  
    --   
    --  PAL security check  
    --  Get publication name using artid  
    --   
    select @publication = p.name  
    from syspublications p join sysarticles a on p.pubid = a.pubid  
    where a.artid = @artid  
    if (@publication is null)  
    begin  
        return (1)  
    end  
    exec @retcode = sys.sp_MSreplcheck_pull @publication = @publication  
    if @@error <> 0 or @retcode <> 0  
    begin  
        return (1)  
    end  
    --   
    --  Validate @subscriber  
    --   
    --   
    --  validate @subscriber_db, @artid  
    --   
    if exists (select *   
        from dbo.syssubscriptions  
        where artid = @artid   
            and srvname = UPPER(@subscriber)   
            and dest_db = @subscriber_db)  
    begin  
        --   
        --  set the reinit flag  
        --   
        update dbo.syssubscriptions  
        set queued_reinit = 0  
        where artid = @artid   
            and srvname = UPPER(@subscriber)   
            and dest_db = @subscriber_db  
        if (@@error != 0)  
            return (1)  
    end  
    else  
    begin  
        return (1)  
    end  
    --   
    --  all done  
    --   
    return (0)  
end

 create procedure sys.sp_MSrepl_snapshot_helppublication  
(  
    @publication    sysname,  
    @publisher        sysname  
)  
AS  
BEGIN  
    SET NOCOUNT ON  
  
    --   
    --  Declarations.  
     --   
  
    DECLARE @pubid                int  
    DECLARE @has_subscription    bit  
    DECLARE @retcode            int  
    DECLARE @publish_bit        int  
      
    SELECT @publish_bit = 1  
  
    --   
    --  Security Check  
     --   
    EXEC @retcode = sys.sp_MSreplcheck_publish  
    IF @@ERROR <> 0 or @retcode <> 0  
        return (1)  
      
  
    --  Parameter Check:  @publisher  
    IF @publisher IS NULL  
    BEGIN  
        RAISERROR (14043, 16, -1, '@publisher', 'sp_MSrepl_snapshot_helppublication')  
        RETURN (1)  
    END  
  
    --  Parameter Check:  @publication  
    IF @publication IS NULL  
    BEGIN  
        RAISERROR (14043, 16, -1, '@publication', 'sp_MSrepl_snapshot_helppublication')  
        RETURN (1)  
    END  
      
    EXECUTE @retcode = dbo.sp_validname @publication  
    IF @retcode <> 0  
        RETURN (1)  
      
    SELECT    'pubid'                        = sp.pubid,  
            'name'                        = sp.name,  
            'restricted'                = 0,  
            'status'                    = sp.status,  
            --  using 'task' is for backward compatibilty  
            'task'                        = convert(int, 1),  
            'replication frequency'        = sp.repl_freq,  
            'synchronization method'    = sp.sync_method,  
            'description'                = sp.description,  
            'immediate_sync'            = sp.immediate_sync,  
            'enabled_for_internet'        = sp.enabled_for_internet,  
            'allow_push'                = sp.allow_push,  
            'allow_pull'                = sp.allow_pull,  
            'allow_anonymous'            = sp.allow_anonymous,  
            'independent_agent'            = sp.independent_agent,  
            'immediate_sync_ready'        = sp.immediate_sync_ready,  
            --  SyncTran  
            'allow_sync_tran'            = sp.allow_sync_tran,  
            'autogen_sync_procs'        = sp.autogen_sync_procs,  
            'snapshot_jobid'            = sp.snapshot_jobid,  
            'retention'                    = sp.retention,  
            'has subscription'            = CASE WHEN EXISTS  
                                            (  
                                                SELECT    *  
                                                FROM    IHsubscriptions  
                                                WHERE    article_id IN   
                                                (  
                                                    SELECT    article_id  
                                                    FROM    IHarticles  
                                                    WHERE    publication_id = sp.pubid  
                                                )  
                                            )  
                                            THEN 1 ELSE 0 END,  
            'allow_queued_tran'            = sp.allow_queued_tran,  
            --  Portable snapshot  
            'snapshot_in_defaultfolder'    = sp.snapshot_in_defaultfolder,  
            'alt_snapshot_folder'        = sp.alt_snapshot_folder,  
            --  Pre/post-snapshot commands  
            'pre_snapshot_script'        = sp.pre_snapshot_script,  
            'post_snapshot_script'        = sp.post_snapshot_script,  
            --  Snapshot compression  
            'compress_snapshot'            = sp.compress_snapshot,  
            --  Post 7.0 ftp support  
            'ftp_address'                = sp.ftp_address,  
            'ftp_port'                    = sp.ftp_port,  
            'ftp_subdirectory'            = sp.ftp_subdirectory,  
            'ftp_login'                    = sp.ftp_login,  
            'ftp_password'                = sys.fn_repldecryptver4(sp.ftp_password),  
            'allow_dts'                    = sp.allow_dts,  
            'allow_subscription_copy'    = sp.allow_subscription_copy,  
            --  7.5 Queued updates  
            'centralized_conflicts'        = NULL,   
            'conflict_retention'        = 14,   
            'conflict_policy'            = NULL,  
            'queue_type'                = NULL,  
            'backward_comp_level'        = sp.backward_comp_level,  
            'publish_to_AD'                = CASE  
                                            WHEN sp.ad_guidname IS NULL  
                                            THEN 0 ELSE 1 END  
    FROM    syspublications        sp,  
            MSpublications        msp,  
            master.dbo.sysservers    ss  
    WHERE    sp.pubid = msp.publication_id  
    AND        msp.publisher_id = ss.srvid  
    AND     UPPER(ss.srvname collate database_default) = UPPER(@publisher) collate database_default  
    AND        ((sp.name = @publication) or  (@publication = N'%'))  
    ORDER BY sp.name  
    IF @@ERROR <> 0 RETURN 1  
      
    RETURN (0)  
END

 create procedure sys.sp_MSrepl_subscription_rowset  
(  
    @subscriber sysname,  
    @agent_id int,  
    @agent_type int = 0  
)  
AS  
BEGIN  
    DECLARE @retcode int  
    DECLARE @subscriber_provider sysname  
      
    SELECT @subscriber_provider = NULL  
      
    --  Security check: sysadmin/dbo/PAL only  
    EXEC @retcode = sys.sp_MScheck_pull_access @agent_id = @agent_id,  
                                                @agent_type = @agent_type  
    IF @@error <> 0 or @retcode <> 0  
        RETURN 1  
          
    --  Query the MSdistribution_agents entry for the subscriber provider  
    SELECT  @subscriber_provider = ma.subscriber_provider  
    FROM    MSdistribution_agents ma, master.dbo.sysservers ss  
    WHERE    UPPER(ss.srvname) = UPPER(@subscriber) collate database_default  
      AND    ma.subscriber_id = ss.srvid  
      AND    ma.id = @agent_id  
  
    IF @@error <> 0  
        RETURN 1  
      
    --  If the subscriber_provider from the MSdistribution_agents table is       
    --  NULL, we either have a downlevel publisher, or yukon security is not  
    --  enabled.  In both cases, we need to call sp_MSrepl_linkedserver_rowset  
    --  to generate the rowset.  
    IF ISNULL(@subscriber_provider, N' ') = N' '  
    BEGIN  
        exec @retcode = sys.sp_MSrepl_linkedservers_rowset @srvname = @subscriber,  
                                                          @agent_id = @agent_id,  
                                                          @agent_type = @agent_type  
        IF @@error <> 0 or @retcode <> 0  
        BEGIN  
            RETURN 1  
        END  
              
        RETURN 0      
              
    END   
  
    --  Query the MSdistribution_agents entry for the subscriber information  
    SELECT  SUB_NAME            = ss.srvname,  
            SUB_PRODUCT         = ss.srvproduct,  
            SUB_PROVIDERNAME    = ma.subscriber_provider,  
            SUB_DATASOURCE      = ma.subscriber_datasrc,  
            SUB_PROVIDERSTRING  = ma.subscriber_provider_string,  
            SUB_LOCATION        = ma.subscriber_location,  
            SUB_CATALOG         = ma.subscriber_catalog  
    FROM    MSdistribution_agents ma, master.dbo.sysservers ss  
    WHERE    UPPER(ss.srvname) = UPPER(@subscriber) collate database_default  
      AND    ma.subscriber_id = ss.srvid  
      AND    ma.id = @agent_id  
  
    IF @@error <> 0  
    BEGIN  
        RETURN 1  
    END      
                                                 
    RETURN 0  
END

C# and I

May 29, 2012

sp_MSretrieve_publication_attributes (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_MSrestoresavedforeignkeys (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_MSresetsnapshotdeliveryprogress (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_MSreset_transaction (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_MSreset_synctran_bit (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_MSreset_subscription_seqno (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_MSreset_subscription (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_MSreset_queued_reinit (Transact-SQL MetaData) Definition

Definition:

MetaData:

May 25, 2012

sp_MSrepl_snapshot_helppublication (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_MSrepl_subscription_rowset (Transact-SQL MetaData) Definition

Definition:

MetaData:

Total Pageviews

Where i go...