My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/How to get the disk usage report working
The new RSS feed is here
If you've downloaded the SSMS reports and want to get the Disk Usage report working. Follow these steps.
- Remove all comments from the data sources, it seems that RS ignores CRs when the SQL is specified as an expression. This means that anything that appears after the comment is treated as part of the comment when -- is used. The other option is to enclose the comment in /* */ this gives the comment a defined end and so the SQL can continue afterwards.
- Add an ; after any declare statment and a non declare statement. declare statements can occure after each other on the same line, however other statements require a command terminator or a new line. i.e. "declare @fred int declare @jones int" is fine but "declare @simon int select 1 from mytable" isn't but "declare @simon int; select 1 from mytable" is.
I'm sure this all stems from the use of XML to store the RDL definitions. Whitespace is rolled up/ignored unless CDATA is used to wrap the text and even then CRLFs are still converted to LF (if memory serves me I discuss this in depth in SQL Server XML Distilled)
... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/How to get the disk usage report working