How to make script to check if exist instead of alter state.

tatvasoft53 · November 14, 2009 1:51AM

Hi All.

when we compare 2 database and redgate generate syncronize script. Now i want this script to run as many times. means the script must check for the object exist OR not instead of directly alter statement.

By default redgate generate script which run only 1 time. when i try to run script second time then it will display error because of again create statement without drop the procedure.

can anyone please tell me is it possible to generate sql script that check the procedure, function,... exist and if it exist then drop it and recreate it?

Thanks in advance.

Brian Donahue · November 16, 2009 5:16AM

Hello,

SQL Compare is designed to syncrhonize only the two schemas being compared, so it will alter wherever possible unless an object needs to be recreated for technical reasons. There is no option that will cause an "IF EXISTS.." to be scripted, but this feature has been requested pretty frequently and will probably appear in a future version (it's tentatively scheduled for 8.2).

tatvasoft53 · November 16, 2009 5:36AM

Hi,

Thanks for reply!!!

I am waiting for the next version. can i know when next version will be available?

Thanks
Mayur

Brian Donahue · November 16, 2009 6:32AM

Hi,

SQL Compare 8.2 is not currently on the release schedule, so it could be a few months away.

David Atkinson · November 16, 2009 4:34PM

tatvasoft53 wrote:

Hi All.

when we compare 2 database and redgate generate syncronize script. Now i want this script to run as many times. means the script must check for the object exist OR not instead of directly alter statement.

By default redgate generate script which run only 1 time. when i try to run script second time then it will display error because of again create statement without drop the procedure.

can anyone please tell me is it possible to generate sql script that check the procedure, function,... exist and if it exist then drop it and recreate it?

Thanks in advance.

Can I ask why you want to dro and recreate it if it exists?

David Atkinson
Product Manager
Red Gate Software

tatvasoft53 · November 20, 2009 6:47AM

Hello Sir,

we are working on very large application.
Now we had so many client those are using our system. Now suppose we had developed a new module or changes in exising module then we need to upgrade system at all client. for that we create change script by comparing old database(last version database) with our working database and just run that script at client side so new changes will be automatically installed.

Thanks,
Mayur

David Atkinson · November 20, 2009 8:49AM

If you generate a migration script from database A to B, it will have CREATE statements for new objects and ALTER statements for existing objects. If all your clients are using databases with the same schema, I can't see why SQL Compare's scripts shouldn't run successfully without having to drop and recreate objects. Or do I misunderstand your requirement?

David Atkinson
Product Manager
Red Gate Software

tatvasoft53 · November 21, 2009 2:13AM

Hello sir,

Tanks for your quick reply!!

I can generate migration script from 1 database to another. but my problem is that i can't install sql comarer at all our clients.

Migration script run successfully for the first time. but when i run script second time then i am getting some errors because in that script create statement will create proc. at first time when i run script but and when i run that script second time then is it already exist so i get error.

That's why i want single script for all changes that i can run multipe times on all my client computer.

I have no idea which changes are required at particular client so i want to run a single latest script at all my clients.

My client for whose i am working has already purchased license version of redgate. but it is not usefull at this stage.

Thanks,
Mayur P

David Atkinson · November 21, 2009 6:40AM

Did you know that your clients can create and email you with a schema snapshot using a free tool, SQL Snapper? This can be downloaded from

http://labs.red-gate.com/index.php/Main_Page

With a schema snapshot you can create an upgrade script that is specific to the customer.

Is this something that will solve your problem?

David Atkinson
Red Gate Software

tatvasoft53 · November 25, 2009 5:01AM

Hello Sir,

Thanks for reply.

The solution that you gave me is working.

Can you please let me know can i generate database from .snp fie ?

David Atkinson · November 26, 2009 6:29AM

If you want to recreate the schema as a database, simply create an empty database:

CREATE DATABASE newdb

then use SQL Compare, putting the snapshot as the source and newdb as the target. Obviously you don't have any data. You can save your data to script files using SQL Data Compare pro and use the same mechanism to synch this with your database, although we'd only recommend you do this for lookup tables.

Hope this helps,

David

junctionEOD · June 4, 2010 5:46PM

Hi,

I am also trying to create re-runable scripts for our client.

It does not appear that the feature made it into the 8.2 release of the software (it was suggested in a previous post that it might).

Is it there and I'm just missing it? Or has it been pushed back to another release (if so, are there any plans on when this might come out)?

Thanks for your help.

David Atkinson · June 6, 2010 2:34AM

It didn't make 8.2. In retrospect this would have been a little unusual, as point releases are generally reserved for bug fixes, as indeed this one was. The most likely scenario is that it will be in 9.0, which is scheduled tentatively for Q4.

It must be noted that adding IF EXISTS statements around each action isn't the ideal way of working, and cannot be expected to work in all cases. Is there any reason why you can't generate the script from the client's schema?

David

How to make script to check if exist instead of alter state.

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk