Snippet Name: Find_Jobs_by_Subscription
Tags:
Created Date: 2025-06-16 13:12:38
Last Modified Date: 2025-05-31 10:31:39
Dependencies:
DBA Notes:
Sample Output:
Title: Find Jobs by Subscription
Author: user
how can i find each job that is assocated to a subscription in tsql
Author: ChatGPT
To find jobs associated with a subscription in SQL Server, you can query the `msdb` database, which contains information about SQL Server Agent jobs and their associated subscriptions.
Here's a basic query to retrieve information about jobs and their subscriptions:
```sql
USE msdb;
GO
-- Query to get job names and their associated subscriptions
SELECT
j.name AS JobName,
s.subscription_id AS SubscriptionID,
s.description AS SubscriptionDescription
FROM
dbo.sysjobs j
INNER JOIN dbo.sysjobschedules js ON j.job_id = js.job_id
INNER JOIN dbo.syscategories c ON j.category_id = c.category_id
LEFT JOIN dbo.sysoperators o ON j.notify_email_operator_id = o.operator_id
LEFT JOIN dbo.sysnotifications n ON j.notify_email_operator_id = n.operator_id
LEFT JOIN dbo.subscriptions s ON j.name = s.report_name
ORDER BY
JobName;
```
### Explanation:
- `sysjobs` contains information about SQL Server Agent jobs.
- `sysjobschedules` contains information about job schedules.
- `syscategories` contains job category information.
- `sysoperators` contains information about operators to whom notifications are sent.
- `sysnotifications` contains information about notifications related to jobs.
- `subscriptions` contains subscription information related to SQL Server Reporting Services (SSRS).
Please note that if you're looking for subscriptions related to SSRS reports, you might need to query the SSRS database directly rather than `msdb`. The query above is a general guide and may need adjustments based on your specific setup and version. If you need to query SSRS directly, you would typically do so using the `ReportServer` database.
Author: user
can you include the report path as wel
Author: ChatGPT
To include the report path in addition to the job and subscription details, you'll need to join with the `Catalog` table from the `ReportServer` database. The `Catalog` table contains information about report paths.
Here's an updated query that combines information from the `msdb` database and the `ReportServer` database:
```sql
USE msdb;
GO
-- Query to get job names, associated subscriptions, and report paths
SELECT
j.name AS JobName,
s.subscription_id AS SubscriptionID,
s.description AS SubscriptionDescription,
c.Path AS ReportPath
FROM
dbo.sysjobs j
INNER JOIN dbo.sysjobschedules js ON j.job_id = js.job_id
INNER JOIN dbo.syscategories c ON j.category_id = c.category_id
LEFT JOIN dbo.sysoperators o ON j.notify_email_operator_id = o.operator_id
LEFT JOIN dbo.sysnotifications n ON j.notify_email_operator_id = n.operator_id
LEFT JOIN dbo.subscriptions s ON j.name = s.report_name
LEFT JOIN ReportServer.dbo.Catalog c ON s.report_path = c.Path
ORDER BY
JobName;
```
### Explanation:
- The `ReportServer.dbo.Catalog` table contains information about report paths.
- The `Path` column in the `Catalog` table represents the report path.
- The query joins the `subscriptions` table with the `Catalog` table on the report path to include the report path in the result.
Make sure you have the appropriate permissions to access the `ReportServer` database, and adjust the table and column names based on your specific SSRS configuration.
Author: user
the subscriptions table does nto ahve a column report_name
Author: ChatGPT
You're right; the `subscriptions` table in SQL Server Reporting Services (SSRS) does not have a `report_name` column. Instead, it has columns like `Report_OID` that reference the report. You'll need to join with the `Catalog` table to get the report path or name.
Here???s a revised query that includes the report path by joining the `subscriptions` table with the `Catalog` table in the `ReportServer` database:
```sql
USE msdb;
GO
-- Query to get job names, associated subscriptions, and report paths
SELECT
j.name AS JobName,
s.subscription_id AS SubscriptionID,
s.Description AS SubscriptionDescription,
c.Path AS ReportPath
FROM
dbo.sysjobs j
INNER JOIN dbo.sysjobschedules js ON j.job_id = js.job_id
LEFT JOIN dbo.subscriptions s ON s.event_description LIKE '%' + j.name + '%'
LEFT JOIN ReportServer.dbo.Catalog c ON s.Report_OID = c.ItemID
ORDER BY
JobName;
```
### Explanation:
- The `subscriptions` table contains subscription details, with `Report_OID` referring to the report ID.
- The `Catalog` table contains details about reports, including their paths.
- `ItemID` in the `Catalog` table corresponds to `Report_OID` in the `subscriptions` table.
Make sure to adapt column names and joins based on your SSRS setup.
Author: user
Subscriptiosn doe snto have the column event_description either
Author: ChatGPT
You're right; the `subscriptions` table in SQL Server Reporting Services (SSRS) does not include a `report_name` or `event_description` column. Instead, you should use the `Catalog` table to retrieve report details and join it with the `subscriptions` table on the `Report_OID` and `ItemID` fields.
Here's an updated approach:
1. Use the `Catalog` table in the `ReportServer` database to get the report paths.
2. Join the `Catalog` table with the `subscriptions` table on `Report_OID` and `ItemID`.
Assuming you have access to the `ReportServer` database and the `msdb` database, the query might look like this:
```sql
USE msdb;
GO
-- Query to get job names, associated subscriptions, and report paths
SELECT
j.name AS JobName,
s.subscription_id AS SubscriptionID,
s.Description AS SubscriptionDescription,
c.Path AS ReportPath
FROM
dbo.sysjobs j
INNER JOIN dbo.sysjobschedules js ON j.job_id = js.job_id
LEFT JOIN dbo.subscriptions s ON s.SubscriptionID = j.name -- Adjust this join as needed
LEFT JOIN ReportServer.dbo.Catalog c ON s.Report_OID = c.ItemID
ORDER BY
JobName;
```
### Explanation:
- `Catalog` contains the `Path` of the report, and `ItemID` represents the unique identifier for the report.
- `subscriptions` contains `Report_OID` which should match the `ItemID` in `Catalog`.
- The `JobName` and `SubscriptionID` might need to be adjusted based on your specific schema and relationships. If the join conditions between jobs and subscriptions need refinement, make sure to adjust them according to your environment.
If the join on `SubscriptionID` is not valid, you may need to determine how your jobs and subscriptions are related in your setup and adjust the query accordingly.
Author: user
ok, i see the job_step has a value like exec [ReportServer].dbo.AddEvent @EventType='SharedSchedule', @EventData='52226094-43a0-41b0-93b2-e49482bba4ac', and this is actually an event that launches more than one subscription, since htey have a shared schedule
Author: ChatGPT
User is working with SQL Server Reporting Services (SSRS) where a job step contains an `exec [ReportServer].dbo.AddEvent` statement that launches multiple subscriptions based on a shared schedule.
Author: tool
Model set context updated.
Author: ChatGPT
To handle this scenario where a job launches multiple subscriptions through a shared schedule, you'll need to:
1. Identify the shared schedule by linking the `JobStep` data to the shared schedule in the `ReportServer` database.
2. Query the subscriptions that are tied to that shared schedule.
Here's a query to help you retrieve this information:
### Step 1: Find the Shared Schedule from the Job
```sql
USE msdb;
GO
-- Query to find the Shared Schedule ID from the job step
SELECT
j.name AS JobName,
js.step_id AS StepID,
js.command AS JobStepCommand,
-- Extract EventData (which is the Shared Schedule GUID) from the command
SUBSTRING(js.command, CHARINDEX('@EventData=', js.command) + 11, 36) AS SharedScheduleID
FROM
dbo.sysjobs j
INNER JOIN dbo.sysjobsteps js ON j.job_id = js.job_id
WHERE
js.command LIKE '%AddEvent%SharedSchedule%';
```
### Step 2: Find Subscriptions Associated with the Shared Schedule
Now that you have the `SharedScheduleID`, you can query the `subscriptions` table in the `ReportServer` database to find subscriptions associated with this shared schedule.
```sql
USE ReportServer;
GO
-- Query to find subscriptions tied to the shared schedule
SELECT
s.subscriptionid AS SubscriptionID,
s.description AS SubscriptionDescription,
c.Path AS ReportPath
FROM
dbo.Subscriptions s
INNER JOIN dbo.ReportSchedule rs ON s.SubscriptionID = rs.SubscriptionID
INNER JOIN dbo.Schedule sc ON rs.ScheduleID = sc.ScheduleID
INNER JOIN dbo.Catalog c ON s.Report_OID = c.ItemID
WHERE
sc.ScheduleID = '52226094-43a0-41b0-93b2-e49482bba4ac'; -- Replace with the SharedScheduleID from the first query
```
### Explanation:
- **Step 1:** Extracts the `SharedScheduleID` (which is the `EventData` in the `AddEvent` command) from the job step.
- **Step 2:** Queries the `ReportServer` database to find subscriptions associated with that shared schedule using the `ScheduleID`.
This should give you a list of subscriptions tied to the shared schedule triggered by the SQL Server Agent job.