I have a Daily Invoice Report that has subscriptions for all customers and is run every night.  The report has parameters of @date & @Customer.  We had issues with invoices not being sent due to the Owner on the reports was disabled in Active Directory.  We do not know how many reports did not get sent out so I am tasked with creating a script to rerun the reports for each day in August.  I have queried the subscriptions, but need to figure out how to rerun the reports using the existing parameters set in the subscription, increment the report parameter date, and execute the emailing of the report.

Here is my query that gives me all the subscription information, but how do I rerun the job for all subscriptions for each date in August?

SELECT USR.UserName AS SubscriptionOwner
      ,SUB.ModifiedDate
      ,SUB.[Description]
      ,SUB.EventType
      ,SUB.DeliveryExtension
      ,SUB.LastStatus
      ,SUB.LastRunTime
      ,SCH.NextRunTime
 ,sub.Parameters
      ,SCH.Name AS ScheduleName      
      ,CAT.[Path] AS ReportPath
      ,CAT.[Description] AS ReportDescription
FROM dbo.Subscriptions AS SUB
     INNER JOIN dbo.Users AS USR
         ON SUB.OwnerID = USR.UserID
     INNER JOIN dbo.[Catalog] AS CAT
         ON SUB.Report_OID = CAT.ItemID
     INNER JOIN dbo.ReportSchedule AS RS
         ON SUB.Report_OID = RS.ReportID
            AND SUB.SubscriptionID = RS.SubscriptionID
     INNER JOIN dbo.Schedule AS SCH
         ON RS.ScheduleID = SCH.ScheduleID
WHERE CAT.[Path] = '/Daily Shipping Invoice736'
ORDER BY USR.UserName
        ,CAT.[Path];

• Edited by IMITAZ 7 hours 56 minutes ago

Hi IMITAZ, 

According to your description, you want to get the required information of all the subscriptions every day, right? 

In your scenario, If you want to run the query every day, you can create a SQL Server agent job to achieve your goal. Please refer to the following steps: 

1. Open SQL Server Management Studio to connect to database. 
2. Expand SQL Server Agent to create a new job. 
3. Add a new step and schedule. 
4. Type your query into the step. 

Reference:

Create a job

If you have any other question, please feel free to ask.

Regards,
Shrek Li