Write a query to display the following in this sequence: job, the number of peop

Question

Explanation / Answer

SET NOCOUNT ON 002. 003. DECLARE @MaxLength INT 004. SET @MaxLength = 50 005. 006. DECLARE @xp_results TABLE ( 007. job_id uniqueidentifier NOT NULL, 008. last_run_date nvarchar (20) NOT NULL, 009. last_run_time nvarchar (20) NOT NULL, 010. next_run_date nvarchar (20) NOT NULL, 011. next_run_time nvarchar (20) NOT NULL, 012. next_run_schedule_id INT NOT NULL, 013. requested_to_run INT NOT NULL, 014. request_source INT NOT NULL, 015. request_source_id sysname 016. COLLATE database_default NULL, 017. running INT NOT NULL, 018. current_step INT NOT NULL, 019. current_retry_attempt INT NOT NULL, 020. job_state INT NOT NULL 021. ) 022. 023. DECLARE @job_owner sysname 024. 025. DECLARE @is_sysadmin INT 026. SET @is_sysadmin = isnull (is_srvrolemember ('sysadmin'), 0) 027. SET @job_owner = suser_sname () 028. 029. INSERT INTO @xp_results 030. EXECUTE sys.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner 031. 032. UPDATE @xp_results 033. SET last_run_time = right ('000000' + last_run_time, 6), 034. next_run_time = right ('000000' + next_run_time, 6) 035. 036. SELECT j.name AS JobName, 037. j.enabled AS Enabled, 038. CASE x.running 039. WHEN 1 040. THEN 041. 'Running' 042. ELSE 043. CASE h.run_status 044. WHEN 2 THEN 'Inactive' 045. WHEN 4 THEN 'Inactive' 046. ELSE 'Completed' 047. END 048. END 049. AS CurrentStatus, 050. coalesce (x.current_step, 0) AS CurrentStepNbr, 051. CASE 052. WHEN x.last_run_date > 0 053. THEN 054. convert (datetime, 055. substring (x.last_run_date, 1, 4) 056. + '-' 057. + substring (x.last_run_date, 5, 2) 058. + '-' 059. + substring (x.last_run_date, 7, 2) 060. + ' ' 061. + substring (x.last_run_time, 1, 2) 062. + ':' 063. + substring (x.last_run_time, 3, 2) 064. + ':' 065. + substring (x.last_run_time, 5, 2) 066. + '.000', 067. 121 068. ) 069. ELSE 070. NULL 071. END 072. AS LastRunTime, 073. CASE h.run_status 074. WHEN 0 THEN 'Fail' 075. WHEN 1 THEN 'Success' 076. WHEN 2 THEN 'Retry' 077. WHEN 3 THEN 'Cancel' 078. WHEN 4 THEN 'In progress' 079. END 080. AS LastRunOutcome, 081. CASE 082. WHEN h.run_duration > 0 083. THEN 084. (h.run_duration / 1000000) * (3600 * 24) 085. + (h.run_duration / 10000 % 100) * 3600 086. + (h.run_duration / 100 % 100) * 60 087. + (h.run_duration % 100) 088. ELSE 089. NULL 090. END 091. AS LastRunDuration 092. FROM @xp_results x 093. LEFT JOIN 094. msdb.dbo.sysjobs j 095. ON x.job_id = j.job_id 096. LEFT OUTER JOIN 097. msdb.dbo.syscategories c 098. ON j.category_id = c.category_id 099. LEFT OUTER JOIN 100. msdb.dbo.sysjobhistory h 101. ON x.job_id = h.job_id 102. AND x.last_run_date = h.run_date 103. AND x.last_run_time = h.run_time 104. AND h.step_id = 0

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.