July 30, 2015 at 3:30 am
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...
January 14, 2019 at 11:05 am
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 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply