Does v9 support both "ALTER" & "If exists .. DROP + CREATE"?

fredfred Posts: 5
edited October 27, 2011 11:53AM in SQL Compare Previous Versions
Can the user choose (for at least Stored Procs & Functions) to choose an update script that either has ALTER or IF EXISTS DROP/CREATE for objects with differences - as has been promised so many times in the past?
«1

Comments

  • Apologies for this. We've hoped to find the resource to add this, but it's hard juggling competing priorities. For every feature we add, there are a dozen others we have to leave out.

    It would be helpful if we could better understand your motivation for doing this. What problem would this solve for you?

    David Atkinson
    Product Manager
    Red Gate Software
    David Atkinson
    Product Manager
    Redgate Software
  • Hi David

    It seems like a pretty basic feature. We are software developers & need to ship incrementatl db updates to our clients. We manage table structure changes tightly and only occasionally use generated code to effect changes - however for SPs & UDFs we want to ship to the latest version consistent with the table structure; and we want this update process to be solid - i.e. we want to drop if exists prior to create.

    So we would only use on SPs/UDFs & without it - no matter how nice your product looks - we can't consider it (which is a shame since you have some nice features - e.g. compare db vs .bak).

    Regards.
    Fred
  • Thanks for the additional information. All of this helps add weight to the feature request.

    Can I ask you what you mean by 'solid'? How do you get into a situation where an ALTER would fail and a IF EXISTS DROP/CREATE would succeed?

    We're currently working on an improved way to manage differing schema versions, and how to migrate between them in a more controlled way. If you're interested in improvements in this area, please help us by filling in the following survey:

    http://www.surveymk.com/s/migrations

    David
    David Atkinson
    Product Manager
    Redgate Software
  • Hi David

    There are many reasons why our Product database SPs/UDFs may differ from an individual client's SPs/UDFs - even when all at the same product version. For example custom reports often require a custom SP to drive the report content - or a new Excel import template would require a new SP to normalise the data prior to update etc ....

    This means that when we issue an interim product update to any given client we know the main table structure (we control this separately) but not necessarily which specific SPs/UDFs. So - for us - ALTER would not be guaranteed to work - whereas IF EXISTS ... DROP before CREATE does (i.e. the latter approach is solid).

    Regards.
    Fred
  • I concur with fred.

    I have a requirement to be able to re-run scripts without it failing.
    The check for existence before the drop would solve this for me.

    I have to provide deployment scripts to a large number of databases that are outside my control and require more fault tolerant scripts that allow for this sort of minor anomalies.

    Cheers,

    Dave
  • It seems that ApexSQL get it.
    It's a feature in their product and may sway my organisation that way, which would be a pity because I've liked the Redgate toolbelt for a while now.

    Cheers,

    Dave
  • David AtkinsonDavid Atkinson Posts: 1,431 Rose Gold 2
    If there was a blanket existence check on each object (alter, creates and drops), would this be enough?

    If the script tries to alter a table, and it doesn't exist on the target, what would you expect to happen? Should it just ignore this fact and carry on? Would this count as 'not failing'?

    If you describe to me the differences between your target databases that you need it to account for, it will help a lot.

    David
    David Atkinson
    Product Manager
    Redgate Software
  • The problem is only fixed by an existance check if you're doing a drop and create with the check being for the drop.

    I'm currently working on building a data dictionary into the schema using extended properties. So that is my immediate need.

    So I guess from your application point of view, the option only applies if you turn of "Modify" and do drop, creates.

    Cheers,

    Dave
  • As per davec's earlier point ... we needed to make a decision and went with ApexSQL.

    Maybe next time.
    Fred
  • David AtkinsonDavid Atkinson Posts: 1,431 Rose Gold 2
    davec wrote:
    The problem is only fixed by an existance check if you're doing a drop and create with the check being for the drop.

    I'm currently working on building a data dictionary into the schema using extended properties. So that is my immediate need.

    So I guess from your application point of view, the option only applies if you turn of "Modify" and do drop, creates.

    So if this 'rerunnable scripts' option is enabled, would it force a rebuild of all tables that need to be altered along with the existence check? Would the potential performance impact be acceptable to you?

    David
    David Atkinson
    Product Manager
    Redgate Software
  • I don't think that it could be used without issues on table modification, but on extended properties, stored procs, functions, etc, you could have an option to do the drop if exists, create without too much drama.

    It's not going to solve everything, but would go a long way in eliminating the amount of manual scripting required to make a script re-runable.
    And that's why we buy these tools.

    There are many reasons why databases these scripts are run against may not be identical in nature (selective release of patches, hot fixes, etc), and these scripts need to cater for that in an elegant way.
  • It seems that from what you're saying, it's the 'textual' objects that matter most. These should drop and re-create without too much dependency enforcement from SQL Server, so I can see how this might work.

    Thanks for the clarification.

    David
    David Atkinson
    Product Manager
    Redgate Software
  • We have the same situation at our company. Sometimes we have to deploy some objects to some customers before the official distribution release. So, if we use the sync script will cause an error. However, if we can have an option, likes Fred said (“if exist… DROP + CREATE”), we can use the same sync script for all of our customers, no matter what they already have the new objects or not.

    A feature like that will improve the efficiency of our updates distribution.

    Thanks,
    Israel
  • ISRAEL wrote:
    We have the same situation at our company. Sometimes we have to deploy some objects to some customers before the official distribution release. So, if we use the sync script will cause an error. However, if we can have an option, likes Fred said (“if exist… DROP + CREATE”), we can use the same sync script for all of our customers, no matter what they already have the new objects or not.

    A feature like that will improve the efficiency of our updates distribution.

    Thanks,

    Thanks for the request. Could you specify exactly which object types this behavior should work with?
    David Atkinson
    Product Manager
    Redgate Software
  • Hi David,


    In our case, is more likely to happen with the SP's. About 95% of the times when we need to install modifications before the official upgrade are SP’s. The other 5% splits between tables, views and functions.

    I hope that help.
    Israel
  • It may be simpler to work out what objects it wouldn't work with. Table modifications would be one.

    I'm getting to the point where I need to get some tools for my team. Can you please tell me if this feature will be in the next release or not?

    If not, then I'll go with ApexSQL as we intend to fill in the data dictionary within the schema and I don't feel like wrapping if exists... around all the extended property drops .

    Cheers,

    Dave
  • This is slated for the next release (v9.5). However, we can never guarantee that it won't slip out of the backlog as priorities and resourcing constraints aren't always as predictable as we'd like.

    Sorry that I can't be any more specific.

    Regards,

    David
    David Atkinson
    Product Manager
    Redgate Software
  • I'd like to add my voice to requesting this. there's 2 scenarios where i think this would be beneficial, one is for sure a feature request, but the other is arguably a bug:

    Create a scalar UDF, perform a sync. then, change the scalar UDF to an inline UDF (keeping the same name). sql compare attempts to alter it, but this doesn't work:

    Cannot perform alter on 'dbo.testes' because it is an incompatible object type.

    Here, doing an if exists... drop... create would solve the problem (and yes i know it's the most common scenario!)
  • The way we hope to implement this is by doing an IF NOT EXISTS CREATE PROC as select 1, and then ALTER PROC <real proc contents>.

    This avoids the case of unnecessarily dropping an object along with its permissions, which could be undesirable.

    This, sadly, wouldn't fix your issue. Have you emailed this problem to support@red-gate.com so it can be addressed via a fix?

    David
    David Atkinson
    Product Manager
    Redgate Software
  • I'd like to chime-in and also request support for the IF EXISTS feature.
    There are 2 main reasons to support this feature:

    1. Occasionnaly, some databases receive "private" hot fixes that would create new tables/Sps/Indexes etc.. When the official service pack is published, it fails if any private hot fixes have been previousely applied. This is a major limitation for using red Gate for our purposes and a major source of frustration.

    2. The ALTER statement doesn't modify the system creation date for the affected object. Databases are often audited for SOX (and other) compliance purposes and all changes to the databases have to be documented. It it much easier to document such changes when the creation date can be crearly visible at the database level. To take this statement one step further, I'd like to see the option to use DROP/CREATE instead of ALTER for the entire synchronization script.

    Thanks,
    -Martin
  • Thanks for the feedback. How would you expect the permissions not to be dropped if you're dropping the object?

    The advantage of ALTER is that these are maintained.

    David
    David Atkinson
    Product Manager
    Redgate Software
  • I'd just like to add my enthusiastic +1 for this enhancement as well. Of course, we would need/use it only for textual objects (functions, sprocs, and sometimes descriptions). For our case, we sometimes need to provide new sprocs or functions to clients on older versions as a service pack fix. But when they later upgrade to the latest version of our database, we don't want the update scripts to fail because the objects already exist.

    As far as permissions on dropped objects go, that is not an issue for us since our clients run a "post update" which rebuilds permissions on all objects anyway.

    I really hope this enhancement can make it into 9.5 as you mentioned. Thanks!
  • I work with JohnyT and want to add my support to this feature as well.

    Say, we recently had to implement several SPs as an urgent fix somewhere several versions back. Now we will need to fix some latest scripts.

    Having either CREATE proc and then ALTER or checks with IF EXISTS DROP
    will help us a lot.
  • Is there any reason why you can 'recompare' with SQL Compare and generate a new script, rather than relying on the previously generated one?
    David Atkinson
    Product Manager
    Redgate Software
  • Is there any reason why you can 'recompare' with SQL Compare and generate a new script, rather than relying on the previously generated one?
    We can, but it becomes quite tedious when you have over 200 clients and maybe only a dozen or so may have needed and applied service packs for updated sprocs. We post our SQL Compare version update scripts on our web site for clients to download and apply. These clients would now have deviated from the regular update path and managing them separately with special "recompares" just because they have some different code becomes a pain for us and them to manage.
  • Hi David,
    Just curious if this has been scheduled for 9.5? We really need this feature. If it's just for server side code (stored procedures, functions), that's totally fine. Thanks!
  • Yes, it's still hanging on there in the backlog for 9.5. I've asked the project manager to get in touch to find out whether the way we hope to solve this matches your expectations.

    David
    David Atkinson
    Product Manager
    Redgate Software
  • Thanks very much for the update, I appreciate it. Basically, I'd love a configuration setting which would change the header for new or altered stored procedures (and functions) from something like...
    CREATE PROCEDURE MyStoredProc
    
    or
    ALTER PROCEDURE MyStoredProc
    

    to this...
    IF EXISTS &#40;SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
       WHERE ROUTINE_NAME = 'MyStoredProc' AND ROUTINE_TYPE = 'PROCEDURE'&#41; ;
       DROP PROCEDURE MyStoredProc ;
    GO
    CREATE PROCEDURE MyStoredProc
    

    Of course there may be additional permissions implications concerning this, but for me personally, I don't care. All permissions get rebuilt later on in a post-update section we append to the generated sql compare scripts. Thanks.
  • aolcotaolcot Posts: 27 Bronze 2
    I'd also like to add my support for a feature exactly as described in this thread and really hope it makes it in for 9.5.
  • Get MS to change how it handles the issue to begin with. Support for "create or replace" (instead of separate create x and alter x) would not only address your issue directly, but would also solve the issue of the dropped permissions that occur for

    if exists('procedure x') drop procedure x
    go
    create procedure x...
Sign In or Register to comment.