Job to Auto start remote SQL Service if stopped in automatic mode

Insert the instance details to be monitored.

Refer https://www.mssqltips.com/sqlservertip/3616/monitor-remote-sql-server-instance-availability/ for more details

Column	Datatype	Purpose
InstanceID	INT (Identity)	Primary Key (surrogate).
InstanceName	VARCHAR(128)	Name of the remote instance to be monitored.
Active	BIT	Flag indicating if the remote instance should be actively monitored (1) or not (0).
CheckSvcs	BIT	Flag indicating if the SQL services related to the remote instance should be monitored (1) or not (0).
ErrorLevel	TINYINT	Value used by the severity parameter of any RAISERROR commands executed by the SQL Server Agent jobs while monitoring the InstanceName.
LastUpdate	DATETIME	Last time the row was updated.
UpdatedBy	VARCHAR(128)	Name of the user that last updated the row.

-- Listing 1

USE [master]GO-- Create the custom error messageEXEC dbo.sp_addmessage @msgnum = 64004,@msgtext = 'Remote Instance Monitor Alert - %s',@severity = 8, @with_log = 'FALSE', @replace ='REPLACE';GO

-- Listing 2USE master -- Use dedicated administrative database where meta-data table should be createdGO
SET NOCOUNT ON;
/*****************************************************************************************Author: Mike Eastland ** **Purpose: The purpose of this script is to create SQL Agent jobs on the local ** instance that will monitor connectivity to remote SQL Server instances. ** **PreReqs: The code in the Remote Monitoring jobs depends on a table in the ** administrative database identified by the USE statement at the beginning ** of this script. This table (dbo.CustomSqlMon) contains a list of the remote ** instances to be monitored by the local SQL Agent jobs. The table will be ** created by this script. *****************************************************************************************/DECLARE @cmd NVARCHAR(MAX),@db VARCHAR(128),@jobAlert VARCHAR(128),@jobCat VARCHAR(128),@jobDB VARCHAR(128),@jobDescr VARCHAR(256),@jobId BINARY(16),@jobName VARCHAR(128),@jobOper VARCHAR(32),@jobStep VARCHAR(256),@jobType VARCHAR(128),@msg VARCHAR(MAX),@returnCode INT,@svr VARCHAR(128),@ver DECIMAL,@waitSec VARCHAR(3);
-- Edit variable values below where applicableSELECT @db = DB_NAME(),@jobAlert = 'Remote Instance Monitor',@jobCat = 'Instance Monitor',@jobOper = 'JobFailure_Notification', /* Name of preferred SQL Server Agent Operator on the local instance */ @svr = @@SERVERNAME, @ver = CONVERT(FLOAT, SUBSTRING(@@VERSION, (CHARINDEX('-', @@VERSION) + 1), 5)),@waitSec = '300'; /* Retry wait interval in seconds for remote connectivity monitoring job */-- End variable initialization
SET @msg = 'Creation of Remote Instance Monitoring Jobs on [' + @@SERVERNAME + '] started at ' + CONVERT(VARCHAR, GETDATE()) + '.';RAISERROR(@msg, 0, 0) WITH NOWAIT;
BEGIN TRANSACTION
SET @msg = ' Backup and/or create metadata table to hold list of remote instances to be monitored.';RAISERROR(@msg, 0, 0) WITH NOWAIT;
IF OBJECT_ID('dbo.CustomSqlMon', 'U') IS NOT NULLBEGINIF EXISTS (SELECT * FROM dbo.CustomSqlMon)BEGINSET @cmd = 'SELECT * INTO dbo.CustomSqlMon_' + CONVERT(VARCHAR, GETDATE(), 112) + REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '') + ' FROM [dbo].[CustomSqlMon]';EXEC sp_ExecuteSQL @cmd;END
DROP TABLE [dbo].[CustomSqlMon]; END
IF OBJECT_ID(N'[dbo].[CustomSqlMon]', 'U') IS NULLBEGINCREATE TABLE [dbo].[CustomSqlMon] (InstanceID INT IDENTITY(1,1) NOT NULL,InstanceName VARCHAR(128) NOT NULL,Active BIT NOT NULL,CheckSvcs BIT NOT NULL,ErrorLevel TINYINT NOT NULL,LastUpdate DATETIME NOT NULL,UpdatedBy VARCHAR(128) NOT NULL);
ALTER TABLE [dbo].[CustomSqlMon] ADD CONSTRAINT [PK_CustomSqlMon] PRIMARY KEY CLUSTERED ([InstanceID]);ALTER TABLE [dbo].[CustomSqlMon] ADD CONSTRAINT [UQ_CustomSqlMon_InstanceName] UNIQUE ([InstanceName]);ALTER TABLE [dbo].[CustomSqlMon] ADD CONSTRAINT [DF_CustomSqlMon_Active] DEFAULT (1) FOR [Active];ALTER TABLE [dbo].[CustomSqlMon] ADD CONSTRAINT [DF_CustomSqlMon_CheckSvcs] DEFAULT (0) FOR [CheckSvcs];ALTER TABLE [dbo].[CustomSqlMon] ADD CONSTRAINT [DF_CustomSqlMon_LastUpdate] DEFAULT (GETDATE()) FOR [LastUpdate];ALTER TABLE [dbo].[CustomSqlMon] ADD CONSTRAINT [DF_CustomSqlMon_UpdatedBy] DEFAULT (SUSER_SNAME()) FOR [UpdatedBy];
SET @cmd = 'CREATE TRIGGER [dbo].[utr_CustomSqlMon] ON [dbo].[CustomSqlMon]AFTER UPDATEASSET NOCOUNT ON
UPDATE lSET l.LastUpdate = GETDATE(), l.UpdatedBy = SUSER_SNAME()FROM [dbo].[CustomSqlMon] lINNER JOIN INSERTED i ON l.InstanceID = i.InstanceID;'EXEC sp_ExecuteSQL @cmd;END
-- Check for existence of specified operator; use failsafe operator if it doesn'tIF NOT EXISTS (SELECT * FROM [msdb].dbo.sysoperators WHERE [name] = @jobOper)BEGINSET @msg = 'Operator [' + @jobOper + '] not found; checking for failsafe operator.';RAISERROR(@msg, 0, 0) WITH NOWAIT;
EXEC [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\SQLServerAgent',N'AlertFailSafeOperator', @jobOper OUTPUT;END
IF @jobOper IS NULLBEGINSET @msg = 'No failsafe operator found; Job [' + @jobName + '] will not be created without notification functionality.';RAISERROR(@msg, 8, 0) WITH LOG, NOWAIT;GOTO QuitWithRollback;END
-- Create alert associated with custom error messageIF EXISTS (SELECT * FROM [msdb].dbo.sysalerts WHERE [name] = @jobAlert)EXEC [msdb].dbo.sp_delete_alert @name = @jobAlert;
EXEC [msdb].dbo.sp_add_alert @name = @jobAlert, @message_id = 64004, @severity = 0, @enabled = 1,@delay_between_responses = 0, @include_event_description_in = 3;
EXEC [msdb].dbo.sp_add_notification @alert_name = @jobAlert, @operator_name = @jobOper,@notification_method = 1;
-- Create job category if it doesn't already existIF NOT EXISTS (SELECT [name] FROM [msdb].dbo.syscategories WHERE [name] = @jobCat AND category_class = 1)BEGINEXEC @returnCode = [msdb].dbo.sp_add_category @class = N'JOB', @type = N'LOCAL', @name = @jobCat;IF (@@ERROR <> 0 OR @returnCode <> 0) GOTO QuitWithRollback;END
-- Remote instance monitor jobSELECT @jobDescr = N'Check connectivity to remote SQL instances', @jobId = NULL, @jobName = N'Monitor - Remote SQL Connectivity', @jobStep = @jobDescr;
IF EXISTS (SELECT * FROM [msdb].dbo.sysjobs WHERE [name] = @jobName)BEGINEXEC @returnCode = [msdb].dbo.sp_delete_job @job_name = @jobName, @delete_unused_schedule = 1;IF (@@ERROR <> 0 OR @returnCode <> 0) GOTO QuitWithRollback;END
IF @ver < 10BEGINSELECT @jobDB = 'VBScript', @jobType = 'ActiveScripting', @cmd = N'On Error Resume Next
set oConn = CreateObject("ADODB.Connection")set oRS = CreateObject("ADODB.RecordSet")
sSvr = "' + @svr + '"sDB = "' + @db + '"iWait = ' + @waitSec + '
sConStr = "Provider=SQLOLEDB;Server=" & sSvr & ";Database=" & sDB & ";Integrated Security=SSPI"oConn.Open sConStr
sSQL = "SELECT InstanceName, ErrorLevel FROM dbo.CustomSqlMon WHERE Active = 1 ORDER BY InstanceName"oRS.CursorLocation = 3oRS.Open sSQL, oConn
if Err.Number <> 0 thensOut = "Error querying dbo.CustomSqlMon."
sSQL = "RAISERROR(64004, 8, 1, ''" & sOut & "'') WITH LOG, NOWAIT"oConn.Execute sSQLelseErr.Clearif oRS.RecordCount = 0 thensOut = "The source table (dbo.CustomSqlMon) appears to be empty."
sSQL = "RAISERROR(64004, 8, 2, ''" & sOut & "'') WITH LOG, NOWAIT"oConn.Execute sSQLelsedo while not oRS.EOF sSvr = oRS("InstanceName").ValueiError = oRS("ErrorLevel").Value
iRC = fRemoteConn(sSvr, iWait)
select case iRCcase -1sOut = "ERROR: Unable to connect to " & sSvrcase -2sOut = "ERROR: Unable to query master.sys.databases on " & sSvrcase elsesOut = "Successfully connected to " & sSvr & ": " & cStr(iRC)iError = 0end select
if iRC < 0 thensSQL = "RAISERROR(64004, " & CStr(iError) & ", 3, ''" & sOut & "'') WITH LOG, NOWAIT"oConn.Execute sSQLend if
oRS.MoveNextloop end ifend if
set oRS = NothingoConn.Closeset oConn = Nothing

Private Function fRemoteConn (fRemoteInst, fWait)
On Error Resume Next
Set fConn = CreateObject("ADODB.Connection")Set fRS = CreateObject("ADODB.RecordSet")Err.Clear
fCount = 0fMax = 2
fConStr = "Provider=SQLOLEDB;Server=" & fRemoteInst & ";Database=master;Integrated Security=SSPI"fSQL = "SELECT COUNT(*) as dbCount FROM sys.databases"
Do While fCount < fMaxErr.ClearfConn.Open fConStrIf Err.Number <> 0 ThenfCount = fCount + 1fStart = Time()fEnd = DateAdd("s", fWait, fStart)
If fCount = fMax ThenfRemoteConn = -1Exit Functionset fRS = Nothingset fConn = NothingElseWhile fEnd >= Time()WendEnd IfElseErr.ClearExit DoEnd IfLoop
fRS.Open fSQL, fConnIf Err.Number <> 0 ThenfRemoteConn = -2Exit Functionset fRS = Nothingset fConn = NothingElsefRemoteConn = fRS("dbCount").ValueEnd If
Err.ClearSet fRS = NothingfConn.CloseSet fConn = Nothing
End Function'ENDELSEBEGINSELECT @jobDB = 'PowerShell', @jobType = @jobDB, @cmd = '$dbName = "' + @db + '";$ErrorActionPreference = "Stop";$retMsg = "";$sqlSvr = "' + @svr + '";$waitSec = ' + @waitSec + ';
$monTbl = New-Object System.Data.DataTable;$monTbl.Columns.Add("InstanceName") | Out-Null;$monTbl.Columns.Add("ErrorLevel", [int]) | Out-Null;
$sqlConn = New-Object System.Data.SqlClient.SqlConnection "Server = $sqlSvr;Database = $dbName;Integrated Security = SSPI";
$sqlCmd = $sqlConn.CreateCommand();
$sqlCmd.CommandText = @"SELECT InstanceName, ErrorLevelFROM dbo.CustomSqlMonWHERE [Active] = 1ORDER BY ErrorLevel DESC, InstanceName"@;
try{$sqlConn.Open();$sqlRdr = $sqlCmd.ExecuteReader();}catch{$retCode = -1;$retMsg = "Error querying the dbo.CustomSqlMon table in the " + $dbName + " database on " + $sqlSvr;}
while ($sqlRdr.Read()){$monRow = $monTbl.NewRow();
$monRow["InstanceName"] = $sqlRdr["InstanceName"];$monRow["ErrorLevel"] = $sqlRdr["ErrorLevel"];
$monTbl.Rows.Add($monRow);}
$sqlRdr.Close();
foreach ($monRow in $monTbl.Rows){$sqlInst = $monRow["InstanceName"];$errLev = $monRow["ErrorLevel"];
$alertCmd = $sqlConn.CreateCommand();
$remoteConn = New-Object System.Data.SqlClient.SqlConnection "Server = $sqlInst;Database = master;Integrated Security = SSPI";$remoteSQL = $remoteConn.CreateCommand();$remoteSQL.CommandText = "SELECT COUNT(*) AS [dbCount] FROM sys.databases";
$retry = 0;$max = 2;
$retCode = 0;$remoteDbCount = 0;
while ($retry -le $max){ if ($retCode -eq 0 -or $retCode -eq -2){try{$remoteConn.Open();$retCode = 1;}catch{$retCode = -2;$retMsg = "Error connecting to SQL Server instance " + $sqlInst;$retry = $retry + 1;}}
if ($retCode -eq 1 -or $retCode -eq -3){try{$remoteDbCount = $remoteSQL.ExecuteScalar();$remoteSQL.Dispose();$remoteConn.Close();$remoteConn.Dispose();$retCode = 0;break;}catch{$retCode = -3;$retMsg = "Error querying master database on " + $sqlInst;$retry = $retry + 1;}}
Start-Sleep -Seconds $waitSec;}
if ($retCode -eq 0 -and $remoteDbCount -gt 3){$SQL = "RAISERROR(''$retMsg'', 0, 0) WITH NOWAIT";$retMsg = "Successfully connected to " + $sqlInst + ".";$alertCmd.CommandText = $SQL;}else{$SQL = "RAISERROR(64004, " + $errLev + ", 0, ''$retMsg'') WITH LOG, NOWAIT";$alertCmd.CommandText = $SQL;}
$alertCmd.ExecuteNonQuery() | Out-Null;
$alertCmd.Dispose();}
$sqlConn.Close();'END
EXEC @returnCode = [msdb].dbo.sp_add_job @job_name = @jobName, @enabled = 0, @notify_level_eventlog = 2, @notify_level_email = 0, @notify_level_netsend = 0, @notify_level_page = 0, @delete_level = 0, @description = @jobDescr, @category_name = @jobCat, @owner_login_name = N'sa', @job_id = @jobId OUTPUT;IF (@@ERROR <> 0 OR @returnCode <> 0) GOTO QuitWithRollback;
EXEC @returnCode = [msdb].dbo.sp_add_jobstep @job_id = @jobId, @step_name = @jobStep, @step_id = 1, @cmdexec_success_code = 0, @on_success_action = 1, @on_success_step_id = 0, @on_fail_action = 2, @on_fail_step_id = 0, @retry_attempts = 0, @retry_interval = 0, @os_run_priority = 0, @subsystem = @jobType, @command = @cmd, @database_name = @jobDB, @flags = 0;IF (@@ERROR <> 0 OR @returnCode <> 0) GOTO QuitWithRollback;
EXEC @returnCode = [msdb].dbo.sp_update_job @job_id = @jobId, @start_step_id = 1;IF (@@ERROR <> 0 OR @returnCode <> 0) GOTO QuitWithRollback;
EXEC @returnCode = [msdb].dbo.sp_add_jobschedule @job_id = @jobId, @name = @jobStep, @enabled = 1, @freq_type = 4, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 15, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_start_date = 20000101, @active_end_date = 99991231, @active_start_time = 3200, @active_end_time = 235959;IF (@@ERROR <> 0 OR @returnCode <> 0) GOTO QuitWithRollback;
EXEC @returnCode = [msdb].dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)';IF (@@ERROR <> 0 OR @returnCode <> 0) GOTO QuitWithRollback;
SET @msg = ' Job [' + @jobName + '] created successfully.';RAISERROR(@msg, 0, 0) WITH NOWAIT;
-- Remote service monitor jobSELECT @jobDescr = N'Check status of services for remote SQL instances', @jobId = NULL, @jobName = N'Monitor - Remote SQL Services', @jobStep = @jobDescr;
IF EXISTS (SELECT * FROM [msdb].dbo.sysjobs WHERE [name] = @jobName)BEGINEXEC [msdb].dbo.sp_delete_job @job_name = @jobName, @delete_unused_schedule = 1;IF (@@ERROR <> 0 OR @returnCode <> 0) GOTO QuitWithRollback;END
IF @ver < 10BEGINSELECT @jobDB = 'VBScript', @jobType = 'ActiveScripting', @cmd = N'On Error Resume Next
set oConn = CreateObject("ADODB.Connection")set oRS = CreateObject("ADODB.RecordSet")
sSvr = "' + @svr + '"sDB = "' + @db + '"
sConStr = "Provider=SQLOLEDB;Server=" & sSvr & ";Database=" & sDB & ";Integrated Security=SSPI"oConn.Open sConStr
sSQL = "SELECT InstanceName, ErrorLevel FROM dbo.CustomSqlMon WHERE Active = 1 AND CheckSvcs = 1"oRS.CursorLocation = 3oRS.Open sSQL, oConn
if Err.Number <> 0 thensOut = "Error querying dbo.CustomSqlMon."
sSQL = "RAISERROR(64004, 8, 1, ''" & sOut & "'') WITH LOG, NOWAIT"oConn.Execute sSQLelseErr.Clearif oRS.RecordCount = 0 thensOut = "The source table (dbo.CustomSqlMon) appears to be empty."
sSQL = "RAISERROR(64004, 8, 2, ''" & sOut & "'') WITH LOG, NOWAIT"oConn.Execute sSQLelsedo while not oRS.EOFiError = oRS("ErrorLevel").ValuesSQL = ""sSvr = oRS("InstanceName").Value
iRC = fSvcCheck(sSvr)
select case iRCcase -4sOut = "ERROR: At least one SQL service failed to start for " & sSvrcase -3sOut = "ERROR: Empty service list returned for " & sSvrcase -2sOut = "ERROR: Could not execute WMI query against " & sSvrcase -1sOut = "ERROR: Could not make WMI connection to " & sSvrcase 0iError = 0sOut = "All SQL services appear to be running on " & sSvr & ": " & cStr(iRC)sSQL = "RAISERROR(''" & sOut & "'', " & CStr(iError) & ", 3) WITH NOWAIT"case ElseiError = 8sOut = "There were " & CStr(iRC) & " SQL service(s) that were restarted on " & sSvrend select
if sSQL = "" thensSQL = "RAISERROR(64004, " & CStr(iError) & ", 4, ''" & sOut & "'') WITH LOG, NOWAIT"end if
oConn.Execute sSQL
oRS.MoveNextloop end ifend if
set oRS = NothingoConn.Closeset oConn = Nothing

Private Function fSvcCheck (fRemoteInst)
On Error Resume Next
If InStr(fRemoteInst, "\") <> 0 ThenfSvr = Mid(fRemoteInst, 1, (InStr(fRemoteInst, "\") - 1))fInst = Mid(fRemoteInst, (InStr(fRemoteInst, "\") + 1), (Len(fRemoteInst) - (InStr(fRemoteInst, "\"))))ElsefSvr = fRemoteInstfInst = "default"End If
Err.ClearfCount = 0Set fWMI = GetObject("winmgmts:{impersonationLevel = impersonate}//" & fSvr & "/root/cimv2")If Err.Number <> 0 ThenfSvcCheck = -1Exit FunctionEnd If
Err.ClearSet fSvcList = fWMI.ExecQuery("SELECT * FROM Win32_Service WHERE PathName LIKE ''%SQL%''")If Err.Number <> 0 ThenfSvcCheck = -2Exit FunctionEnd If
Err.ClearFor Each fSvc In fSvcListfCount = fCount + 1Next
If fCount < 2 ThenfSvcCheck = -3Exit FunctionElsefCount = 0End If
Err.ClearFor Each fSvc In fSvcListfCode = 0
If ( (InStr(fSvc.Name, "$") <> 0 And InStr(fSvc.Name, fInst) <> 0) Or (fInst = "default" And InStr(fSvc.Name, "$") = 0) ) Then
If fSvc.StartMode = "Auto" And fSvc.State <> "Running" ThenfCount = fCount + 1
Select Case fSvc.StateCase "Stopped" fCode = fSvc.StartServiceCase "Paused" fCode = fSvc.ResumeServiceEnd Select
If fCode <> 0 ThenfSvcCheck = -4Exit FunctionEnd IfEnd IfEnd IfNext
Set fSvc = NothingSet fSvcList = NothingSet fWMI = NothingErr.ClearfSvcCheck = fCount
End Function'ENDELSEBEGINSELECT @jobDB = 'Powershell', @jobType = @jobDB, @cmd = '$dbName = "' + @db + '";$ErrorActionPreference = "Stop";$retCode = 0;$retMsg = "";$sqlSvr = "' + @svr + '";
$monTbl = New-Object System.Data.DataTable;$monTbl.Columns.Add("InstanceName") | Out-Null;$monTbl.Columns.Add("ErrorLevel", [int]) | Out-Null;
$sqlConn = New-Object System.Data.SqlClient.SqlConnection "Server = $sqlSvr;Database = $dbName;Integrated Security = SSPI";
$sqlCmd = $sqlConn.CreateCommand();
$sqlCmd.CommandText = @"SELECT InstanceName, ErrorLevelFROM dbo.CustomSqlMonWHERE [Active] = 1AND CheckSvcs = 1ORDER BY ErrorLevel DESC, InstanceName"@;
try{$sqlConn.Open();$sqlRdr = $sqlCmd.ExecuteReader();}catch{$retCode = -1;$retMsg = "Error querying the dbo.CustomSqlMon table in the " + $dbName + " database on " + $sqlSvr;}
while ($sqlRdr.Read() -and $retCode -eq 0){$monRow = $monTbl.NewRow();
$monRow["InstanceName"] = $sqlRdr["InstanceName"];$monRow["ErrorLevel"] = $sqlRdr["ErrorLevel"];
$monTbl.Rows.Add($monRow);}
$sqlRdr.Close();
foreach ($monRow in $monTbl.Rows){$svcList = "";$svcCount = 0;$sqlInst = $monRow["InstanceName"];$errLev = $monRow["ErrorLevel"];
if ($sqlInst.Contains("\")){$l = $sqlInst.IndexOf("\");$Svr = $sqlInst.Substring(0, $l);
$i = ($sqlInst.IndexOf("\") + 1);$l = ($sqlInst.Length - $i);$Inst = "$" + $sqlInst.Substring($i, $l);}else{$Svr = $sqlInst;$Inst = "default";}
try{$svcCont = Get-WmiObject -Computer $Svr -Class Win32_Service -Filter "Name LIKE ''%SQL%''";}catch{$retCode = -2;$retMsg = "Error executing WMI service query against " + $Svr;break;}
foreach ($svc in $svcCont){$svcName = $svc.Name;
$alertCmd = $sqlConn.CreateCommand();
if (($Inst.Contains("$") -and $svcName.Contains($Inst)) -or ($Inst -eq "default" -and -not ($svcName.Contains("$")))){if ($svc.StartMode -eq "Auto" -and -not ($svc.State -eq "Running")){$svcCount = $svcCount + 1;
if ($svcList -eq ""){ $svcList = $svcName; }else{ $svcList = $svcList + ", " + $svcName; }
try{if ($svc.State -eq "Paused"){ $svc.ResumeService() }else{ $svc.StartService() }}catch{$retCode = -3;$retMsg = "The " + $svcName + " service on " + $Svr + " is set to AutoStart but is not currently running. Attempt to restart it failed.";break;}}}}
if ($retCode -eq 0){if ($svcCount -eq 0){$retMsg = "All SQL-related services set to AutoStart for " + $sqlInst + " are currently running."; }else{$retMsg = "The following SQL-related services were successfully restarted for " + $sqlInst + ": " + $svcList;
$SQL = "RAISERROR(''$retMsg'', 0, " + $errLev + ") WITH LOG, NOWAIT";$alertCmd.CommandText = $SQL;
$alertCmd.ExecuteNonQuery()| Out-Null;
$alertCmd.Dispose();}}}
$sqlConn.Close();'END
EXEC @returnCode = [msdb].dbo.sp_add_job @job_name = @jobName, @enabled = 0, @notify_level_eventlog = 2, @notify_level_email = 0, @notify_level_netsend = 0, @notify_level_page = 0, @delete_level = 0, @description = @jobDescr, @category_name = @jobCat, @owner_login_name = N'sa', @job_id = @jobId OUTPUT;IF (@@ERROR <> 0 OR @returnCode <> 0) GOTO QuitWithRollback;
EXEC @returnCode = [msdb].dbo.sp_add_jobstep @job_id = @jobId, @step_name = @jobStep, @step_id = 1, @cmdexec_success_code = 0, @on_success_action = 1, @on_success_step_id = 0, @on_fail_action = 2, @on_fail_step_id = 0, @retry_attempts = 0, @retry_interval = 0, @os_run_priority = 0, @subsystem = @jobType, @command = @cmd, @database_name = @jobDB, @flags = 0;IF (@@ERROR <> 0 OR @returnCode <> 0) GOTO QuitWithRollback;
EXEC @returnCode = [msdb].dbo.sp_update_job @job_id = @jobId, @start_step_id = 1;IF (@@ERROR <> 0 OR @returnCode <> 0) GOTO QuitWithRollback;
EXEC @returnCode = [msdb].dbo.sp_add_jobschedule @job_id = @jobId, @name = @jobStep, @enabled = 1, @freq_type = 4, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 15, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_start_date = 20000101, @active_end_date = 99991231, @active_start_time = 2800, @active_end_time = 235959;IF (@@ERROR <> 0 OR @returnCode <> 0) GOTO QuitWithRollback;
EXEC @returnCode = [msdb].dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)';IF (@@ERROR <> 0 OR @returnCode <> 0) GOTO QuitWithRollback;
SET @msg = ' Job [' + @jobName + '] created successfully.';RAISERROR(@msg, 0, 0) WITH NOWAIT;
IF @@TRANCOUNT > 0COMMIT TRANSACTION;
SET @msg = 'Creation of Remote Instance Monitoring Jobs on [' + @@SERVERNAME + '] completed at ' + CONVERT(VARCHAR, GETDATE()) + '.';RAISERROR(@msg, 0, 0) WITH NOWAIT;
RETURN;
QuitWithRollback:BEGINIF @@TRANCOUNT > 0ROLLBACK TRANSACTION;
SET @msg = 'Creation of Remote Instance Monitoring Jobs on [' + @@SERVERNAME + '] failed at ' + CONVERT(VARCHAR, GETDATE()) + '.';RAISERROR(@msg, 0, 0) WITH NOWAIT;END
GO