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

[ISRAEL]

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

[davec]

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

[David Atkinson]

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

[AndyIrving]

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!)

[David Atkinson]

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

[kmart]

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

[David Atkinson]

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

[JohnnyT]

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!

[Naomi]

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.

[David Atkinson]

Is there any reason why you can 'recompare' with SQL Compare and generate a new script, rather than relying on the previously generated one?

[JohnnyT]

[JohnnyT]

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!

[David Atkinson]

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

[JohnnyT]

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...

[aolcot]

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.