Cancelling a running SSRS timed subscription

Question

I have several timed subscriptions that are running and I would like to cancel them. I looked at the SQL Server agent and the jobs are not in there. Does anyone know of a command in SQL Server that I can execute to kill/cancel the jobs?

I'm using this code to look at the subscriptions I want to cancel:

SELECT USR.UserName AS SubscriptionOwner 
,sub.SubscriptionID
  ,SUB.ModifiedDate 
  ,SUB.[Description] 
  ,SUB.EventType 
  ,SUB.DeliveryExtension 
  ,SUB.LastStatus 
  ,SUB.LastRunTime 
  ,SCH.NextRunTime 
  ,SCH.Name AS ScheduleName       
  ,CAT.[Path] AS ReportPath 
  ,CAT.[Description] AS ReportDescription 
FROM dbo.Subscriptions AS SUB 
 INNER JOIN dbo.Users AS USR 
     ON SUB.OwnerID = USR.UserID 
 INNER JOIN dbo.[Catalog] AS CAT 
     ON SUB.Report_OID = CAT.ItemID 
 INNER JOIN dbo.ReportSchedule AS RS 
     ON SUB.Report_OID = RS.ReportID 
        AND SUB.SubscriptionID = RS.SubscriptionID 
 INNER JOIN dbo.Schedule AS SCH 
     ON RS.ScheduleID = SCH.ScheduleID 
      where SUB.EventType = 'TimedSubscription'
      and Sch.name ='Participation Reports'
 ORDER BY CAT.[Path];

I'm confused - so the jobs were there, but were removed and the subscriptions are currently running? Are you sure the jobs are not under the agent? What version of SQL Server are you running? SQL Server creates guids for job names (if memory serve me correctly), so you could also look at all jobs and match those up by what scheduled ones you are looking for? — LaraRaraBoBara, Aug 02 '19 at 00:06

score 1 · Accepted Answer · answered Aug 01 '19 at 21:52

1

use the schedule name from your query above to run the following command to stop it

:

USE [msdb]
EXEC sp_stop_job @job_name = 'name of your job goes here'

answered Aug 01 '19 at 21:52

Harry

2,636
1
17
29

Cancelling a running SSRS timed subscription

1 Answers1