Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSenum_replication_job

  No additional text.


Syntax

create  procedure sys.sp_MSenum_replication_job
(
    @job_id uniqueidentifier,
	@step_uid uniqueidentifier = null
)
as
begin
    SET NOCOUNT ON

    declare @retcode 		int
            ,@time 			int
            ,@date 			int
            ,@datetime		nvarchar(50)
            ,@runstatus 	int
            ,@message 		nvarchar(1024)
            ,@job_name		sysname
            ,@session_id 	int
            ,@job_found		bit

    
    -- security check
    
    exec @retcode = sys.sp_MSreplcheck_subscribe
    if @@ERROR <> 0 or @retcode <> 0
    begin
        return(1)
    end

	-- check that the user has permission to view this job information
	exec @retcode = sys.sp_MSrepl_check_job_access @id = @job_id,
													@err_not_found = 0,
													@step_uid = @step_uid,
													@job_found = @job_found output		
	if @@ERROR <> 0 or @retcode <> 0
    begin
        return(1)
    end

	
	if @job_found = 1
	begin
		-- retrieve the lastest session id (New session id on every start of sqlagent service)
	    select top(1) @session_id = session_id
			from msdb..syssessions
			order by agent_start_date desc

		-- retrieve current runstatus, message and date time	
		select @runstatus 	= case
								when sysja.run_requested_date is NULL then 5								-- Case when job has never been run but sqlagent is started
								when sysja.job_history_id is not NULL and sysjh.run_status is NULL then 5	-- Case when job has been run but history has been truncated
								else isnull(sysjh.run_status, 4)											-- Normal case...
							end,
				@message 	= isnull(nullif(ltrim(sysjh.message), N''), formatmessage(14243, sysj.name)),							
				@date 		= convert(int, convert(nvarchar(4000), sysja.start_execution_date, 112)),
				@time 		= convert(int, replace(convert(nvarchar(4000), sysja.start_execution_date, 8), ':', '')),
				@datetime 	= replace(convert(nvarchar(4000), sysja.start_execution_date, 21), '-', ''),
				@job_name	= sysj.name
			from msdb.dbo.sysjobactivity sysja
				join msdb.dbo.sysjobs sysj
					on sysja.job_id = sysj.job_id
				left join msdb.dbo.sysjobhistory sysjh
					on sysja.job_id = sysjh.job_id
						and sysja.job_history_id = sysjh.instance_id
			where sysja.job_id = @job_id
				and sysja.session_id = @session_id

	    -- Map SQL Agent runstatus to Replication runstatus
	    select @runstatus 	= case @runstatus
			                        when 0 then 6   -- 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
	end
	else
	begin
		select @runstatus	= NULL,
    			@message	= NULL,	
	    		@date		= NULL,
	        	@time		= NULL,
	        	@datetime	= NULL
	end

	-- Return status and message
	select 'runstatus' 	= @runstatus,
    		'message'  	= @message,
    		'date'  	= @date,
        	'time'  	= @time,
        	'datetime' 	= @datetime
	
    -- all done
    return 0
end

 
Last revision 2008RTM
See also

  sp_get_job_status_mergepullsubscription_agent (Procedure)
sp_get_job_status_mergepushsubscription_agent (Procedure)
sp_MSdrop_replcom (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