The two types of comment notation in TSQL, namely "--" and "/* . . . */" are not entirely the same, and in certain places using the wrong one ( "--") can get you in big trouble.
Here is the scenario: My customer was working on creating a fairly simple report that had a somewhat complex query behind it. The query had originally been developed and debugged in SSMS, where it ran just fine.
He then pasted the tsql code into the query portion of the dataset in SSRS, using the "show as text" option. When run from there, it ran just fine. But when he tried to preview the report, it said that it couldn't process the dataset. So he contacted me.
I won't go into all the things I tried that didn't work, but here is the bottom line:
When he was developing this complex query he had put in a check and then commented it out using the "--" style comment. As long as the query was run in a formatted fashion, it ran fine, but when SSRS went to run the query, it didn't run it in a formatted fashion! Once SSRS saw that "--" it invalidated the rest of the statement (over half of the total code). As soon as I recognized this, I eliminated the comment and code and it ran without a problem.
So, the moral of the story is: Don't use the "--" style of code in any query you will run in SSRS!
Formatting of SQL code is not preserved in the running code. I had heard that this was a problem in other areas before, so I knew it in the back of my mind, and it was that which allowed me to recognize what was going on. I have heard other SQL authorities rail on the use of "--" in code before, advocating that you ALWAYS should us the "/* . . . */" style. I specifically remember it being mentioned as a problm in SSIS, but I had never run into this problem in SSRS before.
So, I resolve from this day forth to join the /* camp and only use it for my comments.
Always use "/* . . . */" for your comments. It never hurts, and will sometimes save you time and pain.
That is all.
