-
Notifications
You must be signed in to change notification settings - Fork 2
/
SQL Agent Job details by job_id.snippet
46 lines (46 loc) · 1.78 KB
/
SQL Agent Job details by job_id.snippet
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
<_locDefinition xmlns="urn:locstudio">
<_locDefault _loc="locNone" />
<_locTag _loc="locData">Title</_locTag>
<_locTag _loc="locData">Description</_locTag>
<_locTag _loc="locData">Author</_locTag>
<_locTag _loc="locData">ToolTip</_locTag>
</_locDefinition>
<CodeSnippet Format="1.0.0">
<Header>
<Title>SQL Agent Job details by job_id</Title>
<Shortcut></Shortcut>
<Description>Returns SQL Agent job and job step details for a specific job_id.</Description>
<Author>Dave Mason https://mastodon.social/@DaveMasonDotMe </Author>
<SnippetTypes>
<SnippetType>Expansion</SnippetType>
</SnippetTypes>
</Header>
<Snippet>
<Declarations>
<Literal>
<ID>job_id</ID>
<ToolTip>Unique ID of the job.</ToolTip>
<Default>0x6AC0F53DE7F66B4E933AD9E99EBF2DF1</Default>
</Literal>
</Declarations>
<Code Language="SQL">
<![CDATA[SELECT j.name AS JobName, j.description AS JobDescription,
js.step_id, js.step_name, js.subsystem,
(SELECT CHAR(13) + CHAR(10) + js.command + CHAR(13) + CHAR(10) FOR XML PATH(''),TYPE) AS [Command as XML],
js.database_name,
'SQLAgent - TSQL JobStep (Job ' +
--Cast the job_id (UNIQUEIDENTIFIER) as VARBINARY first, then convert to VARCHAR.
CONVERT(VARCHAR(64), CAST(j.job_id AS VARBINARY(100)), 1) +
' : Step ' + CAST(js.step_id AS VARCHAR(MAX)) + ')' AS ["Program Name" (as it appears in DMVs, spWhoIsActive, etc.)]
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobsteps js
ON js.job_id = j.job_id
WHERE j.job_id = $job_id$
ORDER BY j.name, js.step_id
]]>
</Code>
</Snippet>
</CodeSnippet>
</CodeSnippets>