Steve's SQL Server Blog

Execute all jobs in same job category

I wonder why I couldn't find a script on the Internet which generates statements to execute all jobs in the same job category.
The only reason I could think of is that it's simple to write so here goes my version

SET NOCOUNT ON 

USE tempdb
GO

DECLARE @Category AS VARCHAR(255)
SET @Category = 'Database Maintenance'

SELECT a.name
INTO #jobsincategory
FROM [msdb].[dbo].[sysjobs] a
LEFT JOIN [msdb].[dbo].[syscategories] b
ON a.category_id = b.category_id
WHERE b.name = @Category

 

DECLARE jobsincategory CURSOR READ_ONLY FOR

SELECT *
FROM #jobsincategory

DECLARE @name VARCHAR(40)

OPEN jobsincategory

FETCH NEXT FROM jobsincategory
INTO @name
WHILE (@@FETCH_STATUS <> -1)

BEGIN

IF (@@FETCH_STATUS <> -2)
BEGIN

DECLARE @message VARCHAR(100)

SELECT @message = 'exec msdb..sp_start_job @job_name =''' + @name + ''''
PRINT @message

END

FETCH NEXT FROM jobsincategory
INTO @name

END

CLOSE jobsincategory
DEALLOCATE jobsincategory

GO

DROP TABLE #jobsincategory

The outcome looks like this:

AS400 Administrators vs SQL Server DBA's

AS400 people tend to be old, they could be your dad but the earn a lot respect. They're usually good in what they do.

Until you confront them with SQL. For some reason the AS400 folks think completely different then DBA's and I'm not pointing any finger at DB2 guys. Let me clarify: I've been in two discussions where I was asked to import data from a member file on DB2. According to the AS400 people, your query should look something like the following:

SELECT * FROM LIBRARYNAME.FILENAME.MEMBERNAME

What's wrong with this picture? In SQL this would go totally wrong as the syntax is:

SELECT * FROM DATABASENAME.OWNER.TABLENAME

SQL will think the FILENAME is actually the OWNER and this is where it goes wrong (not only in the result but also in your discussion with the AS400 guys)
Luckilly there is a simple and clean workaround...ALIASSES

The syntax goes like this:

CREATE ALIAS Foobar FOR FUZZYBEAR

where FUZZYBEAR is actually the member file so the SQL quys can query the alias.

Steve