Solved

how can I detect two subscriptions for a report

Posted on 2011-09-02
5
281 Views
Last Modified: 2012-05-12
Hello

How can I detect two subscriptions for a report rs without using the subscription id? Thanks bibi
0
Comment
Question by:bibi92
  • 3
  • 2
5 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 36475405
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
 

Author Comment

by:bibi92
ID: 36482722
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
 

Author Comment

by:bibi92
ID: 36501490
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
 

Author Closing Comment

by:bibi92
ID: 36501492
I have found a solution. Thanks
0
 
LVL 39

Expert Comment

by:lcohan
ID: 36504637
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question