DropAndCreateInsteadOfAlter (Alias: dacia) / Source Control
brad_schoen
Posts: 3
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.
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.
Comments
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.
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.
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.
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.