CLI behavior change with /MakeScripts switch in 14.4
rdaunce Posts: 5 New member
I'm using the Windows version of sqldatacompare.exe in a git pre-commit git hook script. An example of the command that it uses is:
Is this an intentional change or is this a bug?
SQLDataCompare.exe /scripts1:d:\git\repo /MakeScripts:C:\temp\scripts\863YARnqJv0L3NGWcrSrby7fwgDXRU7qThe source path identified in the /scripts1 switch is the git directory for a database configured for SQL Source Control. The path identified in the /MakeScripts switch is always a newly created empty directory. The command previously created a scripts folder that included scripts for the static data identified in the <DataFileSet> node of the source RedGateDatabaseInfo.xml file. Since the 22.214.171.12432 release, the static data scripts are no being included. I installed the latest version, 126.96.36.19929, and it doesn't include the data scripts either. The last version that it included the data scripts was version 188.8.131.5298.
Is this an intentional change or is this a bug?
0 · Share on Twitter
Unfortunately, I was not able to reproduce this issue on my end. When running the /MakeScripts option with SQL Data Compare, does the directory contain any object creation scripts or does it create an empty directory? Does the directory contain any newly created files while SQL Data Compare is running from command line?
If you add /LogLevel:Verbose, this will generate a log file at C:\Users\<user name>\AppData\Local\Red Gate\Logs\SQL Data Compare that may provide answers as to why the scripts are not created. Can you please send over this log file?
Product Support Engineer
Using V184.108.40.20698 or earlier, the /MakeScripts option creates a directory containing all object creation scripts including the static data in a data folder. Using version V220.127.116.1132 or later, the /MakeScripts option creates a directory containing all object creation scripts except the static data. The Data folder isn't even created.
I setup a basic reproduction of the issue using a 1 table database that has 2 rows of data. I created it using SQL Source Control V18.104.22.16828, set the 1 table as a static data table, and published it to a public repository on GitHub.
The behavior in the second run is what I expected when I upgraded to V14.4.
- At a command prompt, clone the repo:
- With SQLDataCompare V22.214.171.12432 or later installed (I used V126.96.36.19929), run the following commands:
- This is the output of the SQLDataCompare.exe command when I ran it:
- The resulting directory only has the Tables folder with a table creation script. There is no Data folder.
- Uninstall the current version of SQLDataCompare.
- Install SQLDataCompare 188.8.131.5298 or earlier and return to the command prompt:
- This is the output of the SQLDataCompare.exe command when I ran it. This version includes more output.
- The resulting directory has the Tables folder and the Data folder. Both contain the expected creation scripts.
I have attached the log files that were created from these commands to this post. They have been renamed to match the SQLDataCompare version that produced them.
Thanks for your patience. As a quick follow up, I've confirmed with our development team that the old SqlDataCompare.exe /makescripts would have created a copy of the original folder and changes to the command line in later versions has likely caused a regression in this functionality. Is there a specific use case for this as it seems like copying the folder would be equivalent to the old /makescripts?
In addition, I hope the workaround of either copying the folder or using an older version of SQL Data Compare would work for you. Please don't hesitate to reach out if you have any other questions.
Product Support Engineer
My specific use case is cloning the working version in a pre-commit hook on the SQL Source Control repository. Once the clone is made, the pre-commit hook can perform validation and other operations can on the clone without unwanted side effects in the actual git repository. For example, I can validate that naming conventions are followed, or that consistent formatting is applied. If the hook identifies and corrects something, it copies it back to the original repository and the updated version is committed.
Almost all of our per-commit hook script can be work on using a copy of the contents of the directory instead of having SQL Data Compare create the script folder. The one thing copying doesn't handle is applying consistent ordering of static data in the cloned version's creation scripts. In the scripts created by SQL Source Control, the order of the insert statements is inconsistent. In a commit to SQL Source Control involving static data, the commit frequently moves unchanged rows to different locations in the creation script and moves updated rows to the top of the creation script. There are many other forum and user-voice posts about SQL Source Control Static Data ordering inconsistencies and the issues it causes. The inconsistent ordering makes for extremely difficult merge conflict resolution and it makes it difficult to identify what has actually changed when viewing diffs on the git repository. These issues make it easy to for one developer to accidentally revert static data changes made by another developers during a merge conflict because they didn't notice that a row was moved AND changed.
As part of our pre-commit hook, the creation of the clone using SQL Data Compare with the /MakeScripts switch has the additional desirable side effect of creating all static data scripts with the insert statements ordered by the primary key of the table. Our pre-commit hook then replaces any static data creation script that was part of the original commit with the consistently ordered cloned version. This ensures all developers have the same order applied and makes it much easier to see how static data changed over time when viewing the git repository.
The change in behavior prevents us from doing this anymore unless we stick with an older version or come up with a different method. An older version works for the short term but is not a viable solution. It would exclude us from bug fixes, new features, and security updates.
My primary goal is to generate a version of the static data creation scripts in a consistent order. I could accomplish it by running two separate SQL Data Compare commands, one to create the schema scripts and one to copy the data. The issue that I have with that is that it forces us to do two completely separate SQL Data Compare operations. The inclusion of a single compare in our pre-commit hook already added a noticeable delay on every commit that involves static data. Adding a second compare operation would double the time it takes for the pre-commit hook to complete. It would be preferable to be able to run a single compare operation..
I don't have an issue using a different command, different switches, or even a completely different method - I'm mostly interested in accomplishing the same end result. I would even be satisfied with method that creates only the static data creation scripts. Please let me know if you have any suggestions.
Thanks for sharing your feedback.
I've confirmed that running /MakeScripts just creates a copy of the source. Essentially copying the scripts folder and pasting yields the same results. If you do decide to upgrade to a later version of SQL Data Compare, /MakeScripts will no longer create copies of the file as you've noted. Although, the workaround is to run a copy command or manually copying the directory over.
Please don't hesitate to let me know if you have any questions or concerns.