Programmatically get SSRS Database Name

Question

Post reply

Programmatically get SSRS Database Name

Steve Smith-163358

SSCommitted

Points: 1750
More actions
November 15, 2013 at 5:47 am

#301275

Hi,

I have a feeling I've asked this before, but I can't find the details anywhere

I need a way to programmatically get the Report Server Database name.

I can open the Reporting Services Configuration Manager, and see the database in there, however, ideally I need to get this via code or command line so that I can pass it to another application.

I've tried getting it via the web service, but it doesn't appear to pass that information.

The rsreportserver.config file contains the information, but the DSN section is encrypted

Is there any way to get the database value other than using the GUI?

Thank you

Steve

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

This could do this trick:



IF OBJECT_ID('tempdb..##ssrs_candidates') IS NOT NULL DROP TABLE ##ssrs_candidates;

CREATE TABLE ##ssrs_candidates (c_id int identity, ssrs_db varchar(100));

EXEC sp_msforeachdb'

INSERT INTO ##ssrs_candidates

SELECT TABLE_CATALOG

FROM [?].information_schema.columns

WHERE	TABLE_NAME =		''Catalog''

AND		COLUMN_NAME =		''ItemID''

AND		ORDINAL_POSITION =	1

AND		DATA_TYPE=''uniqueidentifier''

'

SELECT * FROM ##ssrs_candidates

I am not a fan of sp_msforeachdb but this is the down & dirty quick way to get you what you need.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Steve Smith-163358 SSCommitted Points: 1750 More actions · Answer 2

Hi Alan,

Thank you very much for the reply.

Unfortunately, it doesn't really help me in my specific case, but has given me an idea which has helped me deal with another issue I was having, so thank you very much for that.

My main issue is that we have numerous test instances of SSRS, and therefore multiple Reporting Services databases.

We also have an application that generates reports using SSRS.

However, it's not always immediately obvious to identify which instance of SSRS, and which database is being used by this application

As such, I was planning to write a separate .net application to extract that information.

I can get the SSRS instance, but retrieving the specific database being used by that instance programmatically was proving difficult.

It's obviously easy to get that information manually from the Report Services configuration Manager.

However, the database information is written to the RSReportServer.config file, and is encrypted in the DSN section of the xml file.

As such, I was wondering if there was any programmatic way to specifically identify which database is being used by an instance of SSRS.

While the script you have provided will identify the SSRS databases on a SQL Instance, I can't see any way to tie that database back to a specific instance of SSRS

Again, thank you for your help

Steve