How to read and parse rsreportserver.config file for Report Server in SSRS



 

DECLARE @ExecCmd VARCHAR(255)

     
DECLARE @y INT

     
DECLARE @x INT

     
DECLARE @FileContents
VARCHAR(MAX)

     
DECLARE @xmlHandle
INT

     
DECLARE @FILENAME
NVARCHAR(1000)

           
CREATE TABLE #configXML

                 
(

                   
PK INT NOT NULL

                              
IDENTITY(1, 1) ,

                   
[XMLValue] VARCHAR(MAX)

                 
)

SET @FileName = 'C:\Program Files\Microsoft SQL Server\MSRS11.SQL01\Reporting
Services\ReportServer\rsreportserver.config'

                
SET @ExecCmd = 'type ' + '"' + @FileName + '"'

                 
SET @FileContents
= ''

                       
INSERT  INTO
#configXML

                                         
EXEC MASTER.dbo.xp_cmdshell @ExecCmd

                                   
SELECT  @y
= COUNT(*)

                                  
FROM    #configXML

                 
SET @x = 0

                 
WHILE @x <> @y - 1

                                              
BEGIN

                       
                              SET @x = @x + 1

                                                     
SELECT  @FileContents
= @FileContents
+ [XMLValue]

                                                     
FROM    #configXML

                                                     
WHERE   PK
= @x

                                               
END

     
-- Display the file contents

           
SELECT  @FileContents
AS FileContents

           
DROP TABLE #configXML

    
-- Parsing the config file XML

     
EXEC sp_xml_preparedocument @xmlHandle OUTPUT, @FileContents

    
SELECT  *

    
FROM    OPENXML
(@xmlHandle, '//Service', 2) WITH

     (

     
IsSchedulingService VARCHAR(255) 'IsSchedulingService',

     
IsNotificationService VARCHAR(255) 'IsNotificationService',

     
PollingInterval VARCHAR(255) 'PollingInterval',

     
WindowsServiceUseFileShareStorage VARCHAR(255) 'WindowsServiceUseFileShareStorage',

      
  RECYCLETIME      
VARCHAR(255)      'RecycleTime'

      
  )

--This will provide you the value of your 2 parameters for
ReportServer URL and Report Manager URL

--The first value would be Report Server output and the 2nd value
is Remport Manager.

     
SELECT  *

     
FROM    OPENXML
(@xmlHandle, '//URL', 5) WITH

      (

     
UrlString VARCHAR(255) 'UrlString',

     
AccountName VARCHAR(255) 'AccountName'

    
)

      
 --This will give you important information
about authenticaion, MaxActiveReqForOneUser, and database timeout

 

      
       Select *

      
      From  OpenXML (@xmlHandle, '//Configuration',0)

             
        Where
ID in (854 ,855,856,857,74,75)

       --This will
provide extension informaiton

 

              SELECT
localname,text

              FROM
OPENXML(@xmlHandle, '//ModelGeneration',0)

              where
localname='#text'

 

EXEC sp_xml_removedocument @xmlHandle

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s