This article explains how to manipulate with SQL objects that are created in one of data sources after the project file was saved, in automated process.
In cases when specific automated processes are set up, like the ones described in the following articles:
- How to automatically compare and synchronize multiple databases on different SQL Server instances
- How to automatically keep two SQL Server database schemas in sync
- How to deploy changes directly to a SQL database from a source control repository
- How to keep a source control repository updated with the latest SQL Server database changes
- How to automatically compare and synchronize SQL Server objects to a shared script folder
… and a project file is saved with all options and specific object selections selected, it’s often necessary to later change these selections like for database objects created after the project was saved, ApexSQL Diff offers three different ways of manipulating the newly added SQL objects:
Ignore project object selection [/ipos] CLI switch
Let’s say that a project file, for an automated process, is saved including specific object selections, in other words, it means that not all objects will be compared and synchronized, but a certain sub-set only. If there is a need to quickly ignore the object selection specified in the project file, just add the /ipos CLI switch to ApexSQL Diff CLI parameters and it will omit the project selection and then include all objects in the comparison and synchronization process.
Synchronize new objects [/sno] CLI switch
The Synchronize new objects CLI switch offers an additional level of manipulation with newly created objects after a project file is saved.
The saved project file contains specific object selection and by using the /sno switch, objects that were selected for synchronization in the project file will be synchronized along with any new objects specified with the /sno:all switch and the CLI would look like this:
ApexSQLDiff /pr:”MyProject.axds” /sno:all
Each object that wasn’t present in the compared data sources when a project file was saved, will be considered as a new one. Newly created objects can be added to a previously saved project file by running the comparison process on that project file in the GUI, checking those newly created objects for synchronization, and saving a project file. In this way, they won’t be considered as new objects for synchronization.
The value of the /sno switch can be also a bitwise set number that represents a specific object type. Learn more about How to use bitwise set in ApexSQL CLI. For example, if only new tables, created after saving a project file, should be included in the synchronization process, along with the object selection from the project file, the CLI will be:
ApexSQLDiff /pr:”MyProject.axds” /sno:8
Ignore new objects option and CLI switch value
If for any reason, any newly added SQL objects, created after the project file was saved, should be ignored for the comparison and synchronization process, this option or CLI switch value can come in handy. There are two ways to achieve ignoring of all newly added SQL objects:
Edit the saved project file:
Run ApexSQL Diff GUI and open the saved project file
Switch to the Options tab and under the Objects section, check the Ignore new objects option:
Click the Save button in bottom-left corner of the project window to save the project file with new option selected.
Add “ino” value to the Ignore general [/ig] CLI switch
If editing a project file (as described in 1) step above) is not an option, new objects can be ignored by adding the /ig (Ignore general) switch with its corresponding values, along with a project file switch in the CLI.
The Ignore general switch contains all options values that correspond to GUI options found under the Attributes for script-based objects, General attributes and Objects sections in the Options tab of the New project window, as shown in the screen shot above.
By default, the Ignore general switch contains the following values:
/ig:ic idep iio iso iws
Option values description:
ic – ignore case
idep – ignore database extended properties
iio – ignore invalid objects
iso – ignore system objects
iws – ignore with encryption
In order to ignore new objects, just add “ino” value (ignore new objects) to the rest of values:
/ig:ic idep iio iso iws ino
The resulting CLI would look like this:
ApexSQLDiff /pr:”MyProject.axds” /ig:ic idep iio iso iws ino
Q: Can I ignore all objects from the project file and synchronize only new ones?
A: Yes. In addition to your project file switch in the CLI, add the /sno:all CLI switch and only the newly added objects will be synchronized. An example of the simplest CLI would be:
ApexSQLDiff /pr:”MyProject.axds” /sno:all /v /f
Q: Can I override the project file settings with CLI switches?
A: Yes. CLI values/settings always have precedence over the project file. If you set a new value for an option via the CLI switch, it will override the corresponding option value saved in the project file.
Q: Can I edit the object selection in the saved project file?
A: Yes. Open the project file in the ApexSQL Diff GUI, expand the Advanced options, and click the Object filter tab. In the Object filter tab, you can select which object types you want to include/exclude from the comparison and to additionally filter each object. Once you’re done with editing, click the Save button to overwrite the project file with new object selection.
Q: Will new objects synchronized if I check the Ignore new objects option in the project file and add the Synchronize new objects [/sno] switch?
A: Yes. The /sno switch overrides the Ignore new option from the project file and synchronizes all new objects specified by the bitwise set value.