Run a SCCM Web Report Query in SQL Mgt Studio

During the course of diagnosing a (possible) bug in a particular SCCM 2007 R2 SP2 web report, I did what I usually do: copy the SQL statement from the report definition into SQL Server Management Studio (SSMS) and run it as an ad hoc query.  Someone asked how to do this because they run into problems with parameterized queries.  Actually, they probably asked me to explain this so I would appear nerdier than they are.  Well, it's really easy, sort of.

First, in the SCCM Admin console, open the properties for the web report, select all the SQL query code and copy it to the clipboard.  Open SSMS, expand the databases and select the SCCM database and click New Query.  Paste the code into the editor window.  Look for all the @variable references in the query.  For each one, add a SQL scalar variable declaration and assign it a value using the following syntax:

Declare @variableName DataType(size)

The trick is knowing the type and size of the appropriate variable.  The easy way to do this is in SSMS is to find the view or table, expand it, then expand the "Columns" collection and look at the column properties.   For example, the CollectionID column for most tables/views is VarChar(8) so if your parameterized query is referencing a Collection ID value, it might look like this…

USE SCCM_ABC

Declare @collID Varchar(8) = 'SMS00001'

(Note: The "ABC" above should be replaced with your 3-char site code).  As soon as the rest of the variables in your SQL code are declared, you should be able to click the Execute link and run the query directly against the data source in SQL Server.  If it runs and displays results, jump out of your chair and scream "IN YO FACE!!!" directly into the face of your nearest co-worker.  They love that.

Before you flip out and spit your bong water all over the computer screen in disgust, please know this:

1. I know there are other ways to do this and this is not the only (or best) way.

2. I know it's dangerous to tinker in SQL Server with SCCM tables.  Driving is dangerous too.  People die from falling down stairs every day also.  My middle name is Danger.  Actually, it's Michael, but whatever.  It can be dangerous to drive while tinkering with SQL SCCM tables, especially on a bicycle.  So, I'm not being that dangerous am I.