bibi92
asked on
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
How can I detect two subscriptions for a report rs without using the subscription id? Thanks bibi
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
ASKER
I have found a solution. Thanks
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.Subscript ions 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_O ID)+'%')
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
select distinct
'ReportName' = c.name,
'SQLJobName' = j.name
from ReportServer.dbo.Subscript
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),
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
ASKER
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