identify failing ssrs

• newbie389

  Valued Member

  Points: 69

  More actions

  July 23, 2018 at 9:23 am

  #354771

  hi

  we have over 600 ssrs reports in sql 2012  over time some reports started to fail for various reasons , I need to identify
  failing ones, and wondered if there is an easy way to list all those which are failing to open without having to run each reports manually?
  I am only interested in those which fail to run so I can troubleshoot them
   thanks in advance

• Thom A

  SSC Guru

  Points: 99126

  More actions

  July 23, 2018 at 9:52 am

  #1998630

  You can get the status of the last attempted subscription from the database. This is the query I use, which I'm pretty sure I stole parts of from somewhere many many years ago, however, should get you there, or at least help:

  --(Lack of) Indenting courtesy of SSCs text editor.... -_-
SELECT EL.InstanceName,
   COALESCE(C.Path, 'Unknown') AS ItemPath,
   C.Name AS ReportName,
   EL.UserName,
   EL.ExecutionId,
   CASE (EL.RequestType)
    WHEN 0 THEN 'Interactive'
    WHEN 1 THEN 'Subscription'
    WHEN 2 THEN 'Refresh Cache'
    ELSE 'Unknown'
   END AS RequestType, -- SubscriptionId,
   EL.Format,
   EL.Parameters,
   CASE (EL.ReportAction)
    WHEN 1 THEN 'Render'
    WHEN 2 THEN 'BookmarkNavigation'
    WHEN 3 THEN 'DocumentMapNavigation'
    WHEN 4 THEN 'DrillThrough'
    WHEN 5 THEN 'FindString'
    WHEN 6 THEN 'GetDocumentMap'
    WHEN 7 THEN 'Toggle'
    WHEN 8 THEN 'Sort'
    WHEN 9 THEN 'Execute'
    ELSE 'Unknown'
   END AS ItemAction,
   EL.TimeStart,
   EL.TimeEnd,
   EL.TimeDataRetrieval,
   EL.TimeProcessing,
   EL.TimeRendering,
   CASE (EL.Source)
    WHEN 1 THEN 'Live'
    WHEN 2 THEN 'Cache'
    WHEN 3 THEN 'Snapshot'
    WHEN 4 THEN 'History'
    WHEN 5 THEN 'AdHoc'
    WHEN 6 THEN 'Session'
    WHEN 7 THEN 'Rdce'
    ELSE 'Unknown'
   END AS Source,
   EL.[Status],
   CASE WHEN EL.[Status] = 'rsSuccess' THEN 1 ELSE 0 END AS Success,
   EL.ByteCount,
   EL.[RowCount],
   EL.AdditionalInfo
FROM ReportServer.dbo.ExecutionLogStorage EL
   LEFT OUTER JOIN ReportServer.dbo.Catalog C ON EL.ReportID = C.ItemID;

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Jeff Moden

  SSC Guru

  Points: 1003873

  More actions

  July 23, 2018 at 1:59 pm

  #1998677

  newbie389 - Monday, July 23, 2018 9:23 AM

  hi

  we have over 600 ssrs reports in sql 2012  over time some reports started to fail for various reasons , I need to identify
  failing ones, and wondered if there is an easy way to list all those which are failing to open without having to run each reports manually?
  I am only interested in those which fail to run so I can troubleshoot them
   thanks in advance

  Heh... if the users aren't complaining, I'd put them in the "To be deleted soon" locker because it would seem that none of the users care enough to report the problems. 😉

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)