PROCEDURE TO CHECK IF A BACKUP IS BEING TAKEN ON A SERVER WHICH IS LINKED TO THE CENTRAL SERVER AND IS ACTIVE

CREATE procedure [dbo].[DBADS_SP_CHECK_JOB_PROGRESS]

@SERVERNAME VARCHAR(200),
@BACKUPJOBRUNNING INT OUTPUT

AS



DECLARE
@JOB_ID VARCHAR(100),
@RUNNABLE INT,
@CMD VARCHAR(2000),
@CMD1 VARCHAR(2000)

SET @RUNNABLE = 0
SET @BACKUPJOBRUNNING = 0


IF (SELECT IS_LINKED FROM sys.servers WHERE NAME = @SERVERNAME)= 1
BEGIN
IF (SELECT RETIRED FROM dbo.servers WHERE SERVERNAME = @SERVERNAME) = '9999-12-31 00:00:00.000'
BEGIN
/* CREATE A GLOBAL TEMP TABLE AND INSERT THE DATA FROM SYSJOBS*/

IF OBJECT_ID('tempdb..##TEMP') IS NOT NULL
DROP TABLE ##TEMP

CREATE TABLE ##TEMP (JOB_ID VARCHAR(100), RUNNING VARCHAR(100))

SET @CMD1 = 'INSERT INTO ##TEMP(JOB_ID)
SELECT
JOB_ID
FROM
['+ @SERVERNAME + '].msdb.dbo.sysjobs
WHERE
UPPER(NAME) LIKE ''%COMPLETE%'''

EXEC(@CMD1)

DECLARE CURSORBACKUP CURSOR

FOR
SELECT
JOB_ID
FROM
##TEMP

OPEN CURSORBACKUP
FETCH NEXT FROM CURSORBACKUP INTO @JOB_ID

WHILE @@FETCH_STATUS =0
BEGIN
/* EACH TIME A JOB_ID IS PASSED INTO THE PROCEDURE TO SEE IF IT RETURNS ANY ROWS*/

SET @CMD = 'DECLARE @ROW_COUNT INT
exec ['+ @SERVERNAME + '].msdb.dbo.sp_get_composite_job_info @job_id =''' + @JOB_ID + ''',
 @execution_status = 1
SET @ROW_COUNT =  @@ROWCOUNT

IF @ROW_COUNT  >= 1
BEGIN
UPDATE ##TEMP
SET RUNNING = 1
WHERE JOB_ID = '''+ @JOB_ID + '''
END'

--print @CMD
EXEC(@CMD)

FETCH NEXT FROM CURSORBACKUP INTO @JOB_ID

END

CLOSE CURSORBACKUP
DEALLOCATE CURSORBACKUP

/* IF THERE ARE BACKUPS RUNNNING, SET THE OUTPUT PARAM*/

SELECT
@RUNNABLE =  COUNT(*)
FROM
##TEMP
WHERE
RUNNING = 1

IF @RUNNABLE > 0
BEGIN
SET @BACKUPJOBRUNNING = 1
END
END
ELSE
BEGIN
RETURN
END
END
ELSE
BEGIN
RETURN
END