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 Previous  1, 2, 3  Next
Author Message
ISRAEL



Joined: 23 May 2011
Posts: 3

PostPosted: Mon May 23, 2011 7:00 pm    Post subject: Reply with quote

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



Joined: 05 May 2011
Posts: 9

PostPosted: Tue May 24, 2011 12:00 am    Post subject: Reply with quote

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



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

PostPosted: Tue May 24, 2011 12:38 am    Post subject: Reply with quote

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



Joined: 06 Feb 2009
Posts: 13

PostPosted: Tue May 31, 2011 9:36 am    Post subject: Reply with quote

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



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

PostPosted: Tue May 31, 2011 9:45 am    Post subject: Reply with quote

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



Joined: 28 Jun 2011
Posts: 1

PostPosted: Tue Jun 28, 2011 4:46 pm    Post subject: Reply with quote

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



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

PostPosted: Tue Jun 28, 2011 4:49 pm    Post subject: Reply with quote

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



Joined: 11 Nov 2009
Posts: 17

PostPosted: Tue Jul 19, 2011 12:48 am    Post subject: Reply with quote

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



Joined: 19 Jul 2011
Posts: 1
Location: WI

PostPosted: Tue Jul 19, 2011 12:52 am    Post subject: Reply with quote

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



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

PostPosted: Tue Jul 19, 2011 8:38 am    Post subject: Reply with quote

Is there any reason why you can 'recompare' with SQL Compare and generate a new script, rather than relying on the previously generated one?
Back to top
View user's profile Send private message Send e-mail
JohnnyT



Joined: 11 Nov 2009
Posts: 17

PostPosted: Tue Jul 19, 2011 2:54 pm    Post subject: Re: Reply with quote

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



Joined: 11 Nov 2009
Posts: 17

PostPosted: Thu Sep 01, 2011 10:30 pm    Post subject: Reply with quote

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



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

PostPosted: Thu Sep 01, 2011 10:41 pm    Post subject: Reply with quote

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



Joined: 11 Nov 2009
Posts: 17

PostPosted: Thu Sep 01, 2011 10:55 pm    Post subject: Reply with quote

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

Code:
CREATE PROCEDURE MyStoredProc

or
Code:
ALTER PROCEDURE MyStoredProc


to this...

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



Joined: 28 Jun 2011
Posts: 25

PostPosted: Fri Sep 02, 2011 8:40 am    Post subject: Reply with quote

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