Category Archives: TSQL Scripts

SQLCodeBlock

SQLCodeBlock is a small tool that contains all day today scripts that database administrators uses for troubleshooting and normal SQL Server health checks. Its a small initiative to collaborate all scripts and assembled them into a windows based application.

You can download and install this tool on you local machine and run it any time, its very easy to use, just open this tool select category,select sub-category and then click fetch script and then click copy to clipboard. CodeBlock2CodeBlock3CodeBlock4

>>SQLCodeBlack Download<< One Drive

>>SQLCodeBlack Download<< Google Drive

Download the zip file and extract the files and click setup.exe to install it on your machine

Advertisements

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

 

 

How to get most frequent used top 10 reports on report server

SELECT TOP 10 COUNT(Name) AS ExecutionCount, Name,
SUM(TimeDataRetrieval) AS TimeDataRetrievalSum,
SUM(TimeProcessing) AS TimeProcessingSum,
SUM(TimeRendering) AS TimeRenderingSum,
SUM(ByteCount) AS ByteCountSum,
SUM([RowCount]) AS RowCountSum
FROM (SELECT TimeStart, Catalog.Type,Catalog.Name,TimeDataRetrieval, TimeProcessing,TimeRendering,ByteCount,[RowCount]
FROM
Catalog
INNER JOIN ExecutionLog ON Catalog.ItemID=ExecutionLog.ReportID
WHERE
ExecutionLog.TimeStart BETWEEN ‘2012-02-01 00:00:00.000’ AND ‘2013-03-28 23:59:00.000’
) AS RE
GROUP BY
Name
ORDER BY
COUNT(Name) DESC,
Name

How to get detailed execution history of reports on Report Server

SELECT  EL.TimeStart , EL.TimeEnd,DATEDIFF(s,EL.TimeStart , EL.TimeEnd) ‘Duration’ ,COALESCE(C.Path, ‘Unknown’) AS ItemPath, EL.UserName,EL.ExecutionId, CASE(EL.RequestType)
  WHEN 0 THEN ‘Interactive’  WHEN 1 THEN ‘Subscription’  WHEN 2 THEN ‘Refresh Cache’  ELSE ‘Unknown’END
   AS RequestType, — SubscriptionId
    EL.Format, Parameters, CASE(EL.ReportAction)   WHEN 1 THEN ‘Render’
    WHEN 2 THEN ‘BookmarkNavigation’  WHEN 3 THEN ‘DocumentMapNavigation’
     WHEN 4 THEN ‘DrillThrough’  WHEN 5 THEN ‘FindString’  WHEN 6 THEN ‘GetDocumentMap’  WHEN 7 THEN ‘Toggle’
      WHEN 8 THEN ‘Sort’  WHEN 9 THEN ‘Execute’  ELSE ‘Unknown’END AS ItemAction,EL.TimeStart, YEAR(EL.TimeStart)
       AS Start_Year,MONTH(EL.TimeStart) AS Start_Month,DATENAME(MONTH,EL.TimeStart) AS Start_Month_Name,
       DATENAME(DW,EL.TimeStart) AS Start_Day_Of_Week,DATEPART(WEEKDAY,EL.TimeStart)
       AS Start_Day_Number_of_Week,EL.TimeEnd, EL.TimeDataRetrieval, EL.TimeProcessing, EL.TimeRendering,
       CASE(EL.Source)  WHEN 1 THEN ‘Live’  WHEN 2 THEN ‘Cache’  WHEN 3 THEN ‘Snapshot’   WHEN 4 THEN ‘History’
        WHEN 5 THEN ‘AdHoc’  WHEN 6 THEN ‘Session’  WHEN 7 THEN ‘Rdce’  ELSE ‘Unknown’END AS Source,EL.Status,
        EL.ByteCount,EL.[RowCount],EL.AdditionalInfo,C.Name,C.CreatedByID,C.ModifiedByID,C.Description,
        C.CreationDate,C.ModifiedDate,CASE   WHEN C.TYPE=1 THEN ‘Folder’  WHEN C.TYPE=2 THEN ‘Report’
        WHEN C.TYPE=3 THEN ‘XML’   WHEN C.TYPE=4 THEN ‘Linked Report’  WHEN C.TYPE=5 THEN ‘Data Source’
         WHEN C.TYPE=6 THEN ‘Model’  WHEN C.TYPE=8 THEN ‘Shared Dataset’  WHEN C.TYPE=9 THEN ‘Report Part’END
         AS Type_Description
          FROM   ExecutionLogStorage AS EL  LEFT OUTER JOIN Catalog AS C ON
          (EL.ReportID = C.ItemID)
          WHERE
          C.Path like ‘%QuickTrend%’
                    AND
          EL.TimeStart BETWEEN ‘2012-02-01 00:00:00.000’ AND ‘2013-02-28 23:59:00.000’
          ORDER By 1

How to get Snapshot associated with the report

SELECT Reportname = c.Name
,SubscriptionDesc=su.Description
,Subscriptiontype=su.EventType
,su.LastStatus
,su.LastRunTime
,Schedulename=sch.Name
,ScheduleType = sch.EventType
,ScheduleFrequency =
CASE sch.RecurrenceType
WHEN 1 THEN ‘Once’
WHEN 2 THEN ‘Hourly’
WHEN 4 THEN ‘Daily/Weekly’
WHEN 5 THEN ‘Monthly’
END
,su.Parameters
FROM ReportServer_SQL2008_Prod.dbo.Subscriptions su
JOIN ReportServer_SQL2008_Prod.dbo.Catalog c
ON su.Report_OID = c.ItemID
JOIN ReportServer_SQL2008_Prod.dbo.ReportSchedule rsc
ON rsc.ReportID = c.ItemID
AND rsc.SubscriptionID = su.SubscriptionID
JOIN ReportServer_SQL2008_Prod.dbo.Schedule Sch
ON rsc.ScheduleID = sch.ScheduleID
WHERE c.Name = @ReportName

How to get subscription associated with the reports on Report Server

SELECT Reportname = c.Name
      ,SubscriptionDesc=su.Description
      ,Subscriptiontype=su.EventType
      ,su.LastStatus
      ,su.LastRunTime
      ,Schedulename=sch.Name
      ,ScheduleType = sch.EventType
      ,ScheduleFrequency =
       CASE sch.RecurrenceType
       WHEN 1 THEN ‘Once’
       WHEN 2 THEN ‘Hourly’
       WHEN 4 THEN ‘Daily/Weekly’
       WHEN 5 THEN ‘Monthly’
       END
       ,su.Parameters
  FROM ReportServer_SQL2008_Prod.dbo.Subscriptions su
  JOIN ReportServer_SQL2008_Prod.dbo.Catalog c
    ON su.Report_OID = c.ItemID
  JOIN ReportServer_SQL2008_Prod.dbo.ReportSchedule rsc
    ON rsc.ReportID = c.ItemID
   AND rsc.SubscriptionID = su.SubscriptionID
  JOIN ReportServer_SQL2008_Prod.dbo.Schedule Sch
    ON rsc.ScheduleID = sch.ScheduleID
WHERE c.Name = @ReportName

How to get details of data sources and associated command with reports on Report Server

;WITH XMLNAMESPACES
(DEFAULT ‘http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition&#8217;,
http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition&#8217; AS REP
)
SELECT c.Path ,c.Name ,DataSetXML.value(‘@Name’, ‘varchar(MAX)’) DataSourceName ,
DataSetXML.value(‘REP:Query[1]/REP:CommandText[1]’, ‘varchar(MAX)’) CommandText
FROM ( SELECT ItemID ,CAST(CAST(Content AS VARBINARY(MAX)) AS XML) ReportXML
FROM ReportServer_SQL2008_Prod.[dbo].[Catalog]
WHERE TYPE = 2
) ReportXML
CROSS APPLY ReportXML.nodes(‘//REP:DataSet’) DataSetXML ( DataSetXML )
INNER JOIN [dbo].[Catalog] c ON ReportXML.ItemID = c.ItemID
— Search by part of the query text
WHERE ( DataSetXML.value(‘REP:Query[1]/REP:CommandText[1]’, ‘varchar(MAX)’) ) LIKE ‘%quicktrend%’