“SQL Server Reporting Services (SSRS) Catalog Queries” CodePlex Project

I have received a fair amount of feedback and follow-up questions about my Extracting SSRS Report RDL (XML) from the ReportServer database blog post in which I show how you can simply get a Report’s (and other catalog items for that matter) XML out of the ReportServer.dbo.Catalog table. 

Once you have extracted the catalog item as XML, you can then leverage SQL Server’s built-in support for the XML data type and XQuery to further parse the XML.  At this point you need to have an understanding of the Reporting Services Report Definition Language XML Schema and a desire to extract specific pieces of information from a report definition. 

Based on the requests I received in my previous blog posts comments, and my own use of these queries in real-world situations, I decided it might be helpful to create a sample SQL Server Management Studio (SSMS) project that contained example objects and queries that could be used to work with the SSRS Catalog contents. 

To that end I have created a “SQL Server Reporting Services (SSRS) Catalog Queries” CodePlex Project (http://ssrscatalogqueries.codeplex.com/).  I hope to expand the project in the future, but for now, the SSMS project includes the following scripts:

  • "01 – Create the ReportQueries database.sql"
  • "02 – Query Catalog Content.sql"
  • "03 – Create the CatalogContent View.sql"
  • "04 – Find all Data Set Commands.sql"
  • "05 – Find all the Data Set Fields.sql"
  • "06 – Determine Fields Actually Used.sql"
  • "07 – Find all Connection Strings.sql"
  • "08 – Extract Connection String Components.sql"

Grab the project and play with the scripts.  If you don’t have the SQL Server 2008 R2 client installed, you won’t be able to SSMS project file (I created it using the SQL 2008 R2 version of SSMS), but you should still be able to open the .SQL script files directly in your current version of SSMS.

If you create a script that is useful, let me know and I will review it as a possible addition to the project.  Of course, make sure to let me know if you use it, like it, hate it, or have questions or changes!

Wouldn’t it be nice if SQL Server exposed the statement tree created by it’s parser?

If you have seen any of my previous blog posts on extracting SSRS Report XML from the ReportServer.dbo.Catalog table, you may have also noticed some of the comments hoping to determine the objects and columns that a report’s data sets reference.  It seems like that should be a simple task, but unfortunately it is harder than it should be. 

SQL Server has to answer that exact same question for itself when the statements are submitted.  SQL Server parses the SQL statements and generates an internal “tree” that represents the statement.  That tree includes a number of things, but certainly the names of the objects referenced by the command.  We could get something similar by using the plan information that is generated, but the plans don’t provide details on the higher level objects (views, etc) that are used in the query. 

I posted a suggestion in connect to have the team provide us with a system function that might return the objects that a statement depends on. 

Check it out, and vote it up if you agree:

https://connect.microsoft.com/SQLServer/feedback/details/709658/expose-sql-parse-tree

Azure ServiceConfiguration.cscfg Changes…

I just put the new November 10, 2011 (SDK v1.6) update of the Azure SDK tools down on my box and was attempting to walkthrough creating an Azure service by hand (not using Visual Studio).  I tried to run a very simple service using a ServiceConfiguration.cscfg file that looked like this:

When I did so I got a number of errors about missing settings:

<path>\ServiceConfiguration.cscfg: Error CloudService103 : The service configuration file does not provide a value for setting ‘?IsSimulationEnvironment?’ for role ‘HelloAzureSDK.Web’

<path>\ServiceConfiguration.cscfg: Error CloudService103 : The service configuration file does not provide a value for setting ‘?RoleHostDebugger?’ for role ‘HelloAzureSDK.Web’

<path>\ServiceConfiguration.cscfg: Error CloudService103 : The service configuration file does not provide a value for setting ‘?StartupTaskDebugger?’ for role ‘HelloAzureSDK.Web’

So it looks like it wants three configuration settings:

  • ?IsSimulationEnvironment?
  • ?RoleHostDebugger?
  • ?StartupTaskDebugger?

I’ve poked around a bit in the docs, and done a few searches, but haven’t had any luck finding details on these items. They appear to be some settings used by the Compute Emulator DFUI.exe, but I haven’t dug much deeper into how they are used, or what appropriate values for them should be.  Regardless, I don’t think we need to define them.  

I looked at the new ServiceConfiguration.Local.cscfg file produced by the Azure project templates in Visual Studio, and didn’t see any of the settings mentioned in the errors message, however it does have a <ConfigurationSettings>…</ConfigurationSettings> element and my original configuration file didn’t. I tried just adding an empty <ConfigurationSettings /> element to my manually created ServiceConfiguration.cscfg file and it worked.

Windows Phone SDK 7.1 available in ISO

Normally, when you install the Windows Phone SDK 7.1 you do it through the web installer located here:

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=27570

However, if you need to install it on a disconnected machine (like a VM with no network access) it’s helpful to have an .iso of the installation media to install from.

Well, while they don’t make it obvious, Microsoft also provides a download for the .iso as well.  You can get it from here (at least until the links change! :-) :

http://go.microsoft.com/fwlink/?LinkID=226694