SET NOCOUNT ON
DECLARE @SQLAgentServiceAccountName VARCHAR(250),
@PrintSQL BIT = 1,
@ExecuteSQL BIT = 0,
@SQL VARCHAR(MAX)
SELECT @SQLAgentServiceAccountName = SQLAgentServiceAccount
FROM Util.dbo.servers
WHERE SQLServerName = @@SERVERNAME
IF @@ROWCOUNT <> 1
BEGIN
RAISERROR('Cannot find @SQLAgentServiceAccountName for %s', 16, 1, @@SERVERNAME)
RETURN
END
/*
DECLARE @AgentServicePath NVARCHAR(156) = N'SYSTEM\CurrentControlSet\Services\' + CASE WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'SQLSERVERAGENT'
ELSE 'SQLAgent$' + @@SERVICENAME
END
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
@AgentServicePath,
N'ObjectName',
@SQLAgentServiceAccountName OUTPUT,
N'no_output'
*/
;
WITH s1
AS (SELECT 'EXEC msdb.dbo.sp_update_job
@job_name = N''' + j.name + ''',
@owner_login_name = ''' + @SQLAgentServiceAccountName + '''' AS SQL
FROM [msdb].[dbo].[sysjobs_view] j
LEFT OUTER JOIN master.sys.syslogins u ON u.sid = j.owner_sid
WHERE ISNULL(u.name, '') <> @SQLAgentServiceAccountName
UNION ALL
SELECT 'EXEC msdb.dbo.sp_update_job
@job_name = N''' + j.name + ''',
@notify_level_email = 2,
@notify_email_operator_name = ''DiabloSupport''' AS SQL
FROM [msdb].[dbo].[sysjobs_view] j
WHERE notify_level_email = 0)
SELECT @SQL = Util.dbo.StringConcat(SQL, '
')
FROM s1
IF @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
IF @ExecuteSQL = 1
AND @SQL <> ''
EXEC(@SQL )
IF @PrintSQL = 1
BEGIN
-- SET JOB STEP TO BE INCLUDED IN JOB HISTORY
SELECT j.name,
flags | 32 AS NewFlag,
js.*
-- UPDATE js SET flags = flags | 32
FROM msdb.dbo.sysjobsteps js
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = js.job_id
WHERE flags & 32 = 0
AND subsystem IN ('CmdExec', 'SSIS')
SELECT j.name,
flags | 4 AS NewFlag,
js.*
-- UPDATE js SET flags = flags | 4
FROM msdb.dbo.sysjobsteps js
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = js.job_id
WHERE flags & 4 = 0
AND subsystem = 'TSQL'
END
IF @ExecuteSQL = 1
BEGIN
-- SET JOB STEP TO BE INCLUDED IN JOB HISTORY
UPDATE js
SET flags = flags | 32
FROM msdb.dbo.sysjobsteps js
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = js.job_id
WHERE flags & 32 = 0
AND subsystem IN ('CmdExec', 'SSIS')
UPDATE js
SET flags = flags | 4
FROM msdb.dbo.sysjobsteps js
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = js.job_id
WHERE flags & 4 = 0
AND subsystem = 'TSQL'
END