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

Forum for users of SQL Compare schema synchronization utility

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

Postby fred » Wed Mar 30, 2011 12:27 pm

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?
fred
 
Posts: 4
Joined: Wed Mar 30, 2011 12:14 pm

Postby David Atkinson » Wed Mar 30, 2011 2:43 pm

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
 
Posts: 1136
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby fred » Thu Mar 31, 2011 12:29 pm

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
fred
 
Posts: 4
Joined: Wed Mar 30, 2011 12:14 pm

Postby David Atkinson » Thu Mar 31, 2011 12:57 pm

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
 
Posts: 1136
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby fred » Thu Mar 31, 2011 1:41 pm

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
fred
 
Posts: 4
Joined: Wed Mar 30, 2011 12:14 pm

The need for If Exists...Drop

Postby davec » Thu May 05, 2011 5:47 am

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
 
Posts: 9
Joined: Thu May 05, 2011 5:34 am

Postby davec » Thu May 05, 2011 6:55 am

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
davec
 
Posts: 9
Joined: Thu May 05, 2011 5:34 am

Postby David Atkinson » Thu May 05, 2011 7:39 am

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
 
Posts: 1136
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby davec » Thu May 05, 2011 8:04 am

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
davec
 
Posts: 9
Joined: Thu May 05, 2011 5:34 am

Postby fred » Thu May 05, 2011 8:52 am

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

Maybe next time.
Fred
fred
 
Posts: 4
Joined: Wed Mar 30, 2011 12:14 pm

Re:

Postby David Atkinson » Thu May 05, 2011 10:05 am

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
 
Posts: 1136
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby davec » Tue May 10, 2011 3:06 am

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.
davec
 
Posts: 9
Joined: Thu May 05, 2011 5:34 am

Postby David Atkinson » Wed May 11, 2011 2:21 pm

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
 
Posts: 1136
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby ISRAEL » Mon May 23, 2011 5:13 pm

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
 
Posts: 3
Joined: Mon May 23, 2011 5:05 pm

Re:

Postby David Atkinson » Mon May 23, 2011 5:15 pm

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
 
Posts: 1136
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Next

Return to SQL Compare Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests