July CTP: Some comments are stripped from stored procedures, functions

Question

I have a copy of my baseline database created using DBPro 2005.  I then created a database project for that database using DBPro 2008 July CTP.  After deploying the 2008 database project to my test SQL 2000 server, I compared the resulting schema to my baseline schema using Red Gate's SQL Compare tool.  This is how I found several differences in my stored procedures.

 

1) DBPro 2008 strips some of the comments from the stored proc definitions.  Comments prior to the "CREATE PROCEDURE" command are stripped.  Comments prior to the "AS" clause are also removed.

 

2)  2008 makes other changes to stored procedure and function definitions.  "CREATE PROC" is changed to "CREATE PROCEDURE".  Also, the spacing and case of function and stored proc arguments are changed.  For example:

 

Code Snippet

CREATE FUNCTION dbo.vfnParseDelimitedListToTable
(
    @strDelimitedList    varchar(1000) = null,
    @strDelimiter        char(1)       = null
)

 

 

is changed to:

 

Code Snippet

CREATE FUNCTION [dbo].[vfnParseDelimitedListToTable]
(@strDelimitedList VARCHAR (1000)=null, @strDelimiter CHAR (1)=null)

 

 

3) In a table definition, the "NOT FOR REPLICATION" clause of an identity column is not retained.

 

Modification of the text associated with a stored procedure, function, trigger, etc. is problematic if you want to use a text comparison tool to compare the contents of SQL scripts.  The object definitions will appear different even though it's only a spacing or formatting change.  Or "CREATE PROC" instead of "CREATE PROCEDURE".  It's not clear to me why it would ever be a good idea to modify the definitions of these objects.

 

Answer 1

Hello,

 

I'm looking at your issues, and I would like to gather a little more information. So if I understand correctly, you originally had a database project from DBPro 2005. Which I assume was a Sql 2000 Database project. Then you created a database project in our July CTP, which I also assume was a Sql 2000 project. How did you populate that project? Was it through Importing the database schema from a live database, or adding all the scripts manually. If you added them manually did you paste the text into scripts, add the files from you old database project, or did you use the script import?

 

Thank you for taking the time to report these issues, as they help us improve product quality.

 

Christian.

Answer 2

Yes, you are correct.  I have a  SQL 2000 database project created in DBPro 2005.  To create the SQL 2000 database project under DBPro 2008, I copied the existing database project created in DBPro 2005 to a new folder and opened it using DBPro 2008.  This converted the project to the 2008 DBPro format.

 

When I look at the individual source files in my 2008 DBPro project, they are identical to the 2005 DBPro files.  The contents of the source stored procedures, functions, triggers, etc. were not modified by the conversion to DBPro 2008.  But, when a deploy is performed on the database project, the objects that are written to the ".sql" script are modified.  For example, the source code in file "dbo.vspProcessMessageForIMC.proc.sql" appears as follows:

 

Code Snippet

CREATE PROCEDURE dbo.vspProcessMessageForIMC (

@msgDateTime datetime,

@msgType varchar(10),

@msgEventCode varchar(10),

@msgControlID varchar(20),

@msgSeqNumber numeric(15),

@msgErrorCodeID char(32),

@msgData text,

@eMessageID char(32),

@GUPIList varchar(8000),

@healthSystemIDList varchar(8000),

@facilityIDList varchar(8000),

@patientIDInternalList varchar(8000),

@patientCacheIDOUT char(32) OUTPUT,

@errorMessageOUT varchar(255) OUTPUT,

@messageIDOUT char(32) OUTPUT

)

-- with encryption

AS

.....

.....

.....

 

But, when you examine the ".sql" script resulting from the deploy, the stored proc is rendered as:

 

Code Snippet

CREATE PROCEDURE [dbo].[vspProcessMessageForIMC]
@msgDateTime DATETIME, @msgType VARCHAR (10), @msgEventCode VARCHAR (10), @msgControlID VARCHAR (20), @msgSeqNumber NUMERIC (15), @msgErrorCodeID CHAR (32), @msgData TEXT, @eMessageID CHAR (32), @GUPIList VARCHAR (8000), @healthSystemIDList VARCHAR (8000), @facilityIDList VARCHAR (8000), @patientIDInternalList VARCHAR (8000), @patientCacheIDOUT CHAR (32) OUTPUT, @errorMessageOUT VARCHAR (255) OUTPUT, @messageIDOUT CHAR (32) OUTPUT
AS

.....

.....

.....

 

All of the stored proc's parameters are stuffed onto a single line.  Not only is the readability poor, if you compare the definition of the modified proc to the original using most comparison tools it will now show as being "different".

Answer 3

 

For the thrid issue that you repored, the table with the Not for replication. Could I see the before and after on that one.

 

Christian.

Answer 4

Sure.  Here's the code contained in "dbo.testRepl.table.sql":

 

Code Snippet

CREATE TABLE [dbo].[testRepl]

(

column_1 int NOT NULL IDENTITY(1,1) NOT FOR REPLICATION,

column_2 int NULL

)

 

 

Here's the code output to the ".sql" deployment script after running a Deploy of the database:

 

Code Snippet

GO
PRINT N'Creating dbo.testRepl';

GO
CREATE TABLE [dbo].[testRepl] (
    [column_1] INT IDENTITY (1, 1) NOT NULL,
    [column_2] INT NULL
);

 

 

Answer 5

The problem with the IDENTITY loosing its Not For Replication has been fixed, and that fix will show up in the final version.

 

The formatting problems are a little harder to deal with. Due to our new model based implementation, where we have a meta model in the background as opposed to the design database, we need to do script generation when we deploy. It allows us the flexibility to do things that we could not do in V1, but unfortunatly it comes at the price of some formatting. We are looking at more extensive formatting options for the future, but currently have no concrete plans.

 

I would be pleased to hear more input from you on the subject.

 

Christian.

Answer 6

Thanks for the info regarding the IDENTITY fix.

 

I'm still not clear on why you would modify a stored proc definition when deploying.  I can understand that the stored proc arguments are parsed to create your model but why do you have to re-assemble and re-format them when writing the proc to the ".sql" script?  You have access to the entire proc definition - why not output it unchanged while using the model representation for your internal processing?  The re-formatting of the stored-proc arguments is less readable not to mention the loss of comments.

 

Will the next release contain a fix to retain comments located prior to the "CREATE PROCEDURE" command and surrounding the "AS" clause?

Answer 7

Are there any new plans put in place to preserve procedure formatting in the GDR?  We have coding standards across the enterprise and using DBPro to make the changes is breaking the standards.  Obviously becoming an issue for us.

Please let us know of any updates on this issue.

Answer 8

Have their been any resolutions to this with GDR R2?  Possibly a command line parameter or deployment setting?
For companies doing software releases having copyrights or production notes stripped out seems like a major bug, not a formatting issue (IMHO).

Hopefully there is an easy resolution that I just haven't come across yet..

Thanks
Robert

---

Robert

Answer 9

This is a major issue for our company also. The removal of comments and reformatting of input parameters makes virtually impossible to provide update scripts to our customers. It also creates issues with comparing sql code in the  database with SQL code stored in TFS. Until we get an option to prevent the refactoring during deploy, we will not upgrade to the GDR version.

Answer 10

Unfortunately we were not able to fix this in GDR R2.  We do plan on fixing the issues around preservation of comments and custom formatting of 'syscomments' objects in VS 2010. 

---

Tom Smith, SDET - Microsoft Visual Studio Team Edition for Database Professionals

Answer 11

But what about the parameters? This is a huge deal.. comments I could live without

Answer 12

Any update on issue?