时间戳

首页 > 解决方案 > Job 的子查询返回超过 1 个值错误

sql - Job 的子查询返回超过 1 个值错误

问题描述

一旦 ssrs 订阅因任何原因失败，我已经创建了一个要执行的作业。

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

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

你能帮忙吗

标签： sqlreporting-services

解决方案

改用这个。

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

推荐阅读