子查询返回了超过 1 个值的作业错误

Question

我创建了一个作业，一旦 ssrs 订阅因任何原因失败就会执行。

如果只发生一次订阅失败，则此作业将完美运行。上次我有 3 次失败。作业 ： 显示此错误：

Msg 512，级别 16，状态 1，第 5 行子查询返回了 1 个以上的值。当子查询跟在 =、！=、<、<= 、>、>= 后面或子查询用作表达式时，不允许这样做。

我试图用 IN 替换 =，但没有工作，所以它可以除了多个值。

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%'
      )
  )

你能帮忙

Answer 1

改用这个。

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%'
      )
  )