“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

WMIPing Utility

I am working on a project that needs to connect to the SQL Server Reporting Services WMI Provider on remote machines.  I have had to work with a number of issues around security and needed a simple tool to help me diagnose them.  To that end I wrote a simple command line application in C# that allows me to pass in a target WMI Namespace as well as optional user name, password, and domain name values to test connectivity.  I thought some others might find it useful as well so I am posting it here.

You can download my WMIPing utility and the .NET (Visual Studio 2008) source code here.

If you are learning to use WMI from .NET, this may also server as a simple sample project.

Let me know if you use it, like it, hate it, want to change it, etc. However, I reserve no rights so feel free to change it on your own as you wish.

There is a readme included with the utility, but the usage is:

WMIPing <TargetNamespace> [<UserName>] [<DomainName>]

Where:
  <TargetNamespace> = The target WMI namespace to connect to.
                      For example:

                      "root\cimv2"

                      To ping a namespace on a
                      remote machine start the namespace with the target
                      machine name.  For example:

                      "\\<remotemachinename>\root\cimv2"

                      For a remote machine named "MyServer":

                      "\\MyServer\root\cimv2"

  [<UserName>]      = The optional user name to use when connecting remotely
                      You will be prompted for the password to use
  [<DomainName>]    = The domain to use when connecting remotely.
                      Requires <UserName>

FYI, If you are in the same boat as me and need to connect to a SQL Server Reporting Services instance, you can use this utility to test connectivity to the namespace.

WMIPing "\\<ServerName>\root\Microsoft\SqlServer\ReportServer\RS_<InstanceName>\v10" (assuming it is SQL 2008 or SQL 2008 R2)

In my dev environment, my reporting services server name is called “SQLVM” and I have a default instance (MSSQLSERVER) and named instance named “SQL2008”. 

Testing connectivity to the default instance (MSSQLSERVER) on the SQLVM server would look like:

WMIPing "\\SQLVM\root\Microsoft\SqlServer\ReportServer\RS_MSSQLSERVER\v10"

Connecting to the “SQL2008” named instance would look like:

WMIPing "\\SQLVM\root\Microsoft\SqlServer\ReportServer\RS_SQL2008\v10"

etc.

The WMIPing utility doesn’t do anything other than test that you can successfully connect from your client machine to the desired WMI namespace on the target server.

Extracting SSRS Content using the SSIS “Export Column” Component

Download the sample project for this blog post (29kb)

UPDATE – An updated version of this project has been published to codeplex at: http://ssrscatalogqueries.codeplex.com/.  The updated version extracts ALL types of content (not just XML, but resources as well) and also exports the folder structure.  It’s really cool!

A couple of months back I posted an article on “Extracting SSRS Report RDL (XML) from the ReportServer Database”.  Recently, Jason Brimhall posted an article that used similar logic to do a “SSRS Export En Masse”.  It’s a great example of how queries against the ReportServer.dbo.Catalog table can be used to recover reports that have been published to the report server, even if you have lost the source files. 

Jason’s blog post used a combination of Transact-SQL, a For Each loop, and VB.NET Script task to do the work.  As I read through his post, I wondered if the SSIS “Export Column” data flow transform help solve the problem and eliminate VB.NET code.  Turns out it can, and it makes for a pretty simple package.

---

Note, here is the source query I will use for my SSRS content.  This is based on the queries discussed in my “Extracting SSRS Report RDL …” blog post.  I’ll refer to this query as the “Extract Query” in my list of instructions below.

-- BEGIN EXTRACT QUERY --
WITH ItemContentBinaries AS
(
  SELECT
     ItemID,Name,[Type]
   ,CASE Type
      WHEN 2 THEN 'Report'
      WHEN 5 THEN 'Data Source'
      WHEN 7 THEN 'Report Part'
      WHEN 8 THEN 'Shared Dataset'
      ELSE 'Other'
     END AS TypeDescription
   ,CONVERT(VARBINARY(MAX),Content) AS Content
   FROM ReportServer.dbo.Catalog
   WHERE Type IN (2,5,7,8)
),
--The second CTE determines the appropriate file extension to use
--plus it strips off the BOM if it exists...
ItemContentNoBOM AS
(
  SELECT
     ItemID,Name,[Type],TypeDescription
    ,CASE Type
       WHEN 2 THEN '.rdl'  --Report Definition Language
       WHEN 5 THEN '.rds'  --Report Data Source
       WHEN 7 THEN '.rsc'  --Report Server Component (? - Guessing)
       WHEN 8 THEN '.rsd'  --Report Server Data (? - Guessing)
     END AS ExportFileExtension
    ,CASE
      WHEN LEFT(Content,3) = 0xEFBBBF
        THEN CONVERT(VARBINARY(MAX),SUBSTRING(Content,4,LEN(Content)))
      ELSE
        Content
    END AS Content
  FROM ItemContentBinaries
)
--The outer query gets the content in its varbinary, varchar and xml representations...
SELECT
   Name + ExportFileExtension AS ExportFileName
  ,CONVERT(xml,Content) AS ContentXML         
FROM ItemContentNoBOM
--  END EXTRACT QUERY  --

---

Here are the steps I followed.  You can download my SSIS project here.

1. Create a new Package in an SSIS BIDS Project. I named my new package “Export SSRS Content.dtsx”
2. Create a target folder that the SSRS Content will be exported to.  I created a folder named “C:\SSRS Content Extracts”
3. Add a Variable to the SSIS Package to store the path to the directory you just created as a string.  I created a variable named “OutputDirectory” and set its default value to “C:\SSRS Content Extracts\” (note the that I included the trailing slash)
4. Add a “Data Flow” task to your control flow.  I named mine “SSRS Content Extract”
5. Add a Connection Manager that points to the ReportServer database.  I named mine “ReportServer DB”
6. Add an “OLE DB Source” to your data flow
   1. I named mine “SSRS Content Source”
   2. Set it to use the “ReportServer DB” connection manager we just created
   3. Set the “Data Access Mode” to “SQL Command”
   4. I pasted the “Extract Query” shown above as the “SQL command text”
7. Add a “Derived Column” transform to the dataflow and name it “Generate Export Path”. Drag the green data path from the “SSRS Content Source” to it.
8. Double click on the “Generate Export Path” transform and add a derived column with the following properties (don’t include the double quotes around the values shown below):
   1. Derived Column Name: “ExportPath”
   2. Derived Column: “<add as a new column>”
   3. Expression: “(DT_WSTR,2048)(@[User::OutputDirectory] +  [ExportFileName])”
   4. The Data Type and Length will be set for you based on the expression above. (DT_WSTR, 2048)
9. Add an “Export Column” transform to the dataflow and name it “Export Content”.  Drag the green data path from the “Generate Export Path” transform to it.
10. Double click on the “Export Content” transform and configure the values as follows (again, don’t include the double quotes):
    1. Extract Column:  “ContentXML”
    2. File Path Column: “ExportPath”
    3. Allow Append: Cleared
    4. Force Truncate: Checked
    5. Write Byte-Order Mark: Cleared

There you have it.  Pretty simple.  A couple of things to note. 

• The expression I used in step 8.3 assumes that the “OutputDirectory” variable value includes the trailing forward slash.
• The “Force Truncate” checkbox set in step 10.4 causes existing files to be overwritten

The resulting data flow looks like this:

You can now run this package (and even supply an alternative export path via the OutputDirectory variable) to export all reports, data sources, report parts, and shared datas sets from an SSRS 2008 R2 installation. 

Download the BIDS project here.

Kinect SDK and the Speech API on a 64bit development machine…

I just finally had time to hook my Kinect up today and start playing with the Kinect SDK Beta.  So far, it is a ton of fun.  However, I had a problem with the ShapeGame sample application not being able to load the Speech libraries.  If you are having the same problem, before you read any further read and watch this and you will likely solve your problem:

Setting Up Your Development Environment on Channel 9. 

I didn’t read the above until the end of my install / uninstall / reboot / reinstall / uninstall / etc.. adventure.  I am running Windows 7 64bit.  Since I was running 64 bit I had downloaded and installed the 64 bit versions of the libraries.  However, when I ran the ShapeGame sample project, I got the following along the bottom:

“One or more of the Speech prerequisites has not been installed.  Please consult the README for more information.”

The actual problem is identified by a PlatformNotSupportedException in the Recognizer class (Recognizer.cs) in the constructor.  When this line of code executed:

            sre = new SpeechRecognitionEngine(ri.Id);

It would throw the exception.  Turns out that (at least with the Kinect SDK) that the 32bit (x86) versions are required.  Once I installed the 32bit (x86) versions of the following (as specified in Setting Up Your Development Environment) all was well. 

Microsoft Speech Platform - Software Development Kit v10.2 (x86)
Microsoft Speech Platform Runtime Version 10.2 (x86)
Microsoft Kinect Speech Platform

Maybe I should go review the other getting started docs.  Might save me some time….