Link to home
Start Free TrialLog in
Avatar of dbaSQL
dbaSQLFlag for United States of America

asked on

Re-appearing SQL Server Agent jobs

I asked a question not too long ago about what would cause SQL Server Agent jobs to re-appear after they had been deleted.  I was basically told by an Expert that because I deleted the jobs using DELETE sysjobs WHERE..., rather than using sp_delete_job, I was doing it an unsupported way, and nothing could be done.

Now I have the same problem again (same customer), where previously deleted SQL Server Agent jobs were removed, but they are magically re-appearring.  Only this time, I am using sp_delete_job, as you can see from the attached script.

Per the customer, "after running the attached script, I verified that the jobs were gone.  Yet, by the next morning they're back. They don't produce output because I have long since deleted the folders they write to".

They are SSRS Subscription Agent jobs.  Can anybody tell me why they are re-appearing after being removed?
DeleteJobs.sql
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dbaSQL

ASKER

There is no TFS and no other schduled job.  The jobs are SSRS-based, so I believe it must be somehow correlated with the subscriptions.  In fact, I am sure that is it, because every time a sub runs, the agent job is created, if not already exists.

That must be it.  I don't know why I did not think about that.  Thank you, jim!!!  I simply need to remove the subscriptions, too.
Avatar of dbaSQL

ASKER

Now just to correlate the job_id's to the subscriptionIDs.... I think using the script I previously uploaded, I could add the job_ids into the @jobs table along with the names.  Then I could use that to identify (and remove) the related subscriptionID.  That's what I'm working, I will send status when I've got it.
Avatar of dbaSQL

ASKER

Hi Jim.  Can you help out?  See the code, I am only trying to associate the job with its subscription, so that it can then be removed.  I thought I finally had it, but the SubscriptionID is the same for all values returned in this construct.  My join isn't quite right.

Do you see it?


declare @Jobs table (
	JobName	varchar(50),SupbscriptionID varchar(36),JobStatus int	)

-- the following are 42 closed parish jobs:
insert into @Jobs (jobname,jobstatus)
values	('1F6D56BB-915A-41E3-8F4D-9548408EC842', 0),
('9740ED7F-7D1B-4FB2-86C9-D9CFBC806701', 0),
('A7386060-D522-4D6B-BB46-32F0E774EA5C', 0),
('DDAF7682-D9BA-49EF-BC08-BBA55CEB0DBF', 0),
('E7780320-0BB9-43E9-8F0A-15DE19DEBCF0', 0),
('ED2B2D22-875E-4BB8-96CD-9C300DB4DEBE', 0),
('4DEF774F-B208-4719-BB2F-6B2A38314ECC', 0),
('71DEE13A-241D-4DF8-A7A4-F0FF3BA92331', 0),
('A9BA39A8-FBF6-4931-A346-2FF3D8681AF3', 0),
('C92BBA6C-A878-44D9-98E4-1CB4423F66E9', 0),
('DB1A0DE8-30F7-4EEF-8049-09ABF8BA697D', 0),
('FBB80ADB-4F9B-4E7A-9BCD-FB3EDC472422', 0),
('2B367512-2C55-4246-8C94-91DAED75EA32', 0),
('6773E903-B520-453C-8185-2D2963BF024E', 0),
('B4F67D9F-11CC-4CB8-BE92-D4CECF8433C4', 0),
('ED1133C2-0FB8-43F9-B78A-45AC14C83D92', 0),
('F238CC8F-8B12-4C9F-8E23-804EF0D9A330', 0),
('F454DDBF-C3D1-45A7-9576-7F64A8DCD7C7', 0),
('406A1BC6-047C-4F32-9EF6-0AB63436F3AF', 0),
('4CA2C0D0-83BC-47D3-B66E-F6DE93F83753', 0),
('5EE6AC0A-7E60-4A07-A78D-C090D3D088BC', 0),
('737EA669-6526-4C2C-A567-B0CE21A8C4C7', 0),
('A1B50ED2-42CD-45EB-8AAC-36ABF9A4A574', 0),
('CF153700-D4FB-4989-BDDD-94BF6C229ECC', 0),
('535FEED3-F9FD-4D4B-A586-03E71B44E20D', 0),
('5445350A-43A8-4EE7-BBB6-7EE15146A577', 0),
('55CD8F71-25F9-4359-9779-16EFF03EB0B5', 0),
('76FFD517-2B52-4392-B1EC-4DDEBB3A395A', 0),
('A43E07B2-BBF7-4B04-8080-8EFFA0CDDB18', 0),
('D7359A5E-1CE8-4709-8587-B276434C2C91', 0),
('2EA3E304-F3F3-493B-A0FD-A8AAFF961E61', 0),
('36BAE3D5-5F86-4F73-ABC6-43841C4FB37A', 0),
('4EEE492C-DCEF-45E4-8EED-D17B53AF38EE', 0),
('7FEE0226-4A3C-45A3-AA0E-DECBB7D757E0', 0),
('B8F69C1D-CE6D-4174-B657-DCD29AA0922C', 0),
('E2AB28BB-6CFC-4ED3-8AA9-F87936DD5465', 0),
('27050324-9495-4811-BFAA-E3B290DFC50B', 0),
('600A5E93-2594-40A5-9279-45D3EA5304CC', 0),
('6510564C-9456-4B51-BC54-E8F4526D3ED1', 0),
('7DE13B6F-1962-42A6-8C7D-FFD383DC8E3F', 0),
('DEE2FE1A-C489-4FBA-86AF-FA19710170B1', 0),
('FEC9F0C6-D57A-488D-84BD-0329C8412E6D', 0),
-- the following are the 6 duplicate parish ,jobs
('F8533BDF-D67B-471B-97B6-F677AB09D07E', 0),
('4C9B44CA-CDE2-4CF6-B5D6-7E1EE54D8997', 0),
('D7027F69-48CF-4ED1-AFEF-A328AD1CBECA', 0),
('6B13C3C8-86D5-4B10-AB4C-6E09CD17F2F2', 0),
('5D430432-5AEE-48F3-B45B-7B832E234A5E', 0),
('F0EFC956-7FCC-44CB-895A-4BC1C047CADD', 0),
('', 0)

--select count(*) [TotalJobs] from @Jobs

UPDATE @jobs 
SET SupbscriptionID = CONVERT(VARCHAR(36),sub.SubscriptionID)
FROM ReportServer.dbo.ReportSchedule rs INNER JOIN ReportServer.dbo.Schedule s 
  ON rs.ScheduleID = s.ScheduleID INNER JOIN ReportServer.dbo.Subscriptions sub 
    ON rs.SubscriptionID = sub.SubscriptionID
WHERE EXISTS(SELECT 1 FROM @Jobs j JOIN msdb..sysjobs sj
			   ON j.JobName collate SQL_Latin1_General_CP1_CI_AS = sj.name collate SQL_Latin1_General_CP1_CI_AS
			 WHERE CONVERT(VARCHAR(36),s.ScheduleID) = j.JobName)

SELECT * FROM @jobs

Open in new window

Avatar of dbaSQL

ASKER

Jim, do you see the flaw in my logic?
Avatar of dbaSQL

ASKER

I've corrected my join.  All is well.  Thank you for the help, Jim. Much appreciated.
Avatar of dbaSQL

ASKER

No.  I thought I had it right, but the deletion of the subscriptions did not complete correctly.  Are there any SSRS Experts who can help me to properly associate a handful of jobs to the appropriate Subscriptions, so they can be deleted while using sp_delete_jobs to remove the job?  The code that I've copied in does everything except remove the subscription.  It failed to do so with this error:
   Cursor Fetch: Implicit conversion from data type uniqueidentifier to int is not allowed.

This is the problem spot:  
            DELETE a                                                            
            FROM dbo.Subscriptions a INNER JOIN #jobsToProcess jtp
              ON a.SubscriptionID  = jtp.SubscriptionID
            WHERE jtp.JobStatus = 1

To correct it, I changed it to this:

            DELETE a                                                            
            FROM dbo.Subscriptions a INNER JOIN #jobsToProcess jtp
              ON CAST(a.SubscriptionID AS VARCHAR(40)) = jtp.SubscriptionID
            WHERE jtp.JobStatus = 1

After the failure, I wrote the 48 jobs back into sysjobs (from JobsTMP that is commented out in the code) -- but now the code as I've posted it is not writing anything to #jobsToProcess.  It only writes the jobnames into the @jobs table variable.

Jim, I'm unsure if you will see this because I closed it prematurely.  Please let me know if you do, and whether you'd like me to open a new one.

SET NOCOUNT ON;

DECLARE @Jobs TABLE (JobName VARCHAR(50))
DECLARE @JobErrors TABLE (
	JobName VARCHAR(50),
	Job_ERROR_NUMBER INT,
    Job_ERROR_SEVERITY INT,
    Job_ERROR_STATE INT,
    Job_ERROR_MESSAGE VARCHAR(255))

-- the following are 42 closed parish jobs:
INSERT @Jobs (jobname)
VALUES('1F6D56BB-915A-41E3-8F4D-9548408EC842'),
('9740ED7F-7D1B-4FB2-86C9-D9CFBC806701'),
('A7386060-D522-4D6B-BB46-32F0E774EA5C'),
('DDAF7682-D9BA-49EF-BC08-BBA55CEB0DBF'),
('E7780320-0BB9-43E9-8F0A-15DE19DEBCF0'),
('ED2B2D22-875E-4BB8-96CD-9C300DB4DEBE'),
('4DEF774F-B208-4719-BB2F-6B2A38314ECC'),
('71DEE13A-241D-4DF8-A7A4-F0FF3BA92331'),
('A9BA39A8-FBF6-4931-A346-2FF3D8681AF3'),
('C92BBA6C-A878-44D9-98E4-1CB4423F66E9'),
('DB1A0DE8-30F7-4EEF-8049-09ABF8BA697D'),
('FBB80ADB-4F9B-4E7A-9BCD-FB3EDC472422'),
('2B367512-2C55-4246-8C94-91DAED75EA32'),
('6773E903-B520-453C-8185-2D2963BF024E'),
('B4F67D9F-11CC-4CB8-BE92-D4CECF8433C4'),
('ED1133C2-0FB8-43F9-B78A-45AC14C83D92'),
('F238CC8F-8B12-4C9F-8E23-804EF0D9A330'),
('F454DDBF-C3D1-45A7-9576-7F64A8DCD7C7'),
('406A1BC6-047C-4F32-9EF6-0AB63436F3AF'),
('4CA2C0D0-83BC-47D3-B66E-F6DE93F83753'),
('5EE6AC0A-7E60-4A07-A78D-C090D3D088BC'),
('737EA669-6526-4C2C-A567-B0CE21A8C4C7'),
('A1B50ED2-42CD-45EB-8AAC-36ABF9A4A574'),
('CF153700-D4FB-4989-BDDD-94BF6C229ECC'),
('535FEED3-F9FD-4D4B-A586-03E71B44E20D'),
('5445350A-43A8-4EE7-BBB6-7EE15146A577'),
('55CD8F71-25F9-4359-9779-16EFF03EB0B5'),
('76FFD517-2B52-4392-B1EC-4DDEBB3A395A'),
('A43E07B2-BBF7-4B04-8080-8EFFA0CDDB18'),
('D7359A5E-1CE8-4709-8587-B276434C2C91'),
('2EA3E304-F3F3-493B-A0FD-A8AAFF961E61'),
('36BAE3D5-5F86-4F73-ABC6-43841C4FB37A'),
('4EEE492C-DCEF-45E4-8EED-D17B53AF38EE'),
('7FEE0226-4A3C-45A3-AA0E-DECBB7D757E0'),
('B8F69C1D-CE6D-4174-B657-DCD29AA0922C'),
('E2AB28BB-6CFC-4ED3-8AA9-F87936DD5465'),
('27050324-9495-4811-BFAA-E3B290DFC50B'),
('600A5E93-2594-40A5-9279-45D3EA5304CC'),
('6510564C-9456-4B51-BC54-E8F4526D3ED1'),
('7DE13B6F-1962-42A6-8C7D-FFD383DC8E3F'),
('DEE2FE1A-C489-4FBA-86AF-FA19710170B1'),
('FEC9F0C6-D57A-488D-84BD-0329C8412E6D'),
-- the following are the 6 duplicate parish ,jobs
('F8533BDF-D67B-471B-97B6-F677AB09D07E'),
('4C9B44CA-CDE2-4CF6-B5D6-7E1EE54D8997'),
('D7027F69-48CF-4ED1-AFEF-A328AD1CBECA'),
('6B13C3C8-86D5-4B10-AB4C-6E09CD17F2F2'),
('5D430432-5AEE-48F3-B45B-7B832E234A5E'),
('F0EFC956-7FCC-44CB-895A-4BC1C047CADD');
SELECT COUNT(*) [@jobs] FROM @jobs

-- put it into #jobsToProcess with the SubscriptionID
IF OBJECT_ID('tempdb..#jobsToProcess') IS NOT NULL
DROP TABLE #jobsToProcess;
SELECT CAST(sj.name AS VARCHAR(40))[JobName],sub.SubscriptionID,0 [JobStatus]
INTO #jobsToProcess
FROM msdb.dbo.sysjobs sj INNER JOIN msdb.dbo.sysjobsteps js 
  ON sj.job_id = js.job_id INNER JOIN @jobs j1 
    ON sj.name COLLATE SQL_Latin1_General_CP1_CI_AS = j1.JobName COLLATE SQL_Latin1_General_CP1_CI_AS INNER JOIN dbo.Schedule sc
	  ON CAST(sc.ScheduleID AS VARCHAR(40)) = j1.JobName INNER JOIN dbo.ReportSchedule rs
	    ON sc.ScheduleID = rs.ScheduleID INNER JOIN dbo.Subscriptions sub
		  ON rs.SubscriptionID = sub.SubscriptionID 

-- create bkup tables
--IF OBJECT_ID('JobsTMP','U') IS NOT NULL
--DROP TABLE dbo.JobsTMP;
--SELECT * INTO JobsTMP FROM msdb.dbo.sysjobs 
--WHERE name IN(SELECT JobName FROM #jobsToProcess)
--IF OBJECT_ID('SubscriptionsTMP','U') IS NOT NULL
--DROP TABLE dbo.SubscriptionsTMP;
--SELECT * INTO SubscriptionsTMP FROM ReportServer.dbo.Subscriptions 
--WHERE SubscriptionID IN(SELECT SubscriptionID FROM #jobsToProcess)

--SELECT COUNT(*) FROM dbo.JobsTMP
--SELECT COUNT(*) FROM dbo.SubscriptionsTMP

-- declare our cursor for the job and subscription removal	
DECLARE 
	@JobName VARCHAR(50),
	@SubscriptionID VARCHAR(40),
	@JobStatus INT,
	@JobCursor CURSOR

SET @JobCursor = CURSOR FOR
SELECT JobName, CAST(SubscriptionID AS VARCHAR(40)) [SubscriptionID], JobStatus
FROM #jobsToProcess FOR UPDATE OF JobStatus

OPEN @JobCursor
FETCH NEXT FROM @JobCursor INTO @JobName,@SubscriptionID,@JobStatus
WHILE @@FETCH_STATUS = 0
BEGIN
	BEGIN TRY
		-- remove job
		EXEC msdb.dbo.sp_delete_job @job_name = @JobName
		-- update #jobsToProcess.JobStatus
		UPDATE #jobsToProcess SET JobStatus = 1 WHERE CURRENT OF @JobCursor
		-- remove related subscription
		DELETE a										
		FROM dbo.Subscriptions a INNER JOIN #jobsToProcess jtp
		  ON a.SubscriptionID = jtp.SubscriptionID
		WHERE jtp.JobStatus = 1
	END TRY
	BEGIN CATCH	-- set status to zero if an error occurs trying to delete job
		UPDATE #jobsToProcess SET JobStatus = 0 WHERE CURRENT OF @JobCursor

		INSERT INTO @JobErrors VALUES(-- write errors, if necessary
			@JobName,
			ERROR_NUMBER(),
			ERROR_SEVERITY(),
			ERROR_STATE(),
			ERROR_MESSAGE()
		)

	END CATCH
	FETCH NEXT FROM @JobCursor INTO @JobName, @SubscriptionID, @JobStatus
END

CLOSE @JobCursor;
DEALLOCATE @JobCursor;


SELECT COUNT(*) [JobsDeleted] FROM #jobsToProcess WHERE JobStatus = 1
SELECT COUNT(*) [Job_NotDeleted] FROM #jobsToProcess WHERE JobStatus = 0
SELECT JobName, Job_ERROR_MESSAGE FROM @JobErrors

SET NOCOUNT OFF;

Open in new window