Tag / SSRS

by Arvind Shyamsundar

PowerShell script to extract T-SQL code from RDL files

One of the things I do in my day job at Microsoft Services is to review database design and T-SQL code. Now, when it comes to code, there is a challenge just to find ALL the possible code which hits our database engine. This is because a non-trivial amount of T-SQL code is not in the form of Stored Procedures, but it is embedded inside other places – such as Reporting Services (SSRS).

Now if you have hundreds of RDL files, extracting T-SQL from those files is a very tedious job. Opening each file in Report Designer, you would have to comb through each dataset – a tedious and error prone procedure. This is where the knowledge of the RDL format is helpful. Luckily for us, the specification for RDL is documented and available publicly. Using this knowledge, I constructed a quick (and perhaps dirty Smile) PowerShell script to scan through multiple RDL files and extract the T-SQL command text.

To use the script just invoke it from PowerShell command prompt and pass in the root folder path (-RootFolder) where your RDL files are stored. The –Recurse parameter is also useful in case you have many subfolders where RDL files are stored.

Do leave comments on whether this script was useful for you!

This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment.  THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.  We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.

This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm.

Advertisement
by Arvind Shyamsundar

Microsoft Report Viewer 2012 update: a ‘gotcha’ to be aware of

Something which I have been waiting for a long time has finally been released! The ReportViewer 2012 redistributable RTM package is available for download now!

Deployment notes

FYI, ReportViewer is used by Management Studio (SSMS), other utilities and also by any custom application which uses this to render local RDLC reports, or within a web application to view remotely rendered reports.

SQL 2012 installation deploys ReportViewer if the Management Tools are selected for installation. The other shipping vehicle for the ReportViewer control is Visual Studio 2012. This blog post pertains more to the case where we installed ReportViewer through the normal SQL 2012 installer.

FYI, you can view the ReportViewer 2012 assembly version at C:windowsassemblyGAC_MSILMicrosoft.ReportViewer.WinForms11.0.0.0__….. Right clicking on the assembly, and viewing the Details tab will give you the version of the DLL.

Note the ‘gotcha’

Now, the updated runtime release will deploy the equivalent of SQL 2012 SP1 binaries, so you get the latest and greatest bits! These should deploy a 11.0.3010 version for the Microsoft.ReportViewer.WinForms.dll file (and other files as well.)

Now, here’s the ‘note from the field’ thing which you can only get from me Smile If you just install SQL 2012 SP1 (without later running the above download) it does not seem to update the ReportViewer control. Normally this may not have much visible impact, but if you are like me, you may want to keep the runtime up to date due to the number of important fixes in such updated versions.

Test case

In my tests, just applying SQL 2012 SP1 installation did NOT upgrade the runtime to 11.0.3000. It was still at 11.0.2100. However, applying the above updated runtime MSI will upgrade the runtime to 11.0.3010.

Your checkpoint is that the version of ReportViewer 2012 assembly under C:windowsassemblyGAC_MSILMicrosoft.ReportViewer.WinForms11.0.0.0__….. should finally be 11.0.3010 or higher. (repeat this check for the other controls such as Microsoft.ReportViewer.WebForms as well.

Conclusion

So in short, if you use ReportViewer – either indirectly (like in SSMS) or directly (through custom applications developed using Visual Studio 2012) it is highly recommended to update your RTM ReportViewer 11.0 runtime to the latest version using the MSI from the download link.

by Arvind Shyamsundar

Hosting ReportServer catalog DBs on older version of SQL database engine

A FAQ we get is whether it is supported to have the ReportServer and ReportServerTempDB (catalog databases used in SSRS) on an older version of SQL Server (as compared to the version used on SSRS.) Here is the summary answer along with the reference links:

For SSRS 2012, it is supported to install the catalog databases on versions all the way down to SQL 2005. Reference: http://msdn.microsoft.com/en-us/library/ms157285.aspx

For SSRS 2008 & R2, the same story – catalog DB can be all the way down to SQL 2005. Reference http://msdn.microsoft.com/en-us/library/ms157285(v=sql.105).aspx. There is a ‘other versions’ link on this page, use that to navigate to the reference for SQL 2008 and 2005 as well.

One topic related to this is when you have SSRS 2008 R2 in the SharePoint 2010 ‘Connected Mode’. Please refer to the blog post http://blogs.msdn.com/b/prash/archive/2010/04/26/integrating-ssrs-r2-rtm-with-sharepoint-2010-rtm.aspx for more details on this scenario.

Thank you for reading!

Arvind.