x

Reporting service tsql syntax - commenting

Hi All

In a recent post I indicated that I've just moved into a new job (reporting) in a new organization and I've noticed some oddness that may or may not be significant. In the SQL that goes into the rdl files here, I've noticed that there is almost NEVER any commenting in the code. Furthermore, there is often a complete absence of easy-on-the-eye formatting, with everything jammed up against the left margin.

Am I missing something here? These people have years of experience with SSRS whereas I have less than 12 months and so I'm wondering whether there are any gotchas that mean you should not comment the code in RDLs the way you would in a *.sql file.

Regards GPO
more ▼

asked Aug 06, 2010 at 11:32 PM in Default

GPO gravatar image

GPO
2.9k 35 38 42

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

I've put comments into SSRS queries before without any problems. And formatting does get retained.

I suspect that the more complex queries are developed outside of SSRS before being copied in, or built up using the query designer, which may not be so well-behaved.
more ▼

answered Aug 07, 2010 at 12:09 AM

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

(comments are locked)
10|1200 characters needed characters left

@ThomasRushton I have discovered a gotcha (in SSRS2000 at least). If you put something in a comment that can be interpreted as a parameter it looks like you'll throw an error message. I had:

-- ? some comment 

in an rdl and got the following message

"An error occurred while executing the query. Incorrect syntax near '?'..."

Same thing occurs with

/*? some comment */
more ▼

answered Aug 09, 2010 at 06:23 PM

GPO gravatar image

GPO
2.9k 35 38 42

As an addendum to this (rather old) post, it is also worth noting that if you are pasting really long SQL into the textbox in SSRS that holds the code, it silently truncates after a certain number of characters. If this generates an error when you run the report, great, at least you know you have a problem. If, however, you're unlucky enough that the code is truncated somewhere that doesn't generate an error (say just before a UNION ALL, like happened with me) then you could have an invisible problem, with your report happily spitting out the wrong answer, and not tellign you it's wrong. Yet another good reason to use stored procedures.
Sep 06 at 04:22 AM GPO
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x991
x559
x10
x9

asked: Aug 06, 2010 at 11:32 PM

Seen: 1285 times

Last Updated: Sep 06 at 04:22 AM