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

fred

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?

David Atkinson

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

fred

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

David Atkinson

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

fred

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

davec

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

davec

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 Atkinson

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

davec

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

fred

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

Maybe next time.
Fred

David Atkinson

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

davec

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.

David Atkinson

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

ISRAEL

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,

David Atkinson

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?

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.

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

David Atkinson wrote:

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.

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

CREATE PROCEDURE MyStoredProc

or

ALTER PROCEDURE MyStoredProc

to this...

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE ROUTINE_NAME = 'MyStoredProc' AND ROUTINE_TYPE = 'PROCEDURE') ;
   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.

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.

vehro

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