THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

Search For a Query in RDL Files with PowerShell

In tracking down poorly performing queries for clients I often encounter the query text in a trace file I've captured, but don't know the source of the query. I've found that many of the poorest performing queries are those written into the reports the business users need to make their decisions. If I can't figure out where they came from, usually years after the queries were written, I can't fix them.

First thing I did was find a great utility called RSScripter, which opens up a Windows dialog that allows you to connect to a Reporting Services server and script the reports to RDL source files in a directory you specify. By exporting the RDL files from the Reporting Services server I can search through and find the queries I need to find.

The next thing I do is set a string variable to contain the query in question, which I do here using a here-string:

$q = @'SELECT  d.SalesOrderID,
        d.OrderQty,
        h.OrderDate,
        o.Description,
        o.StartDate,
        o.EndDate
FROM    Sales.SalesOrderDetail d
        INNER JOIN Sales.SalesOrderHeader h ON d.SalesOrderID = h.SalesOrderID
        INNER JOIN Sales.SpecialOffer o ON d.SpecialOfferID = o.SpecialOfferID
WHERE   d.SpecialOfferID <> 1'@

Now, when RSScripter runs it pulls out everything from RS, and I'm only interested in the RDL files so I use the Get-ChildItem cmdlet and pipe the output through the Where-Object cmdlet to select just those files whose extension is '.rdl', and assign that to a variable.

$rdls = Get-ChildItem | where-object {$_.Extension -eq '.rdl'}

Now I have a collection of RDL file objects, which I can iterate through with the foreach command. For each one I'm going to display the file name, then use the Get-Content cmdlet to read the contents of the RDL file, and pipe that to the Select-String cmdlet to look for the query string. If a match is found it'll display that line after the file name.

foreach ($rdl in $rdls) { write-output $rdl.Name; get-content $rdl.FullName | select-string $q }

While this may be a little crude, it's kind of an ad-hoc way of finding where a query comes from. There are recursive options for Get-ChildItem that'll search the entire subdirectory tree, but for now I didn't need that.

I hope this helps you find some of those troublesome queries you're struggling with.

Allen

Published Tuesday, October 16, 2012 3:58 PM by AllenMWhite

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Mark Vaillancourt said:

Cool stuff, Allen. You can do this without the export by querying the ReportServer database directly. Something this would work: http://markvsql.com/2009/11/find-all-deployed-ssrs-reports-that-reference-a-particular-field/

:)

October 16, 2012 3:12 PM
 

AllenMWhite said:

Thanks, Mark. I looked at the Catalog table but didn't realize that the varbinary was just a recast of a varchar(max).

October 16, 2012 3:30 PM
 

ALZDBA said:

Nice fast lane solution, Allen.

I used this one to just list all queries for rdl files on a given location:

<#

Since RDL is XML maybe just look for the query element

#>

$RptFilePath = "X:\ItsMe\My Documents\SQL Server Management Studio\Custom Reports\"

$ResultFile = "$env:temp\ReportQueries.csv"

$RDLs = Get-ChildItem -Path "$RptFilePath" | ? { $_.extension -eq '.rdl' }

$QueryReport = @()

foreach ( $F in $RDLs ) {

$RptFileName = $F.fullname

[xml]$Rpt =  Get-Content -Path $RptFileName

$RsDataSets = $Rpt.Report.DataSets.DataSet

$ctr = 0

foreach($ds in $Rpt.Report.DataSets.DataSet){

$ctr += 1

#create an empty row

   $row = "" | Select FileName, FullName, QryNo, DataSetName, QueryCommandText

$row.FileName = $F.name

$row.FullName = $RptFileName

$row.QryNo = $ctr

$row.DataSetName = $ds.Name

$row.QueryCommandText = $ds.Query.CommandText

   $QueryReport += $row

 }

}

$QueryReport | sort FullName, QryNo | Export-Csv $ResultFile -Delimiter ';' -NoTypeInformation

Just an alternative I wanted to share.

Thanks,

Johan

October 17, 2012 10:19 AM
 

Shan said:

Hi ALZDBA/Johan,

Thanks a ton for sharing the script! This is what exactly I was looking for. Great Help!!!

March 13, 2014 9:14 AM

Leave a Comment

(required) 
(required) 
Submit

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement