Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSreset_queue

  No additional text.


Syntax
create procedure sys.sp_MSreset_queue (
    @publisher      sysname,                    -- publishing server name
    @publisher_db   sysname,                    -- publishing database name.
    @publication    sysname,                    -- publication name,
    @artid         int)
as
begin
    declare @subserver sysname
            ,@subdbname sysname
            ,@queue_id  sysname
            ,@update_mode int
            ,@retcode smallint
            ,@vbartid varbinary(20)
            ,@queue_server sysname

    set nocount on

    select     @subserver = @@servername,
            @subdbname = db_name(),
            @update_mode = update_mode,
            @queue_id = queue_id,
            @queue_server = queue_server
    from MSsubscription_agents
        where UPPER(publisher) = UPPER(@publisher)
            and publisher_db =  @publisher_db
            and publication = @publication

    if (@update_mode in (2,3))
    begin
        
        -- MSMQ processing
        -- prefix the queue_id with queue server in direct format
        -- and then perform the queue reset
        
        select @queue_id = N'DIRECT=OS:' + @queue_server + N'\PRIVATE$\' + @queue_id

        begin distributed tran
        exec @retcode = sys.xp_resetqueue @queue_id, @subserver,
                            @subdbname, @publication, @artid
        if (@retcode != 0 or @@error != 0)
        begin
            if (@@trancount > 0)
                rollback tran
            return (1)
        end
    end
    else if (@update_mode in (4,5))
    begin
        declare @tranid sysname

        begin tran
        
        -- process MSreplication_queue
        
        select @retcode = 0
        if (exists (select * from sys.objects
            where name = 'MSreplication_queue'))
        begin
            
            -- Strictly speaking we do no need
            -- to delete but, makes it easy for
            -- the queue reader agent
            -- Do not delete any reset messages
            
            delete dbo.MSreplication_queue
                where publisher = UPPER(@publisher)
                and publisher_db =  @publisher_db
                and publication = @publication
                and tranid not like N'sub-reset%'
        end
        else
        begin
            
            -- first queue subscription is being initialized
            -- create queue if necessary
            
            exec @retcode = sp_MScreate_sub_tables_internal
                @tran_sub_table = 0,
                @property_table = 0,
                @sqlqueue_table = 1
        end
        if (@retcode != 0 or @@error != 0)
        begin
            if (@@trancount > 0)
                rollback tran
            return (1)
        end
        
        -- process MSrepl_queuedtraninfo
        
        if (exists (select * from sys.objects
            where name = 'MSrepl_queuedtraninfo'))
        begin
            
            -- Strictly speaking we do no need
            -- to delete but, makes it easy for
            -- the queue reader agent
            -- Do not delete any reset messages
            
            delete dbo.MSrepl_queuedtraninfo
                where publisher = UPPER(@publisher)
                and publisher_db =  @publisher_db
                and publication = @publication
                and tranid not like N'sub-reset%'
        end
        else
        begin
            
            -- first queue subscription is being initialized
            -- create the traninfo if necessary
            
            exec @retcode = sp_MScreate_sub_tables_internal
                @tran_sub_table = 0,
                @property_table = 0,
                @sqlqueue_table = 1
        end
        if (@retcode != 0 or @@error != 0)
        begin
            if (@@trancount > 0)
                rollback tran
            return (1)
        end
        
        -- for subscription reinitialization we
        -- need to insert a RESYNC command message
        
        select @vbartid = cast(@artid as varbinary(20))
                ,@tranid = N'sub-reset-' + cast(NEWID() as sysname)
        insert into dbo.MSreplication_queue (publisher, publisher_db,
            publication,tranid, commandtype, data, datalen)
        values (UPPER(@publisher), @publisher_db,
            @publication, @tranid, 2, @vbartid, datalength(@vbartid))
        if ((@@error != 0) or (@retcode != 0))
        begin
            if (@@trancount > 0)
                rollback tran
            return (1)
        end
        
        -- add an entry in MSrepl_queuedtraninfo
        
        insert into dbo.MSrepl_queuedtraninfo (publisher,publisher_db,publication,tranid,maxorderkey,commandcount)
        values (UPPER(@publisher),@publisher_db,@publication,@tranid,@@identity,1)
    end

    commit tran
    return 0
end

 
Last revision SQL2008SP2
See also

  sp_addqueued_artinfo (Procedure)
sp_MSreset_attach_state (Procedure)
sp_MSreset_queued_reinit (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