Hey, First I want to identify all the subscriptions that failed in SSRS. Then, I want to create a report that will be scheduled to run daily and give the data related to the reports that had failed subscriptions. Some of the columns that I want are:ServerName, ReportName, ReportPath,Status,ErrorMessage I have tried running this script: SELECT C.Name, S.LastRunTime, S.LastStatus, S.Description FROM Subscriptions AS S LEFT OUTER JOIN [Catalog] AS C ON C.ItemID = S.Report_OID WHERE LEFT (S.LastStatus, 12) != 'Mail sent to' AND LEFT (S.LastStatus, 12) != 'New Subscrip' That gives me the report name, but how will I identify, which 1 failed (By looking at number of errors in LastStatus column?), able to get the report path as well. How will i retrieve other columns?

Hi, The code shows you the ones that failed and to get the path, just add another field to your select statement as shown below. SELECT C.Name, S.LastRunTime, S.LastStatus, S.Description, C.Path FROM Subscriptions AS S LEFT OUTER JOIN [Catalog] AS C ON C.ItemID = S.Report_OID WHERE LEFT (S.LastStatus, 12) != 'Mail sent to' AND LEFT (S.LastStatus, 12) != 'New Subscrip' Shahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com

I have already got that !! But my biggest concern is getting the "ErrorMessage" column (why did the subscription fail? What was the reason?) and the ServerName column.

HI SQlCraze ! In addition to your query, you might need to have a look at below Articles; http://sqlblog.com/blogs/rob_farley/archive/2010/03/18/re-running-ssrs-subscription-jobs-that-have-failed.aspx http://jeffprom.wordpress.com/2008/08/22/ssrs-failed-subscription-notifications/ Please let me know if this helps. Hopefully i have answered you correctly. Thanks, Hasham Niaz

I have already gone through this. But none of these posts tell you, how to get the "ErrorMessage" of the failed SSRS subscriptions. I want to use a SQL query for that.

I have already gone through this. But none of these posts tell you, how to get the "ErrorMessage" of the failed SSRS subscriptions. I want to use a SQL query for that. You need the last status column for the error message, use the code below. SELECT C.Name, S.LastRunTime, S.LastStatus, S.Description, C.Path, LastStatus FROM Subscriptions AS S LEFT OUTER JOIN [Catalog] AS C ON C.ItemID = S.Report_OID WHERE LEFT (S.LastStatus, 12) != 'Mail sent to' AND LEFT (S.LastStatus, 12) != 'New Subscrip' Shahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com

LastStatus will not give you the error message of why the subscription failed. It will just tell you taht there were errors.

I just tried and it showed the error message. This is the error message you'll see in Report Manager as well. I don't know of any other that stores more detailed message than this. Shahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com

That's weird, my subscription.LastStatus column consists of data like this:

Hi, You need to enable remote errors on your report server, this will then give you an error message in the last status field. http://msdn.microsoft.com/en-us/library/aa337165(v=sql.100).aspx You can use the sql query below which has a filter for the errors ;-) SELECT C.Name, S.LastRunTime, S.LastStatus, S.Description,C.Path FROM Subscriptions AS S LEFT OUTER JOIN [Catalog] AS C ON C.ItemID = S.Report_OID WHERE LastStatus like '%not valid%'or LastStatus like '%1 error%'or LastStatus like '%2 error%'or LastStatus like '%3 error%'or LastStatus like '%4 error%' or LastStatus like '%5 error%'or LastStatus like '%6 error%'or LastStatus like '%7 error%' or LastStatus like '%8 error%'or LastStatus like '%9 error%') (laststatus like '%fail%' or LastStatus like 'error:%'or

Except this, do you know of any other way , writing a stored procedure or something to get all this info? (Especially detailed error message)

Hi SqlCraze, Yes, we can create a stored procedure to rerun the failed subscriptions. For the detailed information, please see Sharep's answer in the following thread: rerun failed subscriptions Hope this helps. Regards, Mike YinMike Yin TechNet Community Support

I don't want to create a stored procedure to re-run all the subscriptions. I want to create stored procedure that will give me all the information about the failed subscriptions. (Especially - the reason why the subscription failed). I know "Enabling remote errors" is one of the things that we can do. Are there any other alternatives?

I don't want to create a stored procedure to re-run all the subscriptions. I want to create stored procedure that will give me all the information about the failed subscriptions. (Especially - the reason why the subscription failed). I know "Enabling remote errors" is one of the things that we can do. Are there any other alternatives? NO

Hey mate You can't get the error messages from the database. The full error messages are stored in Reporting Services Log Files. They're not in the database. There have been attempts made to parse the RS Log Files with tools, but there are problems with regional date formats in some cases. http://www.microsoft.com/en-us/download/details.aspx?id=24774 The above is the MS parser, might be worth taking a look. Josh Ash

I need to create a new report for failed SSRS subscriptions taht willhave failure messages as well in SSRS. So, it's not possible in any case. Right?

The only information regarding subscription errors and status that you get in the database and the webservice for that matter is what you've already seen in the LastStatus column. Unfortunately, it is not possible to get any further information without parsing the Reporting Services log files. Another related point regarding the LastStatus column is that it is the only information (log files not included) regarding the progress of a subscription, failed, errored or otherwise. This becomes a problem when you want to carry out an action after a subscription has finished, such as merging a series of PDF documents created via a data driven subscription, or sending a notification. You must parse the LastStatus column, returned through a webservice call or directly from the database. Josh Ash