Be notified when subscription failed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Be notified when subscription failed

Author

Topic

tikus
Starting Member

31 Posts

Posted - 2006-12-04 : 11:30:21

Is there anyway to setup where I can be notified if the subscripiton failed? Thanks.

jhermiz

3564 Posts

Posted - 2006-12-04 : 16:09:01

The only place I know you can do this is the actual account profile when you setup outlook. You can setup a rule to check for some status or keyword to trigger an email back to you. Other than that there is a status on the report manager when a subscription fails.

Another thing to look for is a log file that tells you whether the report was sent / not. I am not certain on the exact location but search the reporting services site, help, or your network folders for a log file within rs.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

tikus
Starting Member

31 Posts

Posted - 2006-12-04 : 19:52:35

I found Subscriptions and ExecutionLog tables in ReportServer database. I will play around. Thanks a lot!!!

jhermiz

3564 Posts

Posted - 2006-12-05 : 07:45:52

No problem I used to have a blog on here about how to use the RS database and tap into its data.

Here is a sample entry, I think Graz is working on getting me space back but it will tkae some time:

The Power of ReportServer

A lot of programmer's / dba's install RS and then simply use it to build reports for other databases. In fact many do not even realize that the ReportServer database that is installed on the SQL box has some nifty tables to snatch some relevant information. These 2 examples that I'll go over I've posted in the past on SQLTeam here but I decided it's probably best to place it on my blog so that others can easily get to it.

So our first example involves the Catalog and ExecutionLog tables in the ReportServer database. Let me give you the scenerio I ran into at work and the reason why I wrote this little nifty query. The manufacturing company I work for Johann A. Krause, Inc., a division of ThyssenKrupp, generally go through a lot of meetings to get a product out to one of our many customers. With meetings comes reports, this can include action item reports, issue reports, resolutions, and historical data. Well a lot of the time an employee may come into the meeting being unprepared, so we needed a way to track down whether employees were running specific reports or not. At first I thought to myself I could include some sort of INSERT mechanism right into the application itself when the user generated a report, but that seemed like too much. I dug around the ReportServer database and found exactly what I needed. All I had to do was make sense of it, perform a join, and voila a query that returns the user, date and time, and the report name of any report that came out of our rs box.

Here's the query:

USE ReportServer

GO

SELECT

c1.Name AS ReportName,

e1.UserName AS Employee,

e1.Format, --what did they export the report to (optional)

e1.Parameters, --did they query the result set (optional)

e1.TimeEnd --when did they run it ?

FROM

[Catalog] c1

INNER JOIN

ExecutionLog e1 ON

e1.ReportID = c1.ItemID

ORDER BY c1.Name

GO

That should give you a listing of the employees who ran a report and what report they ran. The next example touches a bit on subscriptions in Reporting Services. Subscriptions in Reporting Services are simply Jobs in SQL Server (if you look in the Jobs section in SQL Server you will notice a UNIQUE GUI ID LIKE 000123-303204-9348-24024045032450, which is simply a subscription). That actual subscription is stored in the ReportServer database in the Catalog and Subscriptions table. So the reason behind this query is to avoid having to find out what subscriptions are going out of your RS box from Report Manager. A lot of times you want to find out what is going on, due to performance issues, on your RS box. So rather than dig around and find out what kind of subscriptions are going out you can simply write a query to display those subscriptions.

USE ReportServer

GO

SELECT

u.UserName AS TheUser, s.*, --you should list out the specific columns you need here and not use '*'

c.Name AS TheReport

FROM

[Catalog] c

INNER JOIN

Subscriptions s ON

s.Report_OID = c.ItemID

INNER JOIN

Users u ON u.UserID = s.OwnerID

ORDER BY c.Name

That should get you a listing of all subscriptions on your RS box. Hope this helps!

Here is another post I made a while back:

What's coming out of those RS subscriptions ?

So what exactly are you getting out of your RS box in terms of subscriptions by your end users? Are your users even aware that they can subscribe to reports? Or what is it exactly that may be causing all that chaos on that box. Well subscriptions are a powerful mechanism included in Reporting Services. End users no longer need to enter client applications just to run a daily report. Back in the ol days managers and end users had to log into an application to see a report, that or they would purchase some 3rd party scheduling tool that allowed them to receive reports automatically.

The world we live in today is all about conveniance. The most amount of work in the least amount of time; generally we love to be efficient or we want to become more efficient. Sometimes we are forced to be more efficient than what we really are. Running reports is just one of those things people hate to do. They hate entering client applications, they hate hoping on the web, and they despise the wait time for those queries to actually run and return result sets.

With RS users can schedule reports to be sent via:

* e-mail
* sent to network shares

What this means is Jon Dough who used to come in at 5:30am just to run a specific report that needed to be ready at 6am can now sleep an extra half hour (as long as traffic isn't so bad). Scheduled reports makes us more efficient, it is all about convenience and having RS do the dirty work should make any end user / manager happy.

A lot of times you want to know what kind of subscriptions are being pulled such as network vs. email. Whether subscriptions were succesful or not as well as if they were modified. All this information is stored in the ReportServer database. This is just another topic much related to my second blog entry about 'The Power of ReportServer'. With that said here is the query you need to run:

SELECT

S.[DeliveryExtension] AS ReportOperation,
S.[Description] AS OperationDescription,
S.[LastStatus] AS ReportLastStatus,
S.[EventType],
SUSER_SNAME(Owner.[Sid]) AS Owner,
Owner.[UserName] AS UserName,
C.[Path] AS Path,
C.Name AS Report,
S.[LastRunTime] AS ReportLastRunOn
FROM
[Subscriptions] S
INNER JOIN
[Catalog] C ON S.[Report_OID] = C.[ItemID]
INNER JOIN
[Users] Owner on S.OwnerID = Owner.UserID
INNER JOIN
[Users] Modified on S.ModifiedByID = Modified.UserID
LEFT OUTER JOIN
[SecData] SD on C.PolicyID = SD.PolicyID
LEFT OUTER JOIN
[ActiveSubscriptions] A with (NOLOCK) on S.[SubscriptionID] = A.[SubscriptionID]
ORDER BY
C.Name --you can change this, I'm ordering by the report name here.

The above will list out the types of subscriptions, the event information, some important dates, as well as the report operation (Email vs. Network). Enjoy!

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources