August 19, 2008 at 11:44 pm
I'm amazed - can't seem to find this addressed anywhere!
How can I find out which reports in RS 2000 have a given recipient (i.e. email address) associated with them? I need to change the email address to a new person, since the former recipient has left the company. This seems like a normal administrative task, yet BOL doesn't seem to address it. (I'm sure I'll have the same issue in RS 2005, so I'm hoping the same solution will work in both versions.)
We are using the Report Manager to set up the subscriptions, if that makes a difference. Any suggestions greatly appreciated!
Steph Brown
August 20, 2008 at 12:33 am
Hello,
In SSRS 2005 the recipient e-mail information is stored in the table ReportServer.dbo.Subscriptions, within the column ExtensionSettings. It is stored as XML in an nText data-type.
I think it's the same in SSRS 2000, but it's been a while since I've used that and we no longer have it installed on any of our Servers, so I can't check.
Hope that helps.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
August 20, 2008 at 1:43 am
Hello again,
I found the below query posted by Bruce Loehle-Conger. Having tried it on one of our servers it seems to do what you want, but unfortunately it relates to SQL 2005.
If you find or write a SSRS 2000 version it would be worth a post.
Regards,
John Marsh
SELECT b.name, b.path,CAST(extensionSettings AS
XML).value('(//ParameterValue/Value)[1]',
'varchar(max)') AS Email,
CAST(extensionSettings AS XML).value('(//ParameterValue/Value)[3]',
'varchar(max)') AS Format,
a.lastruntime,enddate,laststatus, recurrencetype
from Subscriptions a inner join catalog b on a.report_OID = b.ItemID
inner join reportschedule c on a.subscriptionid= c.subscriptionid
inner join schedule d on c.scheduleid = d.scheduleid
where recurrencetype != 1 and (enddate > getdate() or enddate is null)
order by path, name
www.sql.lu
SQL Server Luxembourg User Group
August 20, 2008 at 8:29 am
The query works great in 2005 - thanks! While the same fields exist in 2000, the "CAST AS XML" doesn't seem to work (even running in 2005 Management Studio).
For my purposes at the moment, now that I know where the subscription recipients are I can simply do a "LIKE" statement against the extensionSettings fields in RS 2000. I'll probably play with it a little more and see if I can come up with something more elegant that I can run reports off of (that would be the next step...)
I did update the query you gave me; it turns out the recipient I was looking for was in the "cc" section rather than the "to" section. I also clarified which tables some of the fields are in - makes it easier for the next newbie that needs help!
RS2005 query:
SELECT b.name, b.path,
CAST(a.extensionSettings AS XML).value('(//ParameterValue/Value)[1]',
'varchar(max)') AS Email,
CAST(a.extensionSettings AS XML).value('(//ParameterValue/Value)[2]',
'varchar(max)') AS ccEmail,
CAST(a.extensionSettings AS XML).value('(//ParameterValue/Value)[3]',
'varchar(max)') AS Format,
a.lastruntime,d.enddate,a.laststatus, d.recurrencetype
from Subscriptions a inner join catalog b on a.report_OID = b.ItemID
inner join reportschedule c on a.subscriptionid= c.subscriptionid
inner join schedule d on c.scheduleid = d.scheduleid
where d.recurrencetype != 1 and (d.enddate > getdate() or d.enddate is null)
order by b.path, b.name
RS2000 query (short term solution to my temporary problem!) 😉
SELECT b.name, b.path,
a.extensionSettings,
a.lastruntime,enddate,laststatus, recurrencetype
from Subscriptions a inner join catalog b on a.report_OID = b.ItemID
inner join reportschedule c on a.subscriptionid= c.subscriptionid
inner join schedule d on c.scheduleid = d.scheduleid
where d.recurrencetype != 1 and (d.enddate > getdate() or d.enddate is null)
and a.extensionSettings like '%xxxemailxxx%'
order by b.path, b.name
Steph Brown
August 21, 2008 at 12:40 am
Hello,
Thanks for the update and the improved query. I'm also glad to hear you managed to extract the data that you needed.
Having checked in BOL ("Database Engine XML Enhancements"), the XML data-type was only introduced in SQL 2005 (hence the Cast won't work in SQL 2000).
If I get some time, I'll also try to re-engineer the query to work under SQL 2000, and then post it. Finding "some time" is the hard bit …
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
August 21, 2008 at 12:55 am
Yes, I thought I remembered XML being new with 2005. Some of the new features will work against 2000 databases if you run them in the 2005 Manager, but this apparently is not one of them.
I too have trouble finding the elusive "some time", along with it's even shyer cousin "more time". It took me a month just to find the time to write the original post! (Sad but true) And I thank you again for the quick response; I hate leaving users hanging for that long (even when I have no choice - project priorities and all).
Steph Brown
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply