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

November 30th, 2011 Posted in CodePlex, SQL Server, SSRS
3 Comments »

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!

Read More

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

November 30th, 2011 Posted in SQL Server
No Comments

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

Read More

Adventure Works Logo Re-do

December 1st, 2010 Posted in Adventure Works, SQL Server, SSRS
2 Comments »

Ok, so I’m a nerd.  I’m also a Microsoft Certified Trainer, and I teach a lot of SQL courses so as a result I work with AdventureWorks databases a fair amount.  When I am building reports in SSRS that use AdventureWorks I like to have a nice looking logo to use, and the ones that are provided with the curriculum or are available online leave much to be desired. 

I took it upon myself to create a higher resolution version of the logo files, and thought I would post them here for other control-freak-nerds like myself to use.  As long as Microsoft doesn’t give me grief about copying and re-distributing their logo, then I’ll leave this up here.

Use the links below to download a .zip file with illustrator, corel, photoshop, xaml, jpg, png, and other formats of the logo.  Enjoy.

AdventureWorks Logo AdventureWorksLogo.zip (448k)

Read More

Introduction to SQL Server Reporting Services Session Slides…

April 24th, 2010 Posted in SQL Saturday, SQL Server, SQL Server 2008, SSRS
1 Comment »

Here is the slide deck from the Introduction to SQL Server Reporting Services sessions I gave at SQL Saturday 44 in Huntington Beach, CA earlier today!  Thanks to everybody who came, and sorry for the technical problems getting the Report Manager to load.  It’s back to the same problem I was having about port 80 not getting registered right on Windows 7.  I thought I had that fixed, but I guess not. 

Anyhow grab the slides, and drop me a comment or an email if you have a question!

Slides Screenshot
Introduction to SQL Server Reporting Services 

Thanks again to all the SQL Saturday 44 organizers.  It was a great event!

Read More

SQL Server Reporting Services 2008 for .NET Developers Presentation…

February 9th, 2010 Posted in SQL Server, SQL Server 2008, SSRS
No Comments

I’ll be speaking at the Orange County .Net user group (www.ocdotnet.org) this evening (02/09/2010) on SSRS 2008 for .NET Developers.  I’ll cover the range of options that developers have for enhancing, extending and integrating SQL Server Reporting Services. 

You can get the slide deck, and the sample project for my session here:

image 
SQL Server Reporting Services for .Net Developers (6.6 MB)

Read More

T-SQL GO [Count] – New to me!

August 29th, 2009 Posted in SQL Server, SQL Server 2008
No Comments

Ok, I learned a cool feature of the GO command in SQL Server client tools (osql, sqlcmd and SQL Server Management Studio).  That is that you can add a number [Count] after the GO keyword.  The client tool will then execute the batch that precedes the GO the number of times specified by the [Count] value.

It looks like this was added back in SQL 2005, and I just never noticed it.   So, it’s new to me!

For Example, the following batch submitted in a SQL Server Management Studio query window

SET NOCOUNT ON  –just to cut down on the output…
SELECT ‘Hey, that’’s cool!’ AS Neat
GO 3

Produced the following output:

Beginning execution loop
Neat
—————–
Hey, That’s Cool!

Neat
—————–
Hey, That’s Cool!

Neat
—————–
Hey, That’s Cool!

Batch execution completed 3 times.

You can read the docs at:  http://msdn.microsoft.com/en-us/library/ms188037.aspx

Read More

Introduction to SQL Server for Windows Administrators…

February 11th, 2009 Posted in SQL Server, SQL Server 2008
No Comments

I presented at the WiNSUG user group here in San Diego last week (Feb 5th, 2009).  My topic was “Introduction to SQL Server for Windows Administrators”.  It was pouring rain, so I really appreciate the folks that made it to the meeting.

I have posted the slide deck from my session up on my website so feel free to download it if you would like: icon_pptx “Introduction to SQL Server for Windows Administrators”

Slide Title Screen Shot

Read More

Reporting Services Architecture Diagram…

February 11th, 2009 Posted in SQL Server, SQL Server 2008, SSRS
7 Comments »

I have a standard diagram that I draw on the board when I teach reporting services classes.  I have lately however been teaching some classes online and drawing this diagram by hand on the screen gets a little tedious.  To simplify my life, I drew it in Visio so I could re-use it.  I am posting it up here so that my students, or anybody else for that matter can grab the diagram and re-use it. 

It represents a typical SQL Server 2005 native reporting services deployment.  This also describes the basic structure of a 2008 install, just replay IIS with HTTP.SYS) I may update it in the future, or create another version to include SharePoint installations. 

Use the links below to download the image.  If you update it, or have suggestions for improving it let me know! 

Reporting Services Overview Diagram for Blog

 icon_zip  Download the original Visio Diagram as well as a JPG and PDF (0.5MB)

Read More

Applying a Collation to Columns in a View….

February 10th, 2009 Posted in SQL Server
1 Comment »

Steve Evans asked me a question today that was kind of interesting.  His question was something like:

“I have a view that returns data from multiple tables.  I want two of the columns returned through the view to be case sensitive.  Can I just have the view treat those two columns as case sensitive, or do I have to define those two columns on the tables as case sensitive?”

Or more generically, “Can I return a column in a view with a different collation than the source column?”.

The short answer, is that yes, we can enforce a collation on a column in a view that is different than the collation on the source columns.

The following code creates a view that returns the ProductID column, and two versions of the Name column from the AdventureWorks.Production.Product table.  The first Name column is left to the same collation as the source column, but the second Name column (aliased as NameSensitive) specifies a case sensitive collation for the column in the view. 

USE AdventureWorks;

GO

CREATE VIEW

  Production.ProductSensitive

AS

SELECT

  ProductID,

  Name,

  Name COLLATE SQL_Latin1_General_CP1_CS_AS AS NameSensitive

FROM Production.Product;

 
The key piece is the line:
 

   Name COLLATE SQL_Latin1_General_CP1_CS_AS AS NameSensitive

Most database developers know collations can be applied to databases and columns, but many don’t realize that we can apply collations to expressions as well.  That is exactly what we are doing in that line.  We are applying the case sensitive collation SQL_Latin1_General_CP1_CS_AS to the expression in the select list. 

After running the code above to make the view, we can test that it works correctly by running the following statements:

–Should match at least one row. The [Name] column

–is not case sensitive

SELECT * FROM Production.ProductSensitive

WHERE Name=‘awc logo cap’;

 

–Won’t match any rows because [NameSensitive] is

–case sensitive

SELECT * FROM Production.ProductSensitive

WHERE NameSensitive=‘awc logo cap’;

 

–Will match rows because [NameSensitive] is

–case sensitive, and the ‘AWC Logo Cap’ literal

–uses the proper case.

SELECT * FROM Production.ProductSensitive

WHERE NameSensitive=‘AWC Logo Cap’;

Anyhow, that was a fun question to answer, and thought it might be of either real use, or at last theoretical use to others.  Let me know if you end up using somewhere and why. 

Read More

Using a Recursive CTE to generate a contiguous sequence of values…

December 4th, 2008 Posted in Common Table Expressions, CTEs, SQL Server
2 Comments »

I am working on a chapter for Syngress’ 70-433 test prep guide, and talking about character sets.  In my research I wanted to write a query that would return the values 0 through 255 and the the character associated with each of those numbers.

Yeah if I had a table Like AdventureWorks.Person.Contact where there was a set of 256 contiguous values, I could highjack those values for my source numbers, but that seems much less elegant.

Here is what I came up with:

WITH numbers AS
(
  SELECT 0 AS Number
  UNION ALL
  SELECT Number + 1
  FROM Numbers
  WHERE Number + 1 <= 255
)
SELECT Number, CHAR(Number) AS Character FROM numbers
OPTION (MAXRECURSION 255);

Note the OPTION (MAXRECURSION 255) at the end.  Without that it will error after the first 100 rows (really its after the 100th recursion along this branch, but I digress) by default. But that option will cause it to error if I don’t stop the recursion myself at 255, hence the WHERE Number + 1 <= 255 where clause on the recursive query definition in the CTE.

Anyhow, if you run the query above you will get a result similar to the following

Number  Character
—— ———
…     …
63      ?
64      @
65      A
66      B
67      C
…     …
97      a
98      b
99      c
…     …
253     ý
254     þ
255     ÿ

 

Enjoy!

Read More
Older Entries