DropAndCreateInsteadOfAlter (Alias: dacia) / Source Control

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

DropAndCreateInsteadOfAlter (Alias: dacia) / Source Control

Postby brad_schoen » Tue Apr 02, 2013 6:38 pm

I cannot seem to get this option to work from SQL Compare command line.

I am trying to generate a script that can be ran each time the build is executed by comparing the baseline to the latest state of source control and generating a change script. For this to work each object would need an if exists/drop and create- I am only interested in doing this for stored procedures, user defined functions, and views. It seems like this is the intent of DropAndCreateInsteadOfAlter. I also have ObjectExistenceChecks specified. However, the tool still scripts this as simple ALTER and CREATE's.

Is this supported? I also tried another more manual approach from the MSSQL Studio Source Control plugin setup tab, by altering the configuration file to include the exact same options, but also I did not see that reflected in migration scripts when going that route.

Here is the command I am using when executing SQL Compare from command line for the setup described:

"C:\\Program Files (x86)\\Red Gate\\SQL Compare 10 SQLCompare.exe" /sourcecontrol1 /revision1:Latest /scriptsfolderxml:"C:
\\docs_mects\\branches\\TSv3.60 Database\\Scripts\\compare.xml" /migrationfolderxml:"C:\\docs_mects\\branches\\TSv3.60\\Database\\Scripts \\migration.xml" /scripts2:"C:\\docs_mects\\branches\\TSv3.60\\Database\\SourceSeed" /options:DropAndCreateInsteadOfAlter,if,ifg,iw,ie,iq,nt,iu,iup,ip,nc,oec /scriptfile:"C:\\docs_mects\\branches\\TSv3.60\\Database
\\Scripts\\3.60.001.sql" /include:Function,StoredProcedure,View

Thanks for looking.
brad_schoen
 
Posts: 2
Joined: Tue Apr 02, 2013 6:24 pm

Postby Chris Auckland » Fri Apr 05, 2013 10:17 am

Thanks for your post.

The option DropAndCreateInsteadOfAlter will script drop/create statements in the script, but not for any changes covered by the migration script.

If you use a migration script, it is going to use that exact script to go between the revisions covered by the migration script, regardless of any options you set. A migration script is effectively overwriting any default behavior that would normally occur.

Do you know if this particular deployment is using a migration script? An easy way to tell would be by repeating the comparison in the SQL Compare UI.

The option will only affect the script for changes outside of the migration script.

I hope this makes sense.
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Post subject: DropAndCreateInsteadOfAlter (Alias: dacia)

Postby brad_schoen » Fri Apr 05, 2013 3:35 pm

I think I asked the question in a confusing way, especially by mentioning migration scripts which I didn't intend to use.

What I am attempting to do may or may not be supported: I'm trying to execute SQL compare from the command line to compare two revisions in source control (Red Gate / SVN), and I want the output to be any stored procedure, view, or function that has changed scripted out as IF EXISTS / DROP / CREATE so I can run it with every build. It looked like dacia flag would do this.

Hope that makes sense, and thank you for your response.
brad_schoen
 
Posts: 2
Joined: Tue Apr 02, 2013 6:24 pm

Postby Chris Auckland » Fri Apr 05, 2013 4:32 pm

I think I follow you now.

I've tested out your command and you appear to be right. It doesn't look to be observing the Drop/Create option from the command line.

If it's any consolation it works from the UI. I haven't tested calling a SQL Compare project file from the command line to see if that works any better.

I'll see if I can find a workaround for this, or at least log a bug for the development team to address.

I'll update you once I've taken a closer look.
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby Chris Auckland » Mon Apr 08, 2013 9:34 am

I'm afraid this is a bug with the current version. The DropAndCreateInsteadOfAlter option doesn't seem to be observed from the command line when using source control data sources.

I've logged this as bug SC-6317. I'm not sure of an exact timeframe for the fix, but I would guess it's likely to be around July.

I couldn't find a workaround for this, apart from using the user interface where the option seems to behave correctly.
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Re:

Postby CraigEddy » Fri Jun 28, 2013 3:42 pm

Chris Auckland wrote:I'm afraid this is a bug with the current version. The DropAndCreateInsteadOfAlter option doesn't seem to be observed from the command line when using source control data sources.

I've logged this as bug SC-6317. I'm not sure of an exact timeframe for the fix, but I would guess it's likely to be around July.

I couldn't find a workaround for this, apart from using the user interface where the option seems to behave correctly.


I ran into this issue as well. It is causing us no end of grief at the moment.
CraigEddy
 
Posts: 20
Joined: Fri Sep 28, 2012 2:59 pm


Return to SQL Compare 10

Who is online

Users browsing this forum: grinnell and 0 guests