CODEWITHSUNDEEP
sqlreporting-services
Jamila Al
Jamila Al

Reputation: 7

Subquery returned more than 1 value error with Job

I have created a job to be executed once the ssrs subscription failed for any reason.

This job works perfectly if only one subscription failure occured. the last time I had 3 failure. the job : show this error :

Msg 512, Level 16, State 1, Line 5 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I have tried to replace = with IN but did not work so it can except multiple values.

DECLARE @ScheduleId NVARCHAR (50)
SET
  @ScheduleId = (
    SELECT
      rs.ScheduleID
    FROM
      ReportServer.dbo.Catalog c WITH(NOLOCK)
      INNER JOIN ReportServer.dbo.Subscriptions sub WITH(NOLOCK) ON (c.ItemID = (sub.Report_OID))
      INNER JOIN ReportServer.dbo.ReportSchedule rs WITH(NOLOCK) ON (
        c.ItemID = (rs.ReportID)
        AND sub.SubscriptionID IN (rs.SubscriptionID)
      )
      INNER JOIN ReportServer.dbo.Schedule sch WITH(NOLOCK) ON (rs.ScheduleID = (sch.ScheduleID))
      INNER JOIN msdb.dbo.sysjobs sj WITH(NOLOCK) ON (cast(rs.ScheduleID as sysname) = (sj.name)) --sysname equivalent to nvarchar(128)
      INNER JOIN msdb.dbo.sysjobschedules sjs WITH(NOLOCK) ON (sj.job_id = (sjs.job_id))
      INNER JOIN msdb.dbo.sysschedules ss WITH(NOLOCK) ON (sjs.schedule_id = (ss.schedule_id))
    WHERE
      (
        sub.LastStatus = 'Done: 1 processed of 1 total; 1 errors.'
        OR sub.LastStatus LIKE '%Failure%'
      )
  )

Could you please assist in this

Upvotes: 0

Views: 46

Answers (1)

Ed Bangga
Ed Bangga

Reputation: 13016

use this instead.

DECLARE @ScheduleId NVARCHAR (50)
SET
  @ScheduleId = (
    SELECT
      TOP 1 rs.ScheduleID
    FROM
      ReportServer.dbo.Catalog c WITH(NOLOCK)
      INNER JOIN ReportServer.dbo.Subscriptions sub WITH(NOLOCK) ON (c.ItemID = (sub.Report_OID))
      INNER JOIN ReportServer.dbo.ReportSchedule rs WITH(NOLOCK) ON (
        c.ItemID = (rs.ReportID)
        AND sub.SubscriptionID IN (rs.SubscriptionID)
      )
      INNER JOIN ReportServer.dbo.Schedule sch WITH(NOLOCK) ON (rs.ScheduleID = (sch.ScheduleID))
      INNER JOIN msdb.dbo.sysjobs sj WITH(NOLOCK) ON (cast(rs.ScheduleID as sysname) = (sj.name)) --sysname equivalent to nvarchar(128)
      INNER JOIN msdb.dbo.sysjobschedules sjs WITH(NOLOCK) ON (sj.job_id = (sjs.job_id))
      INNER JOIN msdb.dbo.sysschedules ss WITH(NOLOCK) ON (sjs.schedule_id = (ss.schedule_id))
    WHERE
      (
        sub.LastStatus = 'Done: 1 processed of 1 total; 1 errors.'
        OR sub.LastStatus LIKE '%Failure%'
      )
  )

Upvotes: 1

Related Questions