Subscription

Question

Hi all,

I hv a question about SSRS subscription under mssql 2022 STD, I saw when i subscript to a report, it will create a job under SSMS jobs. I would like to manage the report subscription not run on the specified date store in some table in the database. Is it possible to run another job to use sp_update_job to disable the job when date is find in the table, and enable the job when the date is not find in the table. Because the SSRS's job contain a remarks that "modify this job will make database incompatible", im not sure is it work.

thanks in advance.

Answer 1

Hi @Joey Tsui

instead of using T-SQL I recommend you to use Powershell Get-RsSubscription and Set-RsSubscription

use this as reference

https://stackoverflow.com/questions/54636884/amend-ssrs-subscriptions-with-t-sql

Regards

Javier

Answer 2

Hi @Joey Tsui,

You may add a step before the subscription with TSQL and then run the job everyday.

Such as if the date exists, then print, else then raise error and the next step to run the subscription will not do.

DECLARE @CheckDate DATE = '2025-01-13';  -- Example date to check
-- Check if the specific date exists in the EmployeeAttendance table
IF EXISTS (SELECT 1 FROM EmployeeAttendance WHERE AttendanceDate = @CheckDate)
BEGIN
    -- If the date exists, print success and run the query
    PRINT 'Date exists, proceeding with the query.';
    -- Your query that should run if the date exists
    SELECT EmployeeID, FirstName, LastName
    FROM EmployeeAttendance
    WHERE AttendanceDate = @CheckDate;
END
ELSE
BEGIN
    -- If the date does not exist, raise an error
    RAISEERROR('The specified date does not exist in the table. Operation failed.', 16, 1);
END

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.