Posts Tagged 'SQL Server Reporting Services'

Search SQL Server Reporting Services Report Definition

A little over a year ago, Raja and I were having a discussion about reporting services. The discussing revolved around writing the T-SQL in the report definition or creating stored procedures for each dataset and executing the stored procedure in the report definition.

However, one of the drawbacks of keeping the T-SQL in the report definition is that you cannot just simply search in syscomments to find objects that may be affected by a schema change. But searching the report definition is still possible! A report definition file is simply XML stored in the ReportServer database in the Catalog table. (It is stored using the image data type, even in SQL 2008. What is up with this, Microsoft? I thought the image data type was deprecated in favor of varbinary(max).)

So the first thing that we need to do is convert the image data to XML. You cannot explicitly convert image to XML, but you can explicitly convert image to varbinary and varbinary to XML, so that is what we will do. Next we will shred the XML using the nodes() method. This is the most effective method since a report definition can contain multiple data sets, and we want to capture all of them.

The SQL below will create a view with the report path, report name, data set name and the T-SQL for easy searching.

NOTE: The XML namespace may be different, depending on the version you are using. If this does not work for you at first, run the T-SQL from the derived table and click on the XML. The top line of the XML will contain the namespace corresponding to the version you are using.


USE [ReportServer]
GO
IF EXISTS (SELECT
 *
 FROM
 sys.views
 WHERE
 object_id = OBJECT_ID(N'[dbo].[ReportDefinition]'))
 DROP VIEW [dbo].[ReportDefinition]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[ReportDefinition]
AS
WITH XMLNAMESPACES
(
'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' AS RES
)
SELECT
 c.Path,
 c.Name,
 DataSet.value('@Name','varchar(MAX)') DataSourceName,
 DataSet.value('RES:Query[1]/RES:CommandText[1]','varchar(MAX)') CommandText
FROM
 (SELECT
 ItemID,
 CAST(CAST(Content AS varbinary(max)) AS xml) ReportXML
 FROM
 dbo.Catalog
 WHERE
 Type = 2) ReportXML
CROSS APPLY ReportXML.nodes('RES:Report/RES:DataSets/RES:DataSet') DataSetXML (DataSet)
INNER JOIN dbo.Catalog c
 ON ReportXML.ItemID = c.ItemID
GO

Happy coding!

~Ron

Advertisements

Installing (and Running) SSRS 2008 in Windows Vista

It’s been a while since I’ve played around with SQL Server Reporting Services, and I found a couple of interesting articles on using a report model with SSRS, so I decided to give it a go.

The installation went smoothly, as I have performed this task many times. However, when I visited the reporting services site I had no access. I am an administrator on the machine, and as such should have had access to the site.

I searched around for a good half hour and still hadn’t found anything that worked. Somehow I got the idea to run my internet browser as Administrator. Eureka! I added my local account to the SSRS instance and everything works very nicely.

~Ron