Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSload_tmp_replication_status

  No additional text.


Syntax
create procedure sys.sp_MSload_tmp_replication_status
(
	@agent_type	int = 0
	,@distribution_db sysname = NULL -- for filtering specific distribution db agents
)
as
begin
    set nocount on
    declare @retcode int

    -- security check.
    -- User must be member of 'replmonitor' role in some distribution database at this distributor.
    exec @retcode = sys.sp_MSrepl_DistributorReplMonitorAccess
    if @retcode <> 0 or @@error <> 0
        return (1)

    set nocount on

	declare 	@cmd nvarchar(4000),
			@db_name sysname,
			@distbit int,
			@all_agents int,
			@misc_agent int

	select 	@cmd = null,
			@db_name = null,
			@distbit = 16,
			@all_agents = 0,
			@misc_agent = 5
	
    -- Load misc agent status if requested
	if @agent_type in (@all_agents, @misc_agent)
	begin
		insert into #tmp_replication_status (publisher, publisher_db, publication, publication_type, agent_type, status, agent_id, agent_name, job_id, time_stamp, publisher_srvid)
			select 'publisher' = '',
					'publisher_db' = '',
					'publication' = '',
					'publication_type' = -1,
					'agent_type' = @misc_agent,
			        'status' = case isnull(sjh.run_status,5) -- mapped to never run
			                when 0 then 5   -- Fail mapping
			                when 1 then 2   -- Success mapping
			                when 2 then 5   -- Retry mapping
			                when 3 then 2   -- Shutdown mapping
			                when 4 then 3   -- Inprogress mapping
			                when 5 then 0   -- Unknown is mapped to never run
					end,
					'agent_id' = 0,  -- do not assign any id as we do not keep any local metadata on these
					'agent_name'  = sj.name,
                                   'job_id' = sj. job_id,
					'time_stamp' = NULL,
					'publisher_srvid' = NULL
				from msdb.dbo.sysjobs sj with (nolock) left outer join msdb.dbo.sysjobhistory sjh with (nolock) ON
			            sj.job_id = sjh.job_id and
			            sjh.instance_id = (select max(instance_id)
												from msdb.dbo.sysjobhistory sjh2 with (nolock)
												where sjh2.job_id = sj.job_id)
				where sj.category_id in (11,12,16,17,18)
		if @@error <> 0
		begin
        	goto Err_Handler
		end
	end

	-- now lets load all of the agent status info on a per distdb basis
	-- filter on basis of distribution db_name if specified
	declare #distributiondb_cursor cursor local fast_forward for
		select name
		from master.dbo.sysdatabases
		where category & @distbit <> 0
			and has_dbaccess(name) = 1
			and (@distribution_db is null or @distribution_db = name)
	for read only
	open #distributiondb_cursor
	fetch next from #distributiondb_cursor into @db_name
	while @@fetch_status = 0
	begin
		select @cmd = QUOTENAME(@db_name) + '.sys.sp_MSenum_replication_status'

		insert into #tmp_replication_status exec @cmd @agent_type
		if @@error <> 0
	        	return 1

		fetch next from #distributiondb_cursor into @db_name
	end
	close #distributiondb_cursor
	deallocate #distributiondb_cursor

Done:
	return 0

Err_Handler:
	return 1
end

 
Last revision 2008RTM
See also

  sp_MSdrop_replcom (Procedure)
sp_MShelp_replication_status (Procedure)
sp_MSinit_replication_perfmon (Procedure)
sp_replmonitorrefreshdata (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