Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

how can I detect two subscriptions for a report

Hello

How can I detect two subscriptions for a report rs without using the subscription id? Thanks bibi
0
bibi92
Asked:
bibi92
  • 3
  • 2
1 Solution
 
lcohanDatabase AnalystCommented:
I hope the code below will help:


--find_ssrs_subscriptions
select      
            'NextRunDate' = js.next_run_date,
            'NextRunTime' = js.next_run_time,
            'Frequency'   = case when sc.freq_type = 4 then 'Daily'
                                          when sc.freq_type = 8 then 'Weekly'
                                          when sc.freq_type = 16 then 'Monthly'
                                          end,
            'Subscribers' = s.ExtensionSettings,
            'SubscriptionOwner' = us.UserName,
            'LastRun' = s.LastRunTime,
            'ReportPath' = c.Path,
            'SQLJobName' = j.name
from ReportServer.dbo.Subscriptions s
      inner join ReportServer.dbo.Catalog c on c.ItemID = s.Report_OID
      inner join ReportServer.dbo.Users us on us.UserID = s.OwnerId
      inner join msdb.dbo.sysjobs j on j.job_id = (select job_id from msdb.dbo.sysjobsteps where command like '%'+CONVERT(nvarchar(128),s.Report_OID)+'%')
      inner join msdb.dbo.sysjobschedules js on j.job_id=js.job_id
      inner join msdb.dbo.sysschedules sc on js.schedule_id=sc.schedule_id
0
 
bibi92Author Commented:
Sorry but I don't see the subscription with this query, but with this query :
SELECT s.SubscriptionID
   FROM Catalog c
   INNER JOIN Subscriptions s ON (s.Report_OID = c.ItemID)
   WHERE c.Path = @PATH
Where are the reportserver jobs located?
Thanks
bibi
0
 
bibi92Author Commented:
DECLARE @PATH           varchar(455)      -- nom du rapport SSRS
DECLARE @ABO             varchar(455)      -- nom de l'abonnement via le champ caché NomAbonnement)
   SELECT @SUBSCRIPTIONID = SubscriptionID FROM (
                  SELECT s.SubscriptionID, (CAST(Parameter as XML).query('
                  for $A in /Parameters/Parameter
                  where $A/Name[.="NAbonnement"]
                  return $A/Values/Value
                  ')).value('.','varchar(128)') as NAbonnement
      FROM Catalog c
      INNER JOIN Subscriptions s ON (s.Report_OID = c.ItemID)
      WHERE c.Path = @PATH) as deriv
      WHERE deriv.NAbonnement = @ABO

Regards

bibi
0
 
bibi92Author Commented:
I have found a solution. Thanks
0
 
lcohanDatabase AnalystCommented:
Sorry I am late with the reply...well here's one other query you may find useful as the SSRS jobs don't have a "friendly name" in msdb if you look via SSMS and this should give you the bame of the report associated with the job - just make sure you run it in SQL server where the SSRS jobs are:



select      distinct
            'ReportName' = c.name,
            'SQLJobName' = j.name
from ReportServer.dbo.Subscriptions s
      inner join ReportServer.dbo.Catalog c on c.ItemID = s.Report_OID
      inner join ReportServer.dbo.Users us on us.UserID = s.OwnerId
      inner join msdb.dbo.sysjobs j on j.job_id = (select job_id from msdb.dbo.sysjobsteps where command like '%'+CONVERT(nvarchar(128),s.Report_OID)+'%')
      inner join msdb.dbo.sysjobschedules js on j.job_id=js.job_id
      inner join msdb.dbo.sysschedules sc on js.schedule_id=sc.schedule_id



0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now