CREATE vs ALTER

Aug 20, 2010 at 2:25 PM

Hi,

 

I am using dbdiff software. Apparently, it generate a create script for functions/procedures existing on both databases.

It does not use the ALTER statement.

 

 

Sincerely

Gil France

Coordinator
Sep 3, 2010 at 2:23 AM
Edited Sep 6, 2010 at 4:55 AM

Hi! I recently joined the project as a developer. I apologize for the delay in responding to your e-mail. I've not had a chance to verify that the notification settings are working correctly.

Thanks for reporting that the default behavior is to use a CREATE even when functions and procedures already exist on both databases. I will try to verify whether or not this is standard behavior for the application or is a problem due to some unexpected database configuration issue. For example, right now there is an open issue indicating that the program has problems dealing with database objects that have been renamed. I do know that things like permissions can be lost if objects are dropped and created rather than altered, but also it is much easier to simply recreate an object that has changed rather than to generate the SQL needed to generate a small change.

Thank you for not only taking the time to try out the project but also to share your concerns regarding the implementation!

Edit: Found a work item and discussion reporting what may be this exact issue; it sounds like a problem others have even taken a stab at fixing... I am currently working on a new release for 64-bit issues and this might be small enough to include.

#7716 Invalid "FindCreate" Logic (problems when stored procedures are preceded by comments)

#11874 DBDiff.Schema.SQLServer.Generates.Model.Util.FormatCode.FindCreate issue reported by DBSourceTools developer

CodePlex user rozentalsn reports: I found what I believe is a bug...

#9485 Missing Alter and /or drop statements when comparing MSSqlServer 2008 stored procedures on version 0.8.5

Nov 3, 2010 at 1:38 PM

Please try to squeeze  this into the next release... Otherwise your product is awesome!

/Martin

Feb 17, 2011 at 12:47 PM

hi,

I too face the same problem. for Stored procedures in some cases it scripts create procedure even if the procedure is existing. Work-around is I have to write drop statement for each procedure before the the create statement. I have created small utility to do that. But if it is incorporated in the application then it will be better.

One of the cause for this behaviour is I guess the comments before procedure declaration and before parameter declaration, if the procedure  is created using New Procedure wizard template. or if we put the comment after procedure name.

 

I hope you can find the solution with the above hint.

Shailesh