Data driven subscription failures

  • Casper101

    Hall of Fame

    Points: 3880

    Hi,

    I am running SSRS 2005 with a whole lot of data driven subscriptions.

    Sometimes the status of a data driven subscription will be: " Done: 550 processed of 550 total; 25 errors. "

    Is there anyway to determine exactly which 25 failed?

    I need to then manually (or automatically, if possible) re-send the 25 that failed...

  • meichmann

    SSCommitted

    Points: 1899

    I know this is a very old post, but I thought I would respond to add to the knowledge base:

    To see which reports failed, you can save this query to your stored procedures in SQL:

    SELECT

    h.[server] AS ServerName,

    s.database_name AS DBNAME,

    s.subsystem AS JobType,

    j.name AS JobName,

    CASE WHEN j.enabled = 1 THEN 'Enable'

    ELSE 'Disable'

    END AS IsEnable,

    j.[Description],

    s.step_id AS StepID,

    s.step_name AS StepName,

    s.database_user_name,

    s.output_file_name AS 'Path',

    s.command AS Command,

    msdb.dbo.agent_datetime(h.run_date, h.run_time) AS 'RunDatatime',

    CONVERT (date,convert(char(8),h.run_date), 101) AS Date,

    CASE WHEN h.run_duration<=60 THEN h.run_duration

    WHEN h.run_duration>=100 THEN h.run_duration/100*60+(h.run_duration%100)

    END AS DurationSeconds,

    CASE h.run_status

    WHEN 0 THEN 'Failed'

    WHEN 1 THEN 'Succeeded'

    WHEN 2 THEN 'Retry'

    WHEN 3 THEN 'Canceled'

    END AS RunStatus,

    j.date_modified AS Job_Modified,

    j.date_created,

    h.[Message],

    CASE s.on_success_action

    WHEN 1 THEN 'Quit the job reporting success'

    WHEN 2 THEN 'Quit the job reporting failure'

    WHEN 3 THEN 'Go to the next step'

    WHEN 4 THEN 'Go to the step:'+CAST(s.on_success_step_id AS NVARCHAR(20))

    END AS ONSuccess,

    CASE s.on_fail_action

    WHEN 1 THEN 'Quit the job reporting success'

    WHEN 2 THEN 'Quit the job reporting failure'

    WHEN 3 THEN 'Go to the next step'

    WHEN 4 THEN 'Go to the step:'+CAST(s.on_fail_step_id AS NVARCHAR(20))

    END AS ONFail,

    --Schedule

    p.name AS ScheduleName,

    p.enabled AS ScheduleEnable,

    p.active_start_date,

    p.active_end_date,

    CASE q.next_run_date

    WHEN 0 THEN NULL

    ELSE CAST ( CAST (q.next_run_date AS CHAR ( 8 )) + ' '

    + STUFF(STUFF( RIGHT ( '000000'

    + CAST (q.next_run_time AS VARCHAR ( 6 )),

    6 ), 3 , 0 , ':' ), 6 , 0 , ':' ) AS DATETIME)

    END AS NextRunDatetime,

    CASE p.freq_type

    WHEN 1 THEN 'One time only'

    WHEN 4 THEN 'Daily'

    WHEN 8 THEN 'Weekly'

    WHEN 16 THEN 'Monthly'

    WHEN 32 THEN 'Other'

    WHEN 64 THEN 'Runs when the SQL Server Agent service starts'

    WHEN 128 THEN 'Runs when the computer is idle'

    END AS ExecutionFrequency

    FROM msdb.dbo.sysjobs AS j

    INNER JOIN msdb.dbo.sysjobsteps AS s

    ON j.job_id = s.job_id

    INNER JOIN msdb.dbo.sysjobhistory AS h

    ON s.job_id = h.job_id

    AND s.step_id = h.step_id AND h.step_id <> 0

    INNER JOIN msdb.dbo.sysjobschedules t1

    ON j.job_id = t1.job_id

    LEFT JOIN

    (

    SELECT

    job_id,

    next_run_date,

    next_run_time

    FROM

    (

    SELECT

    ROW_NUMBER() OVER(PARTITION BY job_id ORDER BY next_run_date,next_run_time ) AS ROWID,

    job_id,

    next_run_date,

    next_run_time

    FROM msdb.dbo.sysjobschedules

    ) AS d

    WHERE ROWID=1

    ) q

    ON q.job_id = j.job_id

    INNER JOIN msdb.dbo.sysschedules p

    ON t1.schedule_id = p.schedule_id

    WHERE LEN(j.Name) = 36

    ORDER BY 'RunDatatime' DESC

    If you have custom agent job names, you can add them as an "OR" in the  bottom WHERE clause to include them in the list.

    The query will show you the status of each report the last time they were run.  If a report failed, you can then go to it in the "SQL server Agent > Jobs" section, right click on the job and select "View History".  Once in the history, you can view the step error.  It will tell you what happened for it to fail.

    I actually made a report from this query and set up a subscription to email me every morning so I can see the statuses.

    Hope this helps

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply