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 differentAnonymous
February 01, 2009
The comment has been removedAnonymous
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 JagAnonymous
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