Handling of COMMENTS in the GDR

In the GDR you will mainly find large improvements, but it is not everything is better. One area where we regressed functionality is the deployment of comments inside T-SQL procedures, triggers,  functions etc. With the move to a true and only model based system, objects that are stored in dbo.syscomments or sys.sql_modules, are modeled in such a way that we separate out body script from the object name and parameters. We do this for a reason, so we when you rename an object we also update the actual name inside the script body. The nasty side effect is that all comment before the first real statement in the script body and after the last real statement in the script body as lost when deployed.

For example see the following stored procedure which has a comment line on every possible line.

    1:  -- comment line 1

    2:  CREATE PROCEDURE [dbo].[p_Comments] -- comment line 2

    3:  -- comment line 3

    4:       @param1 int = 0, -- comment line 4

    5:       @param2 int      -- comment line 5

    6:  -- comment line 6

    7:  AS -- comment line 7

    8:  -- comment line 8

    9:  BEGIN -- comment line 9

   10:      SET NOCOUNT ON -- comment line 10

   11:      -- comment line 11

   12:      SELECT @param1, @param2 -- comment line 12

   13:      -- comment line 13

   14:      RETURN 0 -- comment line 14

   15:  END -- comment line 15

   16:  -- comment line 16

   17:  GO -- comment line 17

   18:  -- comment line 18

   19:   

If you would inspect how this information is stored inside the model, by looking at the .DBSCHEMA file which is produced by the build step, it looks somewhat like this:

    1:  <Element Type="ISql100Procedure" Name="[dbo].[p_Comments]">

    2:       <Property Name="IsAnsiNullsOn" Value="True" />

    3:            <Property Name="BodyScript">

    4:                 <Value><![CDATA[BEGIN -- comment line 9

    5:      SET NOCOUNT ON -- comment line 10

    6:      -- comment line 11

    7:      SELECT @param1, @param2 -- comment line 12

    8:      -- comment line 13

    9:      RETURN 0 -- comment line 14

   10:  END]]></Value>

   11:            </Property>

   12:            ...

   13:  </Element>

This fragment indicates that the script body starts at the first statement, in this case the BEGIN statement and ends at the END statements. Comments between those to statements are preserved when deploying, the others, comments line 1 through 8 and 15 through 18 did not make it over. Which become very apparent when you look at the deployment script.

    1:  PRINT N'Creating dbo.p_Comments...';

    2:  GO

    3:   

    4:  CREATE PROCEDURE [dbo].[p_Comments]

    5:  @param1 INT=0, @param2 INT

    6:  AS

    7:  BEGIN -- comment line 9

    8:      SET NOCOUNT ON -- comment line 10

    9:      -- comment line 11

   10:      SELECT @param1, @param2 -- comment line 12

   11:      -- comment line 13

   12:      RETURN 0 -- comment line 14

   13:  END

   14:  GO

The comments inside the source code are preserved, but they simply do not make it to the target.

The team is currently working on finding the appropriate resolution for this issue.

-GertD

Comments

• Anonymous
  February 01, 2009
  Another problem is this when writting comments or utilizing the comment buttons in SQL 2005. We traditional create formatted coments: search engine reference: sql 2008 comment butons function differently than the sql 2005 comment buttons in sql 2005 are different

• Anonymous
  February 01, 2009
  The comment has been removed

• Anonymous
  February 23, 2009
  hi, having the comments after the BEGIN for objects is fine, but it doesn't work for views, as there is no BEGIN END. thx Jag

• Anonymous
  July 20, 2009
  How do we handle commenting in views, other then putting the comments inside the select statement like create view xview as select /Comment placement is stupid now in 2008/ column1 from table