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
coresql.in 