February 24, 2009 at 10:39 am
I'm trying to write a report showing all report subscriptions etc being run by SQL agent jobs. Looks like I can get the information I require from the ReportSchedule table in the report$server database. However, I seem to be getting multiple entries and I seem to be getting snapshots mixed in with subscriptions. An identifier seems to be the ReportAction field or Type in the Catalog table. I'm unsure though of the definition of these two fields. Where can I get a field layout and description of report server database tables please? Would have thought this would have been easy to find on the web somewhere or on the microsoft site but I've failed dismally!
February 24, 2009 at 12:02 pm
Where can I get a field layout and description of report server database tables please?
There are only 26 tables in SQL Server 2005 ReportServerDB and 30 tables in SQL Server 2008 ReportServerDB right click on each table and click on create to a new query window that will give you the create table statement for each table.
Kind regards,
Gift Peddie
February 25, 2009 at 3:00 am
Sorry, what I meant was what does each field in the tables signify rather than the schema itself. For example, what does ReportAction mean in the ReportSchedule table. Basically a description of each field similar to how Microsoft have done with the sysjobhistory table in the msdb database (see this link)
February 25, 2009 at 6:04 am
Do not write applications that run queries against the report server database. The report server database is not a public schema. The table structure might change from one release to the next. If you write an application that requires access to the report server database, always use the Reporting Services APIs to access the report server database.
The information you are looking for is not public because Microsoft does not want you running queries off ReportServerDB and Report Action is action your users perform in a report like use hidden item or use parameter to get different data or calling a subreport.
http://technet.microsoft.com/en-us/library/ms157285.aspx
Kind regards,
Gift Peddie
February 25, 2009 at 6:16 am
Wasn't creating an application really, I was only writing a reporting services report to mail to my support team on a daily basis so that they can add to their daily checklist to check that sql agent jobs including, in this instance, reporting services subscriptions etc run ok each day. They don't have SQL or SQL Management studio on their pcs as they're not technically qualified to have SQL. If you can think of a better way of informing them on progress or failure of these rs jobs (i.e. sql agent jobs that have a unreadable name) separate to the other maintenance plan jobs then please let me know.
February 25, 2009 at 6:38 am
There is a ListRunningJobs stored procedure it may give you the information you need. You could got create a report with it, the other option is to create a view that runs off that table.
Kind regards,
Gift Peddie
February 25, 2009 at 8:06 am
Doesn't that just list jobs that are currently running? A view of the execution results is what I was trying to do in effect, but don't know the meaning of the returned columns (used ReportAction as an example of a column). I'm thinking I ought to do a separate report to list the results of non-ssrs sql agent jobs (such as maintenance plans) by de-selecting those where a record exists in sysjobs (msdb database) as well as in the reportschedule table in report$server. I should then write another one just for reporting services items that are not on demand reports i.e. subscriptions and snapshots. Trouble is knowing where to get these results from. I know I can get report execution details from the executionlog table but I'm not sure whether subscriptions and snapshots are also recorded there. What do you think?
February 25, 2009 at 8:28 am
There is a SnapshotData table and Subscription table and I found getsnapshot and getsubscription stored procedures. If you spend time with the database you will find all you need by either creating views or using existing stored procedures.
Kind regards,
Gift Peddie
February 26, 2009 at 7:52 am
We tend to have alerts generated on failures out of the Windows Event log.
For example, Event 12288 indicates the start of one of our SSIS Packages, Event 12289 indicates the end.
If it fails, we see an Error 12291. We use SCOM to monitor and send alerts.
We also have it set up to look for missing events - so if we don't see our process start between 12am and 2am, we get sent an alert.
So you might consider an approach of monitoring exceptions similar to this.
Not that you'd have SCOM - it's more the concept.
RS has some log files to, so you might want to look at these also. Look under where you installed SQL server.
C:\Program Files\Microsoft SQL Server\MSQL.1\Reporting Services\Log Files would be the default.
Greg E
February 27, 2009 at 10:07 am
We don't have SCOM here, but it sounds like monitoring it that way is quite labour intensive and could get unmanageable with many subscriptions set up if looking at the event viewer for start and end times. Do you find this to be the case? The ideal scenario is to set up something automated to send to my support team with a quick reference that they can scan for any failures.
February 27, 2009 at 10:18 am
Gift Peddie (2/25/2009)
There is a SnapshotData table and Subscription table and I found getsnapshot and getsubscription stored procedures. If you spend time with the database you will find all you need by either creating views or using existing stored procedures.
Have queried the snapshotdata table and can't make head from tail with it really. Subscriptions looks a bit better but doesn't give a run status.
February 27, 2009 at 10:39 am
Actually monitoring is not labor intensive. Once the rule is setup, you're well on your way.
We are alerted on selected errors that show up in the event log, but also have scripts to look for a 'missing' event.
Anything done in SCOM could be done with WMI or Powershell scripts run as a scheduled task.
Concept of 'exceptiion based' is more important than the tool.
Microsoft also has a free tool log parser (I think it's up to version 2.2) which can be used to query log files.
We aren't looking through the log files - that's what the tools are doing for us.
Greg E
March 2, 2009 at 2:20 am
Thanks Greg. Will look in to that.
November 9, 2009 at 2:39 pm
I had a similar request: find the SQLAgent jobs for SSRS Report subscriptions. Here's what I've come up with:
select j.name as JobName
from dbo.Catalog c
inner join dbo.ReportSchedule rs on (c.ItemID = rs.ReportID)
inner join msdb.dbo.sysjobs_view j on (cast(rs.ScheduleID as nvarchar(128)) = j.name)
where c.Name = <Enter_Report_Name>
November 11, 2009 at 2:39 pm
This might help you...
USE [ReportServer]
SELECT c.PATH, s.Description, s.LastStatus, s.LastRunTime
FROM Subscriptions s INNER JOIN CATALOG c ON s.Report_Oid = c.ItemId
WHERE LastRunTime > DATEADD(hh,-12,GETDATE())
ORDER BY s.LastRunTime ASC
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply