Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_replqueuemonitor

  No additional text.


Syntax
create procedure sys.sp_replqueuemonitor (
    @publisher      sysname = NULL
    ,@publisherdb   sysname = NULL
    ,@publication   sysname = NULL
    ,@tranid            sysname = NULL
    ,@queuetype     tinyint = 0 -- 0 = All Queues, 1 = MSMQ, 2 = SQL
)
as
begin
    set nocount on
    declare @retcode int
            ,@queue_server sysname
            ,@queue_id sysname
            ,@data varbinary(8000)
            ,@datalen int
            ,@commandtype int
            ,@cmdstate bit
            ,@mesglen int
            ,@command nvarchar(4000)
            ,@partialindex int
            ,@rowlen int
            ,@comandlen int

    declare @k_mesg_partial_state bit
            ,@k_mesg_complete_state bit
            ,@k_mesg_tran_cmd int
            ,@k_max_rowlen int
            ,@k_queuetype_all tinyint
            ,@k_queuetype_msmq tinyint
            ,@k_queuetype_sql tinyint

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

    create table #mesgs (mesgid int identity PRIMARY KEY, queuetype tinyint default 1, publisher sysname collate database_default, publisher_db sysname collate database_default, publication sysname collate database_default,
                            tranid sysname collate database_default, commandlen int, command ntext)

    
    -- Check if need to look for subscriptions
    
    if exists (select * from sys.objects where name = 'MSsubscription_agents')
    begin
        
        -- Are there any qualifying subscriptions
        
        if exists (select * from dbo.MSsubscription_agents where
                publisher = case when @publisher is NULL then publisher else UPPER(@publisher) end AND
                publisher_db = case when @publisherdb is NULL then publisher_db else @publisherdb end AND
                publication = case when @publication is NULL then publication else @publication end )
        begin
            
            -- initialize
            
            select  @k_queuetype_all = 0
                    ,@k_queuetype_msmq = 1
                    ,@k_queuetype_sql = 2

            
            -- MSMQ based
            
            if (@queuetype in (@k_queuetype_all, @k_queuetype_msmq) and
                exists (select * from dbo.MSsubscription_agents where
                    publisher = case when @publisher is NULL then publisher else UPPER(@publisher) end AND
                    publisher_db = case when @publisherdb is NULL then publisher_db else @publisherdb end AND
                    publication = case when @publication is NULL then publication else @publication end  AND
                    update_mode IN (2,3) AND
                    queue_id != N'mssqlqueue'))
            begin
                
                -- enumerate each queue
                
                create table #queues (publisher sysname collate database_default, publisher_db sysname collate database_default, publication sysname collate database_default, queue_id sysname collate database_default)
                declare #htempcursor cursor local for
                    select publisher, publisher_db, publication, queue_server, queue_id
                    from dbo.MSsubscription_agents
                    where
                        publisher = case when @publisher is NULL then publisher else UPPER(@publisher) end AND
                        publisher_db = case when @publisherdb is NULL then publisher_db else @publisherdb end AND
                        publication = case when @publication is NULL then publication else @publication end  AND
                        update_mode IN (2,3) AND
                        queue_id != N'mssqlqueue'

                open #htempcursor
                fetch #htempcursor into @publisher, @publisherdb, @publication, @queue_server, @queue_id
                while (@@fetch_status = 0)
                begin
                    
                    -- add the queue server prefix
                    
                    select @queue_id = N'DIRECT=OS:' + @queue_server + N'\PRIVATE$\' + @queue_id

                    
                    -- Display all the messages in this queue
                    
                    insert into #mesgs (publisher, publisher_db, publication, tranid, commandlen, command)
                        exec @retcode = sys.xp_displayqueuemesgs @publisher, @publisherdb, @publication, @queue_id, @tranid
                    if (@retcode != 0 or @@error != 0)
                        return 1

                    
                    -- fetch next row
                    
                    fetch #htempcursor into @publisher, @publisherdb, @publication, @queue_server, @queue_id
                end
                close #htempcursor
                deallocate #htempcursor

                
                -- All MSMQ Queues processed
                
                drop table #queues
            end

            
            -- SQL Queued based
            
            if (@queuetype in (@k_queuetype_all, @k_queuetype_sql) and
                exists (select * from dbo.MSsubscription_agents where
                    publisher = case when @publisher is NULL then publisher else UPPER(@publisher) end AND
                    publisher_db = case when @publisherdb is NULL then publisher_db else @publisherdb end AND
                    publication = case when @publication is NULL then publication else @publication end  AND
                    update_mode IN (4,5) AND
                    queue_id = N'mssqlqueue'))
            begin
                
                -- check if we have a queue
                
                if exists (select * from sys.objects where name = 'MSreplication_queue')
                begin
                    
                    -- initialize
                    
                    select @mesglen = 0
                            ,@partialindex = 0
                            ,@k_mesg_partial_state = 1
                            ,@k_mesg_complete_state = 0
                            ,@k_mesg_tran_cmd = 1
                            ,@k_max_rowlen = 8000

                    
                    -- select the messages that qualify
                    

                    declare #htempcursor cursor local for
                        select publisher, publisher_db, publication, tranid, datalen, data, commandtype, cmdstate
                        from dbo.MSreplication_queue
                        where
                            publisher = case when @publisher is NULL then publisher else UPPER(@publisher) end AND
                            publisher_db = case when @publisherdb is NULL then publisher_db else @publisherdb end AND
                            publication = case when @publication is NULL then publication else @publication end  AND
                            tranid = case when @tranid IS NULL then tranid else @tranid end
                    open #htempcursor
                    fetch #htempcursor into @publisher, @publisherdb, @publication, @tranid, @datalen, @data, @commandtype, @cmdstate
                    while (@@fetch_status = 0)
                    begin
                        
                        -- check the message state
                        
                        if (@cmdstate = @k_mesg_partial_state)
                            select @partialindex = @partialindex + 1
                        select @mesglen = @mesglen + @datalen

                        
                        -- process the body only for command type messages
                        -- and if the command spans multiple rows, then
                        -- display only the first row
                        
                        if ((@commandtype = @k_mesg_tran_cmd) and
                            ((@cmdstate = @k_mesg_complete_state and @partialindex = 0) or
                            (@cmdstate = @k_mesg_partial_state and @partialindex = 1)))
                        begin
                            
                            -- decode the command
                            
                            exec @retcode = sys.xp_decodequeuecmd @data, @command OUTPUT
                            if (@retcode != 0 or @@error != 0)
                                return 1
                        end

                        
                        -- Are processing the final row for this command
                        
                        if (@cmdstate = @k_mesg_complete_state)
                        begin
                            
                            -- reset partial index
                            
                            if (@partialindex > 0)
                                select @partialindex = 0

                            if (@command IS NOT NULL)
                            begin
                                
                                -- check if the command needs to truncated to fit the max rowsize
                                
                                select @rowlen = 4 + DATALENGTH(@k_queuetype_sql) +
                                                 DATALENGTH(@publisher) + DATALENGTH(@publisherdb) +
                                                 DATALENGTH(@publication) + DATALENGTH(@tranid) +
                                                 DATALENGTH(@mesglen)
                                        ,@comandlen = DATALENGTH(@command)
                                if (@rowlen + @comandlen > @k_max_rowlen)
                                begin
                                    select @comandlen = @k_max_rowlen - @rowlen
                                    select @comandlen = @comandlen / 2
                                    select @command = SUBSTRING(@command, 1, @comandlen)
                                end

                                insert into #mesgs (queuetype, publisher, publisher_db, publication, tranid, commandlen, command)
                                values (@k_queuetype_sql, @publisher, @publisherdb, @publication, @tranid, @mesglen, @command)
                                if (@retcode != 0 or @@error != 0)
                                    return 1

                                select @command = NULL
                            end

                            
                            -- reset command len
                            
                            if (@mesglen > 0)
                                select @mesglen = 0
                        end

                        
                        -- fetch next row
                        
                        fetch #htempcursor into @publisher, @publisherdb, @publication, @tranid, @datalen, @data, @commandtype, @cmdstate
                    end
                    close #htempcursor
                    deallocate #htempcursor
                end

                
                -- All SQL Queues processed
                
            end
        end
    end

    
    -- return result
    
    select  queue = case when queuetype = @k_queuetype_msmq then N'MSMQ'
                        when queuetype = @k_queuetype_sql then N'SQLQ' end
            ,publisher
            ,publisher_db
            ,publication
            ,tranid
            ,commandlen
            ,command
    from #mesgs
    order by mesgid

    
    -- All done
    
    drop table #mesgs
    return 0
end

 
Last revision SQL2008SP1
See also

  sp_MSdrop_repltran (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