How to resend SSRS subscription emails?
up vote
0
down vote
favorite
If our report email subscriptions fail to get sent due to an issue like the SMTP server fails we have to manually re-send the report emails. Is there any way we can automatically re-fire the subscription emails and re-send them?
ssrs-2016
asked Nov 12 at 18:09
cspell
83
add a comment |
up vote
0
down vote
favorite
If our report email subscriptions fail to get sent due to an issue like the SMTP server fails we have to manually re-send the report emails. Is there any way we can automatically re-fire the subscription emails and re-send them?
ssrs-2016
asked Nov 12 at 18:09
cspell
83
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
If our report email subscriptions fail to get sent due to an issue like the SMTP server fails we have to manually re-send the report emails. Is there any way we can automatically re-fire the subscription emails and re-send them?
ssrs-2016
asked Nov 12 at 18:09
cspell
83
If our report email subscriptions fail to get sent due to an issue like the SMTP server fails we have to manually re-send the report emails. Is there any way we can automatically re-fire the subscription emails and re-send them?
ssrs-2016
ssrs-2016
asked Nov 12 at 18:09
cspell
83
asked Nov 12 at 18:09
cspell
83
asked Nov 12 at 18:09
cspell
83
asked Nov 12 at 18:09
cspell
83
asked Nov 12 at 18:09
cspell
83
83
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
up vote
1
down vote
I've used a similar approach to what Hannover Fist posted, but with a different approach to re-firing the subscriptions:
SELECT
S.ScheduleID AS SQLAgent_Job_Name,
SUB.Description AS Sub_Desc,
SUB.DeliveryExtension AS Sub_Del_Extension,
C.Name AS ReportName,
C.Path AS ReportPath,SUB.LastStatus
FROM ReportSchedule RS
INNER JOIN Schedule S ON (RS.ScheduleID = S.ScheduleID)
INNER JOIN Subscriptions SUB ON (RS.SubscriptionID = SUB.SubscriptionID)
INNER JOIN [Catalog] C ON (RS.ReportID = C.ItemID AND SUB.Report_OID = C.ItemID)
WHERE LEFT (SUB.LastStatus, 12) Like 'Failure%' or LEFT (SUB.LastStatus, 12) Like 'Error%'
order by reportname
USE msdb
EXEC sp_start_job @job_name = <SQLAgent_Job_Name>
One of the big differences is that mine captures both "Failure" and "Error" statuses.
I wish that I could remember where I found this so that I could give proper credit for it, since it's been a life-saver on a couple of occasions.
answered Nov 12 at 23:34
Frank Ball
46729
1
we use same to fire any sub. that did not go out or failed.
– junketsu
Nov 16 at 17:26
add a comment |
up vote
0
down vote
There's not an automatic way to do it.
You can get a list of the reports along with the Subscription IDs that failed by querying the Subscriptions table in the ReportServer database. Then JOIN it to the Catalog table to show the report details.
SELECT Sub.SubscriptionID,
Sub.InactiveFlags,
Sub.ExtensionSettings,
Sub.Description,
Sub.LastStatus,
Sub.EventType,
Sub.MatchData,
Sub.LastRunTime,
Sub.Parameters,
Sub.DeliveryExtension,
Cat.Path,
Cat.Name,
Cat.Description,
Cat.Hidden,
Cat.Parameter,
Cat.ExecutionFlag,
Cat.ExecutionTime
FROM [ReportServer].[dbo].[Subscriptions] as Sub
LEFT OUTER JOIN [ReportServer].[dbo].[Catalog] as Cat on
Sub.Report_OID = ItemID
WHERE LastStatus LIKE 'Failure%'
You could then use the Subscription ID to fire off the subscription for each report using the AddEvent function.
EXEC dbo.AddEvent @EventType = 'TimedSubscription', @EventData = @SUBSCRIPTION_ID;
You could create a stored procedure to loop through the table and fire off the subscriptions.
answered Nov 12 at 23:18
Hannover Fist
5,5871723
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53267795%2fhow-to-resend-ssrs-subscription-emails%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
I've used a similar approach to what Hannover Fist posted, but with a different approach to re-firing the subscriptions:
SELECT
S.ScheduleID AS SQLAgent_Job_Name,
SUB.Description AS Sub_Desc,
SUB.DeliveryExtension AS Sub_Del_Extension,
C.Name AS ReportName,
C.Path AS ReportPath,SUB.LastStatus
FROM ReportSchedule RS
INNER JOIN Schedule S ON (RS.ScheduleID = S.ScheduleID)
INNER JOIN Subscriptions SUB ON (RS.SubscriptionID = SUB.SubscriptionID)
INNER JOIN [Catalog] C ON (RS.ReportID = C.ItemID AND SUB.Report_OID = C.ItemID)
WHERE LEFT (SUB.LastStatus, 12) Like 'Failure%' or LEFT (SUB.LastStatus, 12) Like 'Error%'
order by reportname
USE msdb
EXEC sp_start_job @job_name = <SQLAgent_Job_Name>
One of the big differences is that mine captures both "Failure" and "Error" statuses.
I wish that I could remember where I found this so that I could give proper credit for it, since it's been a life-saver on a couple of occasions.
answered Nov 12 at 23:34
Frank Ball
46729
1
we use same to fire any sub. that did not go out or failed.
– junketsu
Nov 16 at 17:26
add a comment |
up vote
1
down vote
I've used a similar approach to what Hannover Fist posted, but with a different approach to re-firing the subscriptions:
SELECT
S.ScheduleID AS SQLAgent_Job_Name,
SUB.Description AS Sub_Desc,
SUB.DeliveryExtension AS Sub_Del_Extension,
C.Name AS ReportName,
C.Path AS ReportPath,SUB.LastStatus
FROM ReportSchedule RS
INNER JOIN Schedule S ON (RS.ScheduleID = S.ScheduleID)
INNER JOIN Subscriptions SUB ON (RS.SubscriptionID = SUB.SubscriptionID)
INNER JOIN [Catalog] C ON (RS.ReportID = C.ItemID AND SUB.Report_OID = C.ItemID)
WHERE LEFT (SUB.LastStatus, 12) Like 'Failure%' or LEFT (SUB.LastStatus, 12) Like 'Error%'
order by reportname
USE msdb
EXEC sp_start_job @job_name = <SQLAgent_Job_Name>
One of the big differences is that mine captures both "Failure" and "Error" statuses.
I wish that I could remember where I found this so that I could give proper credit for it, since it's been a life-saver on a couple of occasions.
answered Nov 12 at 23:34
Frank Ball
46729
1
we use same to fire any sub. that did not go out or failed.
– junketsu
Nov 16 at 17:26
add a comment |
up vote
1
down vote
up vote
1
down vote
I've used a similar approach to what Hannover Fist posted, but with a different approach to re-firing the subscriptions:
SELECT
S.ScheduleID AS SQLAgent_Job_Name,
SUB.Description AS Sub_Desc,
SUB.DeliveryExtension AS Sub_Del_Extension,
C.Name AS ReportName,
C.Path AS ReportPath,SUB.LastStatus
FROM ReportSchedule RS
INNER JOIN Schedule S ON (RS.ScheduleID = S.ScheduleID)
INNER JOIN Subscriptions SUB ON (RS.SubscriptionID = SUB.SubscriptionID)
INNER JOIN [Catalog] C ON (RS.ReportID = C.ItemID AND SUB.Report_OID = C.ItemID)
WHERE LEFT (SUB.LastStatus, 12) Like 'Failure%' or LEFT (SUB.LastStatus, 12) Like 'Error%'
order by reportname
USE msdb
EXEC sp_start_job @job_name = <SQLAgent_Job_Name>
One of the big differences is that mine captures both "Failure" and "Error" statuses.
I wish that I could remember where I found this so that I could give proper credit for it, since it's been a life-saver on a couple of occasions.
answered Nov 12 at 23:34
Frank Ball
46729
I've used a similar approach to what Hannover Fist posted, but with a different approach to re-firing the subscriptions:
SELECT
S.ScheduleID AS SQLAgent_Job_Name,
SUB.Description AS Sub_Desc,
SUB.DeliveryExtension AS Sub_Del_Extension,
C.Name AS ReportName,
C.Path AS ReportPath,SUB.LastStatus
FROM ReportSchedule RS
INNER JOIN Schedule S ON (RS.ScheduleID = S.ScheduleID)
INNER JOIN Subscriptions SUB ON (RS.SubscriptionID = SUB.SubscriptionID)
INNER JOIN [Catalog] C ON (RS.ReportID = C.ItemID AND SUB.Report_OID = C.ItemID)
WHERE LEFT (SUB.LastStatus, 12) Like 'Failure%' or LEFT (SUB.LastStatus, 12) Like 'Error%'
order by reportname
USE msdb
EXEC sp_start_job @job_name = <SQLAgent_Job_Name>
One of the big differences is that mine captures both "Failure" and "Error" statuses.
I wish that I could remember where I found this so that I could give proper credit for it, since it's been a life-saver on a couple of occasions.
answered Nov 12 at 23:34
Frank Ball
46729
answered Nov 12 at 23:34
Frank Ball
46729
answered Nov 12 at 23:34
Frank Ball
46729
answered Nov 12 at 23:34
Frank Ball
46729
46729
1
we use same to fire any sub. that did not go out or failed.
– junketsu
Nov 16 at 17:26
add a comment |
1
we use same to fire any sub. that did not go out or failed.
– junketsu
Nov 16 at 17:26
1
1
we use same to fire any sub. that did not go out or failed.
– junketsu
Nov 16 at 17:26
we use same to fire any sub. that did not go out or failed.
– junketsu
Nov 16 at 17:26
add a comment |
up vote
0
down vote
There's not an automatic way to do it.
You can get a list of the reports along with the Subscription IDs that failed by querying the Subscriptions table in the ReportServer database. Then JOIN it to the Catalog table to show the report details.
SELECT Sub.SubscriptionID,
Sub.InactiveFlags,
Sub.ExtensionSettings,
Sub.Description,
Sub.LastStatus,
Sub.EventType,
Sub.MatchData,
Sub.LastRunTime,
Sub.Parameters,
Sub.DeliveryExtension,
Cat.Path,
Cat.Name,
Cat.Description,
Cat.Hidden,
Cat.Parameter,
Cat.ExecutionFlag,
Cat.ExecutionTime
FROM [ReportServer].[dbo].[Subscriptions] as Sub
LEFT OUTER JOIN [ReportServer].[dbo].[Catalog] as Cat on
Sub.Report_OID = ItemID
WHERE LastStatus LIKE 'Failure%'
You could then use the Subscription ID to fire off the subscription for each report using the AddEvent function.
EXEC dbo.AddEvent @EventType = 'TimedSubscription', @EventData = @SUBSCRIPTION_ID;
You could create a stored procedure to loop through the table and fire off the subscriptions.
answered Nov 12 at 23:18
Hannover Fist
5,5871723
add a comment |
up vote
0
down vote
There's not an automatic way to do it.
You can get a list of the reports along with the Subscription IDs that failed by querying the Subscriptions table in the ReportServer database. Then JOIN it to the Catalog table to show the report details.
SELECT Sub.SubscriptionID,
Sub.InactiveFlags,
Sub.ExtensionSettings,
Sub.Description,
Sub.LastStatus,
Sub.EventType,
Sub.MatchData,
Sub.LastRunTime,
Sub.Parameters,
Sub.DeliveryExtension,
Cat.Path,
Cat.Name,
Cat.Description,
Cat.Hidden,
Cat.Parameter,
Cat.ExecutionFlag,
Cat.ExecutionTime
FROM [ReportServer].[dbo].[Subscriptions] as Sub
LEFT OUTER JOIN [ReportServer].[dbo].[Catalog] as Cat on
Sub.Report_OID = ItemID
WHERE LastStatus LIKE 'Failure%'
You could then use the Subscription ID to fire off the subscription for each report using the AddEvent function.
EXEC dbo.AddEvent @EventType = 'TimedSubscription', @EventData = @SUBSCRIPTION_ID;
You could create a stored procedure to loop through the table and fire off the subscriptions.
answered Nov 12 at 23:18
Hannover Fist
5,5871723
add a comment |
up vote
0
down vote
up vote
0
down vote
There's not an automatic way to do it.
You can get a list of the reports along with the Subscription IDs that failed by querying the Subscriptions table in the ReportServer database. Then JOIN it to the Catalog table to show the report details.
SELECT Sub.SubscriptionID,
Sub.InactiveFlags,
Sub.ExtensionSettings,
Sub.Description,
Sub.LastStatus,
Sub.EventType,
Sub.MatchData,
Sub.LastRunTime,
Sub.Parameters,
Sub.DeliveryExtension,
Cat.Path,
Cat.Name,
Cat.Description,
Cat.Hidden,
Cat.Parameter,
Cat.ExecutionFlag,
Cat.ExecutionTime
FROM [ReportServer].[dbo].[Subscriptions] as Sub
LEFT OUTER JOIN [ReportServer].[dbo].[Catalog] as Cat on
Sub.Report_OID = ItemID
WHERE LastStatus LIKE 'Failure%'
You could then use the Subscription ID to fire off the subscription for each report using the AddEvent function.
EXEC dbo.AddEvent @EventType = 'TimedSubscription', @EventData = @SUBSCRIPTION_ID;
You could create a stored procedure to loop through the table and fire off the subscriptions.
answered Nov 12 at 23:18
Hannover Fist
5,5871723
There's not an automatic way to do it.
You can get a list of the reports along with the Subscription IDs that failed by querying the Subscriptions table in the ReportServer database. Then JOIN it to the Catalog table to show the report details.
SELECT Sub.SubscriptionID,
Sub.InactiveFlags,
Sub.ExtensionSettings,
Sub.Description,
Sub.LastStatus,
Sub.EventType,
Sub.MatchData,
Sub.LastRunTime,
Sub.Parameters,
Sub.DeliveryExtension,
Cat.Path,
Cat.Name,
Cat.Description,
Cat.Hidden,
Cat.Parameter,
Cat.ExecutionFlag,
Cat.ExecutionTime
FROM [ReportServer].[dbo].[Subscriptions] as Sub
LEFT OUTER JOIN [ReportServer].[dbo].[Catalog] as Cat on
Sub.Report_OID = ItemID
WHERE LastStatus LIKE 'Failure%'
You could then use the Subscription ID to fire off the subscription for each report using the AddEvent function.
EXEC dbo.AddEvent @EventType = 'TimedSubscription', @EventData = @SUBSCRIPTION_ID;
You could create a stored procedure to loop through the table and fire off the subscriptions.
answered Nov 12 at 23:18
Hannover Fist
5,5871723
answered Nov 12 at 23:18
Hannover Fist
5,5871723
answered Nov 12 at 23:18
Hannover Fist
5,5871723
answered Nov 12 at 23:18
Hannover Fist
5,5871723
5,5871723
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53267795%2fhow-to-resend-ssrs-subscription-emails%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
