How to add "UseCreateOrAlterForRerunnableScripts" to an existing SQL Change Automation project

PDQ

I've looked at the only other Forum question that discusses this topic, and it shows a screenshot of selecting this value from a "new project setup window". I have several existing projects for which this action needs to be taken.

1. How do I manually set the option in the sqlproj file?
2. How do I cause the programmable objects scripts to be refreshed so that this option is included in the script?

Thanks,
Pam

DanC

Hi Pam,

So the documentation has now been updated to give additional information on how this feature works.

So you will need to delete the existing objects from the project to allow them to be regenerated with the new syntax, this will only change the checksums and is intended behavior. 

Hope this helps!

DanC

Hi Pam, 

So this has recently been added and the following documentation should help with this: Document

With regards to refreshing the Programmable Objects you'd need to make a change so they can be re-imported, what I can suggest depending upon your Compare options would be the following:

- Disable Ignore White Space  <SyncOptionIgnoreWhiteSpace>False</SyncOptionIgnoreWhiteSpace>
- Script out all the objects and reformat with a minor change like some white space
- Re-generate the scripts and commit
- Re-enable Ignore White Space <SyncOptionIgnoreWhiteSpace>True</SyncOptionIgnoreWhiteSpace>

Hope this helps!

PDQ

Dan,

Thanks for your response. However, I've attempted what you suggest, without success. 

Just to give context, I am using SQL Change Automation from within Visual Studio.

I manually added the following line to my sqlprl file:

    <UseCreateOrAlterForRerunnableScripts>True</UseCreateOrAlterForRerunnableScripts>

I manually changed the "SyncOptionIgnoreWhiteSpace" option to "True".
I then added a space to a script (from SSMS), then "refreshed" from Visual studio. 
The script was not modified, so I made a substantive change to the script, and then "refreshed".
The script was now altered (a blank line was added), but the new "CREATE or ALTER" text was not added to the script.

Can you provide any additional options for getting this to work?

I have 8 existing projects with SQL Change Automation included which need this option (due to replication of views). Even if the steps you suggested worked, performing the steps on each programmable object will take an enormous amount of time. Are there any shortcuts other than what you suggest above? 

As I brought this feature request to my Red-Gate sales/tech contacts, I shared that the inability to deploy replicated views was a blocking issue that prevented me from deploying SCA projects without having replication removed during deployment. I was thrilled when I heard that the feature was added to SCA, but I had envisioned a more user-friendly method of applying the change to my existing projects.

Thanks,
Pam