Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Identify All Reports That Have A Subscription Assigned??? (+ any underlying detail) Expand / Collapse
Author
Message
Posted Monday, February 4, 2013 3:56 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 11:55 AM
Points: 155, Visits: 479
Hi

EXCUSE MY NAIVETY IF THIS IS UNDER THE WRONG SECTION - Thanks

New to the RS so bear with me please. If anyone could help, greatly appreciated.

We have several reports (100+) that are scheduled (have a subscription) to go out either daily/monthly/ weekly etc but need to consolidate what we have.

In retrospect, I could have done in the old paper & pen / Excel way, but given the complexity of SQL Server (and it's off shoots), I feel that there could possibly be an easier way.

Q?
Is it possible to query RS (in perhaps a SQL SELECT... or similar) to show all current reports that have a SUBSCRIPTION assigned.

If the above is possible, would currently need to be able to SELECT/view the subscription details. IE: Scheduled times, Distribution Details, Start/End times etc etc.


i have tried connecting to reporting services via SQL Server Managment Studio in order to attempt querying but seem to get the following message:

SERVER NAME:= "XXXX-YYYY\ZZZ_REPORTS" (alias due to my work, sorry) but just get the following message.....

"TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

....... etc etc "

It also mentions the following two statements...

------------------------------
ADDITIONAL INFORMATION:

The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing.
(rsReportServerDatabaseUnavailable) (Report Services SOAP Proxy Source)

----------------------------

The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing.
(rsReportServerDatabaseUnavailable) (ReportingServicesLibrary)

----------------------------

ALSO

When i right click on the sever in question to look at the properties i get a similar message to the above??

A connection to the database is required for all requests and processing.
(rsReportServerDatabaseUnavailable) (ReportingServicesLibrary)


Sorry again if i come a cross as a bit of a novice.

Is this User permissions, and/or set up issues or something else????

Thanks in advance
Post #1415518
Posted Monday, February 4, 2013 4:12 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:37 PM
Points: 21,209, Visits: 14,898
You can use the following:
http://jasonbrimhall.info/2013/01/07/ssrs-subscriptions-report/




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1415522
Posted Monday, February 4, 2013 5:02 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 11:55 AM
Points: 155, Visits: 479
Many Thanks for your reply.

Just to check , i need to be in 'SQL server Man Studio' ?

I select OBJECT EXPLORER then CONNECT?

From there, I select 'REPORTING SERVICES' and then choose our prefered server?

This opens up the R_server within OBJECT EXPLORER with nodes of 'Jobs', Security' & 'Shared Scheds'.

Upon the right clicking of these nodes, i get various options but nothing that resembles what i see in 'DATABASE ENGINE'. (This is the area I am confident with, the rest of this is new to me, SORRY!!).

How do i run your suggested query against the RS database.

I need to check i have permissions to run said report, how would i find out ??

Again, thanks for being pacient with me, I'm self taught and every day is a new lesson (not to mention a headache)

Thanks again for any advice

P
Post #1415531
Posted Monday, February 4, 2013 9:06 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:37 PM
Points: 21,209, Visits: 14,898
Are you using reporting Services 2008 or 2005?




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1415562
Posted Tuesday, February 5, 2013 1:33 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 11:55 AM
Points: 155, Visits: 479
I am using 2008.

Thanks
Post #1415641
Posted Tuesday, February 5, 2013 6:07 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:37 PM
Points: 21,209, Visits: 14,898
Just connect to the database engine where your ReportServer database resides (no need to connect to reporting services in management studio).

Run the query from within management studio pointed at your reportserver database.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1415780
Posted Tuesday, February 5, 2013 3:03 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 11:55 AM
Points: 155, Visits: 479
Firstly, Thanks again for your time and help.

OK, spoke to the techies at work today, appparently i had insufficient acces rights.

This is now sorted, can connect to our RS Server and view the database and tables.

I have found the correct DB and tried running the script in your link.

It errors with the following....????

PS: Do you know of a schema/table definitions doc for the RS database

Msg 6850, Level 16, State 1, Line 25
Column name 'TEXT()' contains an invalid XML identifier as required by FOR XML; '('(0x0028) is the first character at fault.
Post #1416121
Posted Tuesday, February 5, 2013 3:13 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:37 PM
Points: 21,209, Visits: 14,898
Sounds like you may have miscopied the script. Paste the exact query you have decided to use. Also make sure you are running this against the ReportServer database.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1416122
Posted Tuesday, February 5, 2013 3:34 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 11:55 AM
Points: 155, Visits: 479
script used ...(thanks)

DECLARE @ReportName VARCHAR(100)
SET @ReportName = NULL;

CREATE TABLE #morepower (MonthDate BIGINT,N BIGINT,PowerN BIGINT PRIMARY KEY CLUSTERED
,NameofMonth VARCHAR(25),WkDay VARCHAR(25))
;

WITH powers(powerN, n) AS (
SELECT POWER(2,number), number
FROM master.dbo.spt_values
WHERE type = 'P' AND number < 31)

INSERT INTO #morepower ( MonthDate ,N,PowerN ,NameofMonth ,WkDay)
SELECT ROW_NUMBER() OVER (ORDER BY N) AS MonthDate,N,PowerN
,CASE WHEN N BETWEEN 0 AND 11
THEN DATENAME(MONTH,DATEADD(MONTH,N+1,0)-1)
ELSE NULL
END AS NameofMonth
,CASE WHEN N BETWEEN 0 AND 6
THEN DATENAME(weekday,DATEADD(DAY,n+1,0)-2)
ELSE NULL
END AS WkDay
FROM powers

SELECT DISTINCT s.ScheduleID,Ca.PATH AS ReportManagerPath,Ca.Name AS ReportName
, U.UserName AS SubscriptionCreator
,Su.Description AS SubscriptionDescription,S.StartDate,Su.LastRunTime
,CASE
WHEN s.RecurrenceType = 1 THEN 'One Off'
WHEN s.RecurrenceType = 2 THEN 'Hour'
WHEN s.RecurrenceType = 4 THEN 'Daily'
WHEN s.RecurrenceType = 5 THEN 'Monthly'
WHEN s.RecurrenceType = 6 THEN 'Week of Month'
END AS RecurrenceType
,s.EventType
,ISNULL(REPLACE(REPLACE(STUFF(
(SELECT ', ['+CONVERT(VARCHAR(20),MonthDate)+']' AS [TEXT()]
FROM #morepower m1
WHERE m1.powerN < s.DaysofMonth+1
AND s.DaysofMonth & m1.powerN <>0
ORDER BY N FOR XML PATH(''), TYPE).VALUE('.','VARCHAR(MAX)')
, 1, 2, ''),'[',''),']','')
,'N/A') AS DaysofMonth
,ISNULL(c1.NameOfMonth,'N/A') AS MonthString
,ISNULL(c2.WkDays,'N/A') AS DaysofWeek
,CASE MonthlyWeek
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
WHEN 5 THEN 'Last'
ELSE 'N/A'
END AS MonthlyWeek
,ISNULL(CONVERT(VARCHAR(10),s.DaysInterval),'N/A') AS DaysInterval
,ISNULL(CONVERT(VARCHAR(10),s.MinutesInterval),'N/A') AS MinutesInterval
,ISNULL(CONVERT(VARCHAR(10),s.WeeksInterval),'N/A') AS WeeksInterval
FROM #morepower mp, dbo.Schedule s
INNER JOIN ReportSchedule RS
ON S.ScheduleID = RS.ScheduleID
INNER JOIN CATALOG Ca
ON Ca.ItemID = RS.ReportID
INNER JOIN Subscriptions Su
ON Su.SubscriptionID = RS.SubscriptionID
INNER JOIN Users U
ON U.UserID = S.CreatedById
OR U.UserID = Su.OwnerID
CROSS APPLY (SELECT s.ScheduleID,REPLACE(REPLACE(STUFF(
(SELECT ', ['+ NameofMonth + ']' AS [TEXT()]
FROM #morepower m1 ,dbo.Schedule s1
WHERE m1.NameofMonth IS NOT NULL
AND m1.powerN & s1.MONTH <>0
AND s1.ScheduleID = s.ScheduleID
ORDER BY N FOR XML PATH(''), TYPE).VALUE('.','VARCHAR(MAX)')
, 1, 2, ''),'[',''),']','') AS NameOfMonth)c1
CROSS APPLY (SELECT s.ScheduleID,REPLACE(REPLACE(STUFF(
(SELECT ', [' + WkDay + ']' AS [TEXT()]
FROM #morepower m1 ,dbo.Schedule s2
WHERE m1.WkDay IS NOT NULL
AND DaysOfWeek & m1.powerN <>0
AND s2.ScheduleID = s.ScheduleID
ORDER BY N FOR XML PATH(''), TYPE).VALUE('.','VARCHAR(MAX)')
, 1, 2, ''),'[',''),']','') AS WkDays) c2
WHERE Ca.Name = ISNULL(@ReportName,Ca.Name);

DROP TABLE #morepower;
Post #1416134
Posted Tuesday, February 5, 2013 3:48 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:37 PM
Points: 21,209, Visits: 14,898
I see what happened. The value and text() all got UpperCased. They should be lower case like in the attached script

DECLARE @ReportName VARCHAR(100)
SET @ReportName = NULL;

CREATE TABLE #morepower (MonthDate BIGINT,N BIGINT,PowerN BIGINT PRIMARY KEY CLUSTERED
,NameofMonth VARCHAR(25),WkDay VARCHAR(25))
;

WITH powers(powerN, n) AS (
SELECT POWER(2,number), number
FROM master.dbo.spt_values
WHERE type = 'P' AND number < 31)

INSERT INTO #morepower ( MonthDate ,N,PowerN ,NameofMonth ,WkDay)
SELECT ROW_NUMBER() OVER (ORDER BY N) AS MonthDate,N,PowerN
,CASE WHEN N BETWEEN 0 AND 11
THEN DATENAME(MONTH,DATEADD(MONTH,N+1,0)-1)
ELSE NULL
END AS NameofMonth
,CASE WHEN N BETWEEN 0 AND 6
THEN DATENAME(weekday,DATEADD(DAY,n+1,0)-2)
ELSE NULL
END AS WkDay
FROM powers

SELECT DISTINCT s.ScheduleID,Ca.PATH AS ReportManagerPath,Ca.Name AS ReportName
, U.UserName AS SubscriptionCreator
,Su.Description AS SubscriptionDescription,S.StartDate,Su.LastRunTime
,CASE
WHEN s.RecurrenceType = 1 THEN 'One Off'
WHEN s.RecurrenceType = 2 THEN 'Hour'
WHEN s.RecurrenceType = 4 THEN 'Daily'
WHEN s.RecurrenceType = 5 THEN 'Monthly'
WHEN s.RecurrenceType = 6 THEN 'Week of Month'
END AS RecurrenceType
,s.EventType
,ISNULL(REPLACE(REPLACE(STUFF(
(SELECT ', ['+CONVERT(VARCHAR(20),MonthDate)+']' AS [text()]
FROM #morepower m1
WHERE m1.powerN < s.DaysofMonth+1
AND s.DaysofMonth & m1.powerN <>0
ORDER BY N FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)')
, 1, 2, ''),'[',''),']','')
,'N/A') AS DaysofMonth
,ISNULL(c1.NameOfMonth,'N/A') AS MonthString
,ISNULL(c2.WkDays,'N/A') AS DaysofWeek
,CASE MonthlyWeek
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
WHEN 5 THEN 'Last'
ELSE 'N/A'
END AS MonthlyWeek
,ISNULL(CONVERT(VARCHAR(10),s.DaysInterval),'N/A') AS DaysInterval
,ISNULL(CONVERT(VARCHAR(10),s.MinutesInterval),'N/A') AS MinutesInterval
,ISNULL(CONVERT(VARCHAR(10),s.WeeksInterval),'N/A') AS WeeksInterval
FROM #morepower mp, dbo.Schedule s
INNER JOIN ReportSchedule RS
ON S.ScheduleID = RS.ScheduleID
INNER JOIN CATALOG Ca
ON Ca.ItemID = RS.ReportID
INNER JOIN Subscriptions Su
ON Su.SubscriptionID = RS.SubscriptionID
INNER JOIN Users U
ON U.UserID = S.CreatedById
OR U.UserID = Su.OwnerID
CROSS APPLY (SELECT s.ScheduleID,REPLACE(REPLACE(STUFF(
(SELECT ', ['+ NameofMonth + ']' AS [text()]
FROM #morepower m1 ,dbo.Schedule s1
WHERE m1.NameofMonth IS NOT NULL
AND m1.powerN & s1.MONTH <>0
AND s1.ScheduleID = s.ScheduleID
ORDER BY N FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)')
, 1, 2, ''),'[',''),']','') AS NameOfMonth)c1
CROSS APPLY (SELECT s.ScheduleID,REPLACE(REPLACE(STUFF(
(SELECT ', [' + WkDay + ']' AS [text()]
FROM #morepower m1 ,dbo.Schedule s2
WHERE m1.WkDay IS NOT NULL
AND DaysOfWeek & m1.powerN <>0
AND s2.ScheduleID = s.ScheduleID
ORDER BY N FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)')
, 1, 2, ''),'[',''),']','') AS WkDays) c2
WHERE Ca.Name = ISNULL(@ReportName,Ca.Name);

DROP TABLE #morepower;





Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1416136
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse