February 4, 2013 at 3:56 pm
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
February 4, 2013 at 4:12 pm
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 4, 2013 at 5:02 pm
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
February 4, 2013 at 9:06 pm
Are you using reporting Services 2008 or 2005?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 5, 2013 at 1:33 am
I am using 2008.
Thanks
February 5, 2013 at 6:07 am
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 5, 2013 at 3:03 pm
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.
February 5, 2013 at 3:13 pm
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 5, 2013 at 3:34 pm
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;
February 5, 2013 at 3:48 pm
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 5, 2013 at 3:55 pm
I have also added a download link on the blog with the properly formatted code.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 5, 2013 at 4:01 pm
Thank you ever so much. This is exactly what i needed.
Thanks Again.
P
February 5, 2013 at 4:03 pm
You are welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 5, 2013 at 4:10 pm
Just one last thing since you seem to know your stuff (tell me get lost of you want 🙂 )
Having gained access to the RS server and underying tables I'm trying to make sense of how they all relate.
looking at your script and also doing some analysis of fields names within the tables, i starting to make sense of the DB.
Do you happen to know of a schema/data dictionary for the RS database that would show the links beteen the tables etc..
If not, its OK, you've helped me more than enough.
Thanks again
P
February 5, 2013 at 4:13 pm
I have not found any good reliable documentation on the RS database.
Much of it comes from discovery and tinkering.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply