Red Gate forums :: View topic - Does v9 support both "ALTER" & "If exists .. DROP + CREATE"?
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare Previous Versions
SQL Compare Previous Versions forum

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

Search in SQL Compare Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Go to page 1, 2, 3  Next
Author Message
fred



Joined: 30 Mar 2011
Posts: 4

PostPosted: Wed Mar 30, 2011 12:27 pm    Post subject: Does v9 support both "ALTER" & "If exists .. DROP + CREATE"? Reply with quote

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?
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1124
Location: Twitter: @dtabase

PostPosted: Wed Mar 30, 2011 2:43 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
fred



Joined: 30 Mar 2011
Posts: 4

PostPosted: Thu Mar 31, 2011 12:29 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1124
Location: Twitter: @dtabase

PostPosted: Thu Mar 31, 2011 12:57 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
fred



Joined: 30 Mar 2011
Posts: 4

PostPosted: Thu Mar 31, 2011 1:41 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
davec



Joined: 05 May 2011
Posts: 9

PostPosted: Thu May 05, 2011 5:47 am    Post subject: The need for If Exists...Drop Reply with quote

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
Back to top
View user's profile Send private message
davec



Joined: 05 May 2011
Posts: 9

PostPosted: Thu May 05, 2011 6:55 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1124
Location: Twitter: @dtabase

PostPosted: Thu May 05, 2011 7:39 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
davec



Joined: 05 May 2011
Posts: 9

PostPosted: Thu May 05, 2011 8:04 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
fred



Joined: 30 Mar 2011
Posts: 4

PostPosted: Thu May 05, 2011 8:52 am    Post subject: Reply with quote

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

Maybe next time.
Fred
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1124
Location: Twitter: @dtabase

PostPosted: Thu May 05, 2011 10:05 am    Post subject: Re: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
davec



Joined: 05 May 2011
Posts: 9

PostPosted: Tue May 10, 2011 3:06 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1124
Location: Twitter: @dtabase

PostPosted: Wed May 11, 2011 2:21 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
ISRAEL



Joined: 23 May 2011
Posts: 3

PostPosted: Mon May 23, 2011 5:13 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1124
Location: Twitter: @dtabase

PostPosted: Mon May 23, 2011 5:15 pm    Post subject: Re: Reply with quote

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?
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Go to page 1, 2, 3  Next
Page 1 of 3

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group