Sometimes we, as data professionals, have to think outside the box. I know, crazy idea right? Each shop and situation are different; there will always be several different ways in most cases that you can arrive at a solid solution.

This post has a few intentions behind it:

• It is not a “take this solution; it’s the only way”.
• Generate some thought and additional methods to reach a goal.
• This is not intended for a production environment.

Good, now that we have those few things out-of-the-way let’s get to the meat of the topic. A situation arises where you want to give a bit more control to teams to execute jobs without giving full access to the SQL agent. In that case a good utility database may come in handy.

Example of an issue: A QA team is in need of kicking jobs off to test in a specific environment. Keeping in mind that each shop can be different this also means that security levels at varying shops will be different. There are a few choices that may come to mind with this issue:

• Fire off an email to the DBA team and wait for them to kick job off.
• Fire off an email to someone with access and wait for them to kick the job off.
• Wait for the predefined schedule on the job agent and let it kick the job off.

Another method would be to utilize a utility database. You can give it whatever name meets your criteria in this case we will just call it TestingJobs. Let’s look at the overall picture below and how this all fits together:

Things you’ll need

• UtilityDatabase
• Two Stored Procedures
• Table
• Agent Job

Step1: Create the TestingJobs database (I won’t go into specifics here on proper set up; assume this is already created).

Step2: Create a table called ControlJobs inside the TestingJobs database

USE [TestingJobs]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[ControlJobs](

[JobControlID] [INT] IDENTITY(1,1) NOT NULL,

[JobName] [VARCHAR](500) NOT NULL,

[RunStatus] [BIT] NOT NULL DEFAULT ((0)),

[LastRanBy] [VARCHAR](50) NOT NULL,

[LastRanByApp] [VARCHAR](150) NULL,

[Date_Modified] [DATETIME] NOT NULL DEFAULT (GETDATE()),

[Active] [BIT] NOT NULL DEFAULT ((1))

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

Step3: Store procedure creation for table insertion (note the parameter @JobName)

USE [TestingJobs]
GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[test_StartJobs] ( @JobName VARCHAR(100) )
AS

BEGIN
        /************************************************************************

This script will insert the record needed to kick off agent jobs.

        ************************************************************************/

INSERT  INTO [TestingJobs].[dbo].[ControlJobs]

( [JobName] ,

[RunStatus] ,

[LastRanBy] ,

[LastRanByApp] ,

[Date_Modified] ,

[Active]

)
VALUES  ( @JobName ,

1 ,

” ,

” ,
GETDATE() ,

1

);

END;

Step4: Set up stored procedure that will run the pending jobs.

USE [TestingJobs]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[RunPendingJobs]
AS

SET NOCOUNT ON;

    DECLARE @JobName VARCHAR(500) ,

@JobStatus INT ,

@RC INT;

DECLARE cur_RunJobs CURSOR

FOR
        SELECT  JobName
FROM    Ddbo.ControlJobs
WHERE   RunStatus = 1
ORDER BY JobName;

OPEN cur_RunJobs;

FETCH NEXT FROM cur_RunJobs
INTO @JobName;

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ‘Checking to see if job is currently running. ‘;

EXEC @RC = dbo.GetCurrentRunStatus @job_name = @JobName;

IF @RC = 0
EXEC msdb.dbo.sp_start_job @JobName;
ELSE

PRINT @JobName + ‘ is currently running.’;

UPDATE  ControlJobs
SET     RunStatus = 0 ,

Date_Modified = GETDATE()
WHERE   JobName = @JobName;

FETCH NEXT FROM cur_RunJobs INTO @JobName;

END;

CLOSE cur_RunJobs;
DEALLOCATE cur_RunJobs;

Step5: Set up stored procedure to check if job is already running

USE [TestingJobs];
GO

SET ANSI_NULLS ON;

GO

SET QUOTED_IDENTIFIER ON;

GO

ALTER PROCEDURE [dbo].[GetCurrentRunStatus] ( @job_name sysname )
AS

SET NOCOUNT ON;
    /* Is the execution status for the jobs.

Value Description

0 Returns only those jobs that are not idle or suspended.

1 Executing.

2 Waiting for thread.

3 Between retries.

4 Idle.

5 Suspended.

7 Performing completion actions  */

DECLARE @job_id UNIQUEIDENTIFIER ,

@is_sysadmin INT ,

@job_owner sysname ,

@Status INT;

SELECT  @job_id = job_id
FROM    msdb..sysjobs_view
WHERE   [name] = @job_name;
SELECT  @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N’sysadmin’), 0);
SELECT  @job_owner = SUSER_SNAME();

CREATE TABLE #xp_results

(

job_id UNIQUEIDENTIFIER NOT NULL ,

last_run_date INT NOT NULL ,

last_run_time INT NOT NULL ,

next_run_date INT NOT NULL ,

next_run_time INT NOT NULL ,

next_run_schedule_id INT NOT NULL ,

requested_to_run INT NOT NULL , — BOOL

request_source INT NOT NULL ,

request_source_id sysname COLLATE DATABASE_DEFAULT

NULL ,

running INT NOT NULL , — BOOL

current_step INT NOT NULL ,

current_retry_attempt INT NOT NULL ,

job_state INT NOT NULL

);
INSERT  INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner,

@job_id;
SELECT  @Status = running
FROM    #xp_results;
RETURN @Status;

DROP TABLE #xp_results;

SET NOCOUNT OFF;

Step6: Job Creation

Create a SQL agent job that will call the RunPendingJobs in the database. You can set this schedule to three minutes for this test.

The Benefit

Now think of a QA team member sitting at their desk running multiple tasks. This does take some coordinated effort in getting the job names but now that the basics are set up the team member could run the execute command for the test_StartJobs which will place the necessary information into the control jobs table. Of course the proper security would need to be set up in order for the user to be added (think AD groups). By utilizing the above method the team can suffice on it’s own in a non prod environment streamlining some of the inefficiencies that have plagued the groups in the past.

Summary

A few things to note here:

• Don’t ever take code off the internet without testing it. This is just a thought-provoking post and there are some things in this post that are dependent upon one to set up and test.
• I realize there are multiple ways to accomplish this. This is just an avenue to explore and test with some thinking outside the box.
• Don’t limit yourself to “I can’t” or “This will not fly at my shop”; challenge yourself to become innovative and think of ways to tackle problems.