ssrs订阅_SSRS订阅失败警报

ssrs订阅

Even now, with SQL Server 2016 SP1 released only a few months ago, it baffles me that there is still no built-in functionality to alert admins or users of failed subscriptions in Reporting Services. We still rely on scripts like the one I’m about to describe or report recipients contacting administrators/helpdesks when their report emails don’t arrive or fail to appear in file shares. This is something that people have had to work around for years. There is some documentation that Microsoft provides to help you get started with monitoring subscriptions from the log files with direction on using PowerShell but it is not by any means a complete solution. (Monitor Reporting Services Subscriptions)

即使是现在，SQL Server 2016 SP1仅在几个月前才发布，这使我感到困惑，因为仍然没有内置功能可以在Reporting Services中向管理员或用户发出失败的订阅警报。 当他们的报告电子邮件没有到达或没有出现在文件共享中时，我们仍然依靠我将要描述的脚本或报告收件人联系管理员/服务台的脚本。 这是人们多年来一直在努力的事情。 Microsoft提供了一些文档，以帮助您开始从日志文件监视订阅，并指导使用PowerShell的方法，但这绝不是一个完整的解决方案。 （ 监视报告服务订阅 ）

主题变化 (Variations on a theme)

As you can imagine, with this being a lingering issue in SSRS there have been a number of similar solutions and approaches to handle & troubleshoot failed subscriptions.

您可以想象，由于这是SSRS中的一个长期问题，已经有许多类似的解决方案和方法来处理和排除失败的订阅。

One such example is Dean Kalanquin’s blog post on MSDN from 2009 (Monitoring and Troubleshooting Subscriptions) which details how SSRS treats subscriptions with a few examples of why they’d fail. NOTE: Its only related to SQL Server 2005 & 2008 though.

一个这样的例子是Dean Kalanquin在2009年发布的有关MSDN的博客文章（《 监视和故障排除订阅》 ），其中详细介绍了SSRS如何对待订阅，并提供了一些失败原因的示例。 注意：它仅与SQL Server 2005和2008有关。

Also there’s still many Active feedback posts with similar workarounds & no solutions. I.e. (Notification of SSRS subscription when it fails).

此外，仍然有许多Active反馈帖子具有类似的解决方法，但没有解决方案。 即（ SSRS订阅失败时的通知 ）。

我的方法 (My Approach)

With over 50 subscriptions set up by BI developers and over 100 created by users in a separate SharePoint integrated instance, there are a lot of potential failures that could be missed by both admins & users. This script was put together when we were running SQL Server 2008 & still remains in use through 2008 R2, 2014 & most likely when we upgrade to 2016 too. I have recently made my own improvements & tweaks as a pet project.

BI开发人员设置了50多个订阅，而用户在单独的SharePoint集成实例中创建了100多个订阅，因此，管理员和用户都可能会忽略很多潜在的失败。 该脚本是在我们运行SQL Server 2008时放在一起的，直到2008 R2、2014仍然使用，最有可能在我们升级到2016年时使用。 最近，我作为宠物项目进行了自己的改进和调整。

This is an example of the script’s output. I’ve kept it fairly simple as it’s used mainly by admins & to keep code maintenance to a minimum. As we are constructing it in HTML you can add any amount of formatting or customisation to suit your requirements.

这是脚本输出的示例。 我将其保持相当简单，因为它主要由管理员使用，并将代码维护降至最低。 当我们用HTML构造它时，您可以添加任何数量的格式或自定义设置以满足您的要求。

The SQL script should be setup as a T-SQL step in a SQL Agent job, set to run as often as you require. The majority of my subscriptions go out at the same time each day so I have it running twice a day within a few hours of each other & only highlighting failures in the last 24 hours. This script will capture all SSRS based subscription failures:

应将SQL脚本设置为SQL代理作业中的T-SQL步骤，并设置为根据需要运行。 我的大部分订阅每天都在同一时间退出，因此我每天要在彼此几个小时内运行两次，并且仅强调最近24小时内的失败。 此脚本将捕获所有基于SSRS的订阅失败：

• Email subscriptions
  电子邮件订阅
• File Share subscriptions
  文件共享订阅
• Data Driven subscriptions
  数据驱动订阅
• Data source Cache refreshes
  数据源缓存刷新

NOTE: The 24 hour restriction is good to stop you being spammed regularly by an unfixable error or one that requires further investigation. The downside being that on a rare occasion I’ve found a failure has been missed (on a monthly scheduled report) and only rediscovered months later when the user notified us of 2 months’ worth of reports missing. This is because the original failure would stop subsequent schedule runs from executing & the last run would be beyond the 24 hour window. To get the best of both worlds, you may be best running a “time restricted Agent job” daily and a non-restricted job once a month to capture any unresolved errors.

注意：24小时限制可以防止您因无法修复的错误或需要进一步调查的错误而定期向您发送垃圾邮件。 不利的一面是，在极少数情况下，我发现失败被遗漏了（按月排定的报告），直到用户通知我们2个月的报告遗失，几个月后才重新发现。 这是因为原始故障将停止执行后续计划，并且最后一次运行将超出24小时窗口。 为了兼顾两者，您最好每天运行一次“时间受限的代理程序”工作，每月运行一次非受限的工作，以捕获所有未解决的错误。

Upon discovering a failed subscription the script sends an email to the set recipients with all the relevant information, including the SQL Agent job name, (which is a horribly unreadable GUID for every SSRS subscription job). This allows you to re-run the subscription easily & clear the failure error.

一旦发现失败的订阅，脚本将向电子邮件发送给设置的收件人，其中包含所有相关信息，包括SQL Agent作业名称（对于每个SSRS订阅作业，这都是非常难以阅读的GUID）。 这使您可以轻松地重新运行订阅并清除失败错误。

笔记 (Notes)

I have excluded a few different status options in this script as they would be considered success scenarios. I may not have caught all possible options as this list was built over time. Feel free to change or adjust to suit. Microsoft provides a list of possible values for the Status column in the documentation I linked to at the start (Monitor Reporting Services Subscriptions) though it doesn’t cover Cache refreshes or subscriptions that may be executing when you query the table (ie. “Pending” status).

我在此脚本中排除了一些不同的状态选项，因为它们被认为是成功方案。 由于此列表是随着时间的推移而建立的，因此我可能没有抓住所有可能的选择。 随时更改或调整以适合自己。 Microsoft提供了我一开始链接到的文档（“ Monitor Reporting Services订阅” ）中“状态”列的可能值列表，尽管它没有涵盖查询表时可能正在执行的缓存刷新或订阅（即“待处理”）。 “ 状态）。

 
WHERE
 Sub.[LastStatus] NOT LIKE '%was written%' --File Share subscription
AND Sub.[LastStatus] NOT LIKE '%pending%' --Subscription in progress. No result yet
AND Sub.[LastStatus] NOT LIKE '%mail sent%' --Mail sent successfully.
AND Sub.[LastStatus] NOT LIKE '%New Subscription%' --New Sub. Not been executed yet
AND Sub.[LastStatus] NOT LIKE '%been saved%' --File Share subscription
AND Sub.[LastStatus] NOT LIKE '% 0 errors.' --Data Driven subscription
AND Sub.[LastStatus] NOT LIKE '%succeeded%' --Success! Used in cache refreshes
AND Sub.[LastStatus] NOT LIKE '%successfully saved%' --File Share subscription
AND Sub.[LastStatus] NOT LIKE '%New Cache%' --New cache refresh plan
 

I have also defined a few specific variables which will need to be changed to suit your own environment.

我还定义了一些特定的变量，需要对其进行更改以适合您自己的环境。

 
SELECT @EmailRecipient = 'Changeme@craigporteous.com'
 

This can be multiple users, simply separate them with a semicolon.

这可以是多个用户，只需用分号分隔即可。

 
--Set DB Mail profile to use
SELECT TOP 1 @ProfileName = [Name] FROM msdb.dbo.sysmail_profile WHERE [Name] = 'Alert-BI-Admins'
 

The profile will be the relevant Operator listed under the SQL Server Agent shown in Management Studio.

该配置文件将是Management Studio中显示SQL Server代理下列出的相关操作员 。

These are the only necessary changes you will need to make to the script for it to work. You can however, alter the fields returned, as well as the HTML section to include company logos or other formatting. In the case of several reporting instances, I further differentiate them by changing the colour of the header text in the email body by editing this HTML snippet:

这些是您需要对脚本进行的唯一必要更改，它才能起作用。 但是，您可以更改返回的字段以及HTML部分以包括公司徽标或其他格式。 对于多个报告实例，我通过编辑以下HTML代码段来更改电子邮件正文中标题文本的颜色，从而进一步区分它们：

N'<H3 style=”color:red; font-family:verdana”>

N'<H3 style =“” color： 红色 ； font-family：verdana”>

The main body of the email is created as an HTML table with the dataset placed inside & header’s manually defined. As you can see in the first SELECT statement I’ve added ISNULL to convert null values into zero length strings.

电子邮件的主体被创建为HTML表格，数据集位于＆标题的手动定义内部。 正如您在第一个SELECT语句中看到的那样，我添加了ISNULL以将空值转换为零长度的字符串。

 
ISNULL(REPLACE(Sub.[Description],'send e-mail to ',''),' ') AS Recipients
 

This is done as some subscriptions will have no value in the description field. The null causes the remaining columns in that row to move out of line. Its purely a formatting change.

这样做是因为某些预订在描述字段中没有任何价值。 null会导致该行中的其余列移出行。 纯粹是格式更改。

进一步的发展 ( Further Development )

Although I’ve opted to keep the output simple, it is possible to add more functionality to the dataset, such as making report paths into clickable links to speed up troubleshooting. This can be achieved by wrapping a link tag with your report server URL around the [Path] field on the first select statement then casting as XML within the table select statement.

尽管我选择使输出保持简单，但是可以向数据集添加更多功能，例如将报表路径变成可单击的链接以加快故障排除速度。 这可以通过以下方法来实现：在第一个select语句的[Path]字段周围使用带有报表服务器URL的链接标记，然后在表select语句中将其转换为XML。

Eg.

例如。

‘<a href=“http://MyReportingSite.com/Reports/Pages/Report.aspx?ItemPath=’ + Cat.[Path] + ‘ “></a>’

'<a href=”http://MyReportingSite.com/Reports/Pages/Report.aspx?ItemPath='+目录[路径]+'"> </a>'

It can be tricky if you are using special characters in report names or folders. This can break the XML conversion and cause the whole process to fail. Wrapping the field in a REPLACE would capture the most common character though

如果在报表名称或文件夹中使用特殊字符，可能会很棘手。 这可能会中断XML转换并导致整个过程失败。 将字段换成REPLACE会捕获最常见的字符

 
  td =CAST(REPLACE(t.[Path], ‘&’, ‘&amp’) AS XML),'',
 

订阅警报T-SQL脚本失败 (Failed Subscription Alerting T-SQL Script)

This is a standardised version of the entire script that should be added as a T-SQL step to a SQL Agent job. I’ve commented out the date restriction that I mentioned above.

这是整个脚本的标准化版本，应作为T-SQL步骤添加到SQL Agent作业中。 我已注释掉上面提到的日期限制。

 
USE ReportServer
GO
 
DECLARE @count INT
 
SELECT
   	Cat.[Name],
   	Rep.[ScheduleId],
   	Own.UserName,
   	ISNULL(REPLACE(Sub.[Description],'send e-mail to ',''),' ') AS Recipients,
   	Sub.[LastStatus],
   	Cat.[Path],
   	Sub.[LastRunTime]
INTO
   	#tFailedSubs
FROM
   	dbo.[Subscriptions] Sub with (NOLOCK)
INNER JOIN
   	dbo.[Catalog] Cat with (NOLOCK) on Sub.[Report_OID] = Cat.[ItemID]
INNER JOIN
   	dbo.[ReportSchedule] Rep with (NOLOCK) ON (cat.[ItemID] = Rep.[ReportID] and Sub.[SubscriptionID] =Rep.[SubscriptionID])
INNER JOIN
   	dbo.[Users] Own with (NOLOCK) on Sub.[OwnerID] = Own.[UserID]
WHERE
Sub.[LastStatus] NOT LIKE '%was written%' --File Share subscription
AND Sub.[LastStatus] NOT LIKE '%pending%' --Subscription in progress. No result yet
AND Sub.[LastStatus] NOT LIKE '%mail sent%' --Mail sent successfully.
AND Sub.[LastStatus] NOT LIKE '%New Subscription%' --New Sub. Not been executed yet
AND Sub.[LastStatus] NOT LIKE '%been saved%' --File Share subscription
AND Sub.[LastStatus] NOT LIKE '% 0 errors.' --Data Driven subscription
AND Sub.[LastStatus] NOT LIKE '%succeeded%' --Success! Used in cache refreshes
AND Sub.[LastStatus] NOT LIKE '%successfully saved%' --File Share subscription
AND Sub.[LastStatus] NOT LIKE '%New Cache%' --New cache refresh plan
-- AND Sub.[LastRunTime] > GETDATE()-1
 
-- If any failed subscriptions found, proceed to build HTML & send mail.
SELECT @count = COUNT(*) FROM #tFailedSubs
 
IF (@count>0)
 
   	BEGIN
 
   	DECLARE @EmailRecipient NVARCHAR(1000)
   	DECLARE @SubjectText NVARCHAR(1000)
   	DECLARE @ProfileName NVARCHAR(1000)
   	DECLARE @tableHTML1 NVARCHAR(MAX)
   	DECLARE @tableHTMLAll NVARCHAR(MAX)
 
   	SET NOCOUNT ON
   	
   	SELECT @EmailRecipient = 'Changeme@craigporteous.com'
   	SET @SubjectText = 'Failed SSRS Subscriptions'
 
   	--Set DB Mail profile to use
   	SELECT TOP 1 @ProfileName = [Name] FROM msdb.dbo.sysmail_profile WHERE [Name] = 'Alert-BI-Admins'
   	
   	SET @tableHTML1 =
 
         	N'<H3 style="color:red; font-family:verdana">Failed SSRS Subscription details. Please resolve & re-run jobs</H3>' +
         	N'<p align="left" style="font-family:verdana; font-size:8pt"></p>' +
         	N'<table border="2" style="font-size:8pt; font-family:verdana; text-align:left">' +
         	N'<tr style="color:black; font-weight:bold">' +
         	N'<th>Report Name</th><th>SQL Agent Job ID</th><th>Owner Username</th><th>Distribution</th><th>Error Message</th><th>Report Location</th><th>Last Run Time</th></tr>' +
         	CAST((
                	SELECT
                       	td = t.[Name],'',
                       	td = t.[ScheduleId],'',
                       	td = t.[UserName],'',
                       	td = t.[Recipients],'',
                       	td = t.[LastStatus],'',
                       	td = t.[Path],'',
                	   	td = t.[LastRunTime]
                	FROM
                       	#tFailedSubs t
                	FOR XML PATH('tr'), TYPE)
         	AS NVARCHAR(MAX) ) +
         	N'</table>'
 
SET @tableHTMLAll = ISNULL(@tableHTML1,'')
 
IF @tableHTMLAll <> ''
   	
   	BEGIN
 
   	--SELECT @tableHTMLAll
         	EXEC msdb.dbo.sp_send_dbmail
                	@profile_name = @ProfileName,
                	@recipients = @EmailRecipient,
                	@body = @tableHTMLAll,
                	@body_format = 'HTML',
                	@subject = @SubjectText
   	END
 
SET NOCOUNT OFF
 
DROP TABLE #tFailedSubs
 
END  
 

但是PowerBI拥有它！ (But PowerBI has it!)

Much like we can now do (to a limited extent) with dataset refreshes in PowerBI (pictured) I would have expected Microsoft to have the functionality to alert on failed subscriptions in SSRS, especially as they are bringing the 2 products together with PowerBI On-prem. It may be something we need to wait much longer for though. Until then, I think this script fills that gap.

就像我们现在可以（在一定程度上）对PowerBI中的数据集进行刷新（如图所示）一样，我希望Microsoft具有在SSRS中订阅失败时发出警报的功能，尤其是因为它们将这两种产品与PowerBI On-上班 我们可能需要等待更长的时间。 在此之前，我认为该脚本填补了这一空白。

参考资料 (References)

• Monitoring and Troubleshooting Subscriptions 订阅的监视和故障排除
• Notification of SSRS subscription when it fails SSRS订阅失败时的通知
• Monitor Reporting Services Subscriptions 监视Reporting Services订阅

翻译自: https://www.sqlshack.com/ssrs-failed-subscription-alerting/

ssrs订阅