Thursday, June 27, 2013

[SSRS] Get the SQL Query from your RDL via T-SQL

Coworker asked for this.

Basically we wanted to get the queries out of the RDLs on our Reporting Services server.  I saw a post originally that discussed using the tool to pull all the RDLs off of the machine, then parsing them.  Hey, waitasecond....

Immediately found the blog that showed how to use the double-convert to get back the XML.  Once I had that, I started querying the XML to get out what I wanted.  Now why am I getting NULL.....

So, after spending an hour banging my head against the XML and getting back NULL, I went to StackOverflow, whereupon a nice person named Mikael gave me the syntax I was looking for (thanks, Mikael!).  Turns out that you have to ignore the namespace, which means your syntax can get... weird (at least compared to what I normally use).

Overall, though, pleased how it turned out. Please let me know if you have any questions, comments or if there's more stuff I should show.  DataSource coming in a couple weeks; I have the code, just need to integrate it.

(Click on the "View Raw" to get the SQL in a new window.  Thanks!)