How to resend SSRS subscription emails?
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 '18 at 18:09
cspellcspell
83
add a comment |
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 '18 at 18:09
cspellcspell
83
add a comment |
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 '18 at 18:09
cspellcspell
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 '18 at 18:09
cspellcspell
83
asked Nov 12 '18 at 18:09
cspellcspell
83
asked Nov 12 '18 at 18:09
cspellcspell
83
asked Nov 12 '18 at 18:09
cspellcspell
83
asked Nov 12 '18 at 18:09
cspellcspell
83
83
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
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 '18 at 23:18
Hannover FistHannover Fist
5,7321824
add a comment |
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 '18 at 23:34
Frank BallFrank Ball
52429
1
we use same to fire any sub. that did not go out or failed.
– junketsu
Nov 16 '18 at 17:26
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
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 '18 at 23:18
Hannover FistHannover Fist
5,7321824
add a comment |
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 '18 at 23:18
Hannover FistHannover Fist
5,7321824
add a comment |
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 '18 at 23:18
Hannover FistHannover Fist
5,7321824
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 '18 at 23:18
Hannover FistHannover Fist
5,7321824
answered Nov 12 '18 at 23:18
Hannover FistHannover Fist
5,7321824
answered Nov 12 '18 at 23:18
Hannover FistHannover Fist
5,7321824
answered Nov 12 '18 at 23:18
Hannover FistHannover Fist
5,7321824
5,7321824
add a comment |
add a comment |
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 '18 at 23:34
Frank BallFrank Ball
52429
1
we use same to fire any sub. that did not go out or failed.
– junketsu
Nov 16 '18 at 17:26
add a comment |
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 '18 at 23:34
Frank BallFrank Ball
52429
1
we use same to fire any sub. that did not go out or failed.
– junketsu
Nov 16 '18 at 17:26
add a comment |
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 '18 at 23:34
Frank BallFrank Ball
52429
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 '18 at 23:34
Frank BallFrank Ball
52429
answered Nov 12 '18 at 23:34
Frank BallFrank Ball
52429
answered Nov 12 '18 at 23:34
Frank BallFrank Ball
52429
answered Nov 12 '18 at 23:34
Frank BallFrank Ball
52429
52429
1
we use same to fire any sub. that did not go out or failed.
– junketsu
Nov 16 '18 at 17:26
add a comment |
1
we use same to fire any sub. that did not go out or failed.
– junketsu
Nov 16 '18 at 17:26
1
1
we use same to fire any sub. that did not go out or failed.
– junketsu
Nov 16 '18 at 17:26
we use same to fire any sub. that did not go out or failed.
– junketsu
Nov 16 '18 at 17:26
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.
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
